cursor - How to do this lookup more elegantly in Python and MySQL? -

-

i have bunch of files in directory named nameid_cityid.txt, nameid , cityid being ids of name (integer(10)) , city (integer(10)) in mydata table.

while following solution works, doing type conversions since fetchall fetches 'l' , file name tuple of nameid, cityid strings,..

if can suggest pythonic or more elegant way of doing same, awesome, me , communtiy!

what trying achieve : find files directory don't have record in database , file, parse/move/delete it.

mysql table mydata :

nameid  cityid 15633   45632 2354    76894

python :

for pdffile in os.listdir(filepath):     cityid, nameid = pdffile.strip('.txt').split('_')[0], pdffile.strip('.txt').split('_')[1]         cursor.execute("select cityid, nameid mydata")     alreadyparsed = cursor.fetchall()     targetvalues = ((str(cityid), str(nameid)) cityid, nameid in alreadyparsed)     if (int(cityid), int(nameid)) in alreadyparsed:         print cityid, nameid, "found"     else:         print cityid, nameid, "not found"

i'd use set quick , easy testing:

cursor.execute("select concat(nameid, '_', cityid, '.txt') mydata") present = set([r[0] r in cursor])  pdffile in os.listdir(filepath):     nameid, cityid = map(int, pdffile.rsplit('.', 1)[0].split('_'))     print nameid, cityid,     print "found" if pdffile in present else "not found"

first, i've pulled query outside of filename loop; no point in querying same set of rows each time.

secondly, i'll let mysql generate filenames me using concat ease of collecting information set.

thirdly, because have set of filenames, testing each individual filename against set simple pdffile in present test.

and finally, i've simplified filename splitting logic 1 line.

now, if want set of filenames not present yet in database (rather enumerate ones , ones not), use set operation:

cursor.execute("select concat(nameid, '_', cityid, '.txt') mydata") present = set([r[0] r in cursor])  pdffile in (set(os.listdir(filepath)) - present):     nameid, cityid = map(int, pdffile.rsplit('.', 1)[0].split('_'))     print nameid, cityid, "found"

here use .difference operation (with - operator) remove filenames there rows in database, in 1 simple operation.


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more