python - Can I put arbitrary text in SQL comments when using ODBC? -

consider table defined by

create table example (id integer) 

and insert statement adding tuples example, having single quote in sql comment:

my_cmd = """-- comment's device insert example (id) values (?) """ 

i connecting mysql via pyodbc module. surprsing effect seems insert command consistently fails .iff. there odd number of apostrophes in comment. message mentions hy000 and:

'the sql contains 0 parameter markers, 1 parameters supplied' 

update: behavior has been verified mysql support staff, there chance classified bug, actually. see gord's answer, lists specimen, , comments pointing mysql bug database.

a full test script listed below. wonder if quote thing gotcha? odd:

is wrong assume comments start @ -- , extends end of line if statement text passed via odbc? (i put space after --, , use sql comments portability. different comment characters, or myslq block comments don't change effect.)

a trace of odbc session shows sqlnumparams of 1 , 0 successful , failing cases listed below, respectively. however, statements given. tempted assume linebreaks ('\n') ineffective. somehow contradicted working examples below have 2 quotes (my_cmd_with_two_qutoes), or none (i.e. my_cmd "*" replacing "'").

or driver issue?

import pyodbc  my_cmd = """-- comment's device insert example (id) values (?) """  my_cmd_with_two_quotes = """-- comment's device's insert example (id) values (?) """  conn = pyodbc.connect("dsn=abcdef;pwd=...") thing_named_cursor = conn.cursor()  # success, replacing single apostrophe: thing_named_cursor.execute(my_cmd.replace("'", "*"), (123, ))  # success, too: thing_named_cursor.execute(my_cmd_with_two_quotes, (456, ))  # failure: try:     thing_named_cursor.execute(my_cmd, (789, )) except pyodbc.error, e:     print("error: %s" % e)  conn.commit() conn.close() 

or driver issue?

yes. recreated issue following test code against mysql database

import pyodbc cnxn = pyodbc.connect("dsn=usbmysql") crsr = cnxn.cursor() sql = """ -- comment's device insert example (id) values (?) """ crsr.execute(sql, [11]) cnxn.commit() cnxn.close() 

but works fine dsn pointing microsoft sql server database (using sql server native client 11.0).

the code fails when using pypyodbc against mysql database, it's not issue pyodbc.

also, following vbscript code ...

option explicit const adparaminput = 1 const adinteger = 3 dim con, cmd set con = createobject("adodb.connection") con.open "dsn=usbmysql" set cmd = createobject("adodb.command") cmd.activeconnection = con cmd.commandtext = _     "-- gord's test" & vbcrlf & _     "insert example (id) values (?)" cmd.parameters.append cmd.createparameter("?", adinteger, adparaminput, , 121) cmd.execute con.close 

... fails ...

[mysql][odbc 5.2(w) driver][mysqld-5.6.13-log]you have error in sql syntax; check manual corresponds mysql server version right syntax use near '?)' @ line 2

... changing gord's gord''s in commandtext ...

cmd.commandtext = _     "-- gord''s test" & vbcrlf & _     "insert example (id) values (?)" 

... allows run without complaint.

additional information:

for record, mysql connector/python not have problem original comment string in question. works fine:

import mysql.connector cnxn = mysql.connector.connect(port=3307, user='root', password='whatever', database='mydb') crsr = cnxn.cursor() sql = """ -- comment's device insert example (id) values (%s) """ crsr.execute(sql, [101]) cnxn.commit() cnxn.close()