mysql - How can I prevent extra quotations being inserted into MySQLdb statement from Python? -


i have series of sql select statements need run python script using mysqldb. 1 of variables pass select statements called unit_ids. i've tried treating unit_ids string tuple of strings. initially, backslashes being inserted string. after looking around online, i've been able avoid backslashes, quotation marks being inserted instead. here current code:

connection = mysqldb.connect('localhost', 'root', '*****', 'test') cur = connection.cursor  unit_ids = ('0a1', '0a2', '0a3', '0a4') attr = 'sample'  cur.execute("""select count(*) test attribute = %s , unit_id in %r""", (a, tuple(unit_ids)))

using cur._last_executed, can see actual sql statement performed was:

select count(*) test attribute = 'sample' , unit_id in ("'0a1'", "'0a2'", "'0a3'", "'0a4'")

any ideas on need change in order have ('0a1', '0a2', '0a3', '0a4') remain unchanged in sql statement?

update: here's exact output when using %s:

>>> cn = mysqldb.connect('localhost', 'root', '***', '***') >>> c = cn.cursor() >>> unit_ids = ('0a1', '0a2', '0a3', '0a4') >>> = 'foo' >>> c.execute("""select count(*) model_test attribute = %s , unit_id in %s""", (a, unit_ids)) 1l >>> print(c._last_executed) select count(*) model_test attribute = 'foo' , unit_id in ("'0a1'", "'0a2'", "'0a3'", "'0a4'")

at point, think may need create separate variables each element of unit_ids (eg unit_id1 = '0a1'). i'm using python 2.7.9 , mysql server 5.6, way.



update 2: @thebjorn solved it: version of mysqldb outdated. after upgrading, there no more quotation marks being inserted sql statement.

you don't need magic, regular mysql way:

connection = mysqldb.connect('localhost', 'root', '*****', 'test') cur = connection.cursor()  unit_ids = ('0a1', '0a2', '0a3', '0a4') attr = 'sample'  cur.execute("""select count(*) test attribute = %s , unit_id in %s""", (a, unit_ids))

the wrinkle can see if a not included , unit_ids had 1 item, tuple syntax trip up. if make unit_ids list syntax wouldn't quite awkward:

unit_ids = ('0a1',) cur.execute("select count(*) test unit_id in %s", (unit_ids,))

which when inlined becomes:

cur.execute("select count(*) test unit_id in %s", (('0a1',),))

vs. using lists (one parameter, parameter 1 item list):

cur.execute("select count(*) test unit_id in %s", [['0a1']])

you can use %s mysql parameters (a few others can used too, not %r -- isn't string interpolation).

update: must doing something differently me.. here output of cursor._last_executed

>>> cn = mysqldb.connect('server', 'user', 'password', 'database') >>> c = cn.cursor() >>> unit_ids = ('0a1', '0a2', '0a3', '0a4') >>> c.execute("select count(*) foo id in %s", (unit_ids,)) traceback (most recent call last):   file ... _mysql_exceptions.programmingerror: (1146, "table 'djangodevelop.foo' doesn't exist") >>> c._last_executed "select count(*) foo id in ('0a1','0a2','0a3','0a4')" >>>

Comments

Post a Comment

Popular posts from this blog

html - Outlook 2010 Anchor (url/address/link) -

i'm trying link specific pdf page msoutlook'10 or msword'10, #page=... anchor particularly useful , works fine elsewhere. unfortunately when click [ctr-k] in outlook or winword, links re-formatted assuming unc path. pdf file located on shared drive. using "file:" prefix links open pdf in adobe reader (outside of browser), , "http:" open inside browser. issue seem need open in browser "file:" prefix in order anchor work. know anchors work inside browser. outlook uses windows/explorer default program (based on file extension) seems open in adobe reader neglecting anchor. is aware of solution, link specific page or bookmark of shared-pdf ms outlook and/or winword link? common references: mailermailer.com/...anchor-tags-html-emails campaignmonitor.com/...anchor-links-in-email-newsletters/ stackoverflow.com/...anchor-in-pdf-document
Read more

javascript - Why does running this loop 9 times take 100x longer than running it 8 times? -

consider code: test = function() { } t = new test(); (var = 0; < 8; i++) { result = t instanceof test; } if change number of iterations 8 9 , loop take 100 times longer complete in latest version of firefox (41.0.1). tested on 2 different pcs , magic limit 8. here jsperf test used: http://jsperf.com/instanceof-8-times-vs-9-times does have idea why might happen? seems specific instanceof . not happen if else object, example check property. note: filed bugzilla bug this. jan de mooij mozilla team has posted details in bugzilla thread . here's simplistic interpretation of highly technical answers: in i < 8 case, firefox smart enough hoist result = t instanceof test; statement out of loop (according tests, doesn't seem omit altogether). in i < 9 case, apparently doesn't optimization . why? reason not clear, related fact 9 iterations threshold above function considered "hot" enough run through jit compiler. i < 8 cas...
Read more

Getting gateway time-out Rails app with Nginx + Puma running on Digital Ocean -

following this guide setup app on digital ocean droplet, not working. site not loading , it's throwing: "gateway time-out". the puma server works, since ps aux | grep puma prints: root 15878 0.0 0.4 376832 2168 ? sl 12:50 0:00 puma 2.13.4 (unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock) [20150930164940] root 15881 0.0 0.7 903676 3692 ? sl 12:50 0:06 puma: cluster worker 0: 15878 [20150930164940] root 24654 0.0 0.1 11748 916 pts/2 s+ 18:00 0:00 grep --color=auto puma my nginx config: user root; worker_processes 1; events { worker_connections 1024; } http { upstream puma { server unix:///var/www/myapp/shared/tmp/sockets/myapp-puma.sock; } server { listen 80; server_name myapp.com; root /var/www/myapp/current/public; access_log /var/www/myapp/current/log/access.log; error_log /...
Read more