MySQLDB - parameterised SQL - "TypeError: not all arguments converted during string formatting"

[shearichard]

Hi - I have written some python to insert a row into a table using
MySQLDB. I have never before written SQL/Python using embedded
parameters in the SQL and I'm having some difficulties. Could someone
point me in the right direction please ?

The python looks like this :

import MySQLdb
import MySQLdb.cursors
conn = MySQLdb.Connect(host='localhost', user='abc,passwd='def',
db='ghi',compress=1)

cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
sqlQuery = "INSERT INTO EVA_COMPANY
(COM_ADDRESS,COM_AUTOID,COM_COMPANY_ADDRESS,COM_STRUCT_OWNER_CODE)
VALUES (?,?,?,?) "
print sqlQuery
sql = cursor.execute(sqlQuery,("test","NULL","Tester1017",5))
cursor.close()
conn.commit()

.... and the table looks like this ...

CREATE TABLE `eva_company` (
`COM_AUTOID` int(9) unsigned NOT NULL auto_increment,
`COM_COMPANY_NAME` varchar(128) NOT NULL,
`COM_TRADING_NAME` varchar(128) default NULL,
`COM_ADDRESS` varchar(256) NOT NULL,
`COM_POSTAL_ADDRESS` varchar(256) default NULL,
`COM_CONTACT_NAME` varchar(56) default NULL,
`COM_CONTACT_POSITION` varchar(56) default NULL,
`COM_CONTACT_TELEPHONE` varchar(16) default NULL,
`COM_CONTACT_FAX` varchar(16) default NULL,
`COM_CONTACT_EMAIL` varchar(256) default NULL,
`COM_STRUCT_OWNER_CODE` int(9) unsigned default NULL,
`COM_INDUSTRY_CODE` varchar(16) default NULL,
`COM_INDUSTRY_DESC` varchar(56) default NULL,
`COM_NUMBER_OF_SITES` int(9) default NULL,
`COM_NATURE_OF_RELATIONSHIP` varchar(128) default NULL,
PRIMARY KEY (`COM_AUTOID`),
KEY `IDX_COM1` (`COM_STRUCT_OWNER_CODE`),
KEY `IDX_COM0` (`COM_COMPANY_NAME`),
CONSTRAINT `FK_COS_COM0` FOREIGN KEY (`COM_STRUCT_OWNER_CODE`)
REFERENCES `eva_code_comp_struct_ownership` (`COS_AUTOID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

.... but when I try to execute the python I get an error ...

Traceback (most recent call last):
File "sheadbtest1.py", line 8, in ?
sql = cursor.execute(sqlQuery,("test","NULL","Tester1017",5))
File
"C:\bin\installed\Python2.4.1\Lib\site-packages\MySQLdb\cursors.py",
line 132, in execute
self.errorhandler(self, TypeError, m)
File
"C:\bin\installed\Python2.4.1\Lib\site-packages\MySQLdb\connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
TypeError: not all arguments converted during string formatting

.... as I say if anyone could provide me with some tips I'd appreciate
it.

thanks

richard shea.

 

[Dennis Lee Bieber]

Hi - I have written some python to insert a row into a table using
MySQLDB. I have never before written SQL/Python using embedded
parameters in the SQL and I'm having some difficulties. Could someone
point me in the right direction please ?
sqlQuery = "INSERT INTO EVA_COMPANY
(COM_ADDRESS,COM_AUTOID,COM_COMPANY_ADDRESS,COM_STRUCT_OWNER_CODE)
VALUES (?,?,?,?) "

One: I believe MySQLdb uses %s placeholders, not ?
'format'

From the DB-API PEP

paramstyle

String constant stating the type of parameter marker
formatting expected by the interface. Possible values are
[2]:

'qmark' Question mark style,
e.g. '...WHERE name=?'
'numeric' Numeric, positional style,
e.g. '...WHERE name=:1'
'named' Named style,
e.g. '...WHERE name=:name'
'format' ANSI C printf format codes,
e.g. '...WHERE name=%s'
'pyformat' Python extended format codes,
e.g. '...WHERE name=%(name)s'

print sqlQuery
sql = cursor.execute(sqlQuery,("test","NULL","Tester1017",5))

Two: I think it is smart enough to translate a Python None to a
MySQL NULL -- which is different from a four-character string containing
NULL (or even a zero-character string "").

CREATE TABLE `eva_company` (
`COM_AUTOID` int(9) unsigned NOT NULL auto_increment,

As an auto-increment field, the recommendation would be to not even
mention the field in the INSERT SQL -- that also takes care of the
problem of specifying NULL to a non-null field!

--

 

[shearichard]

Hi Dennis - Thanks for your help with this ....

Hi - I have written some python to insert a row into a table using
MySQLDB. I have never before written SQL/Python using embedded
parameters in the SQL and I'm having some difficulties. Could someone
point me in the right direction please ?
sqlQuery = "INSERT INTO EVA_COMPANY
(COM_ADDRESS,COM_AUTOID,COM_COMPANY_ADDRESS,COM_STRUCT_OWNER_CODE)
VALUES (?,?,?,?) "

One: I believe MySQLdb uses %s placeholders, not ?
'format'

From the DB-API PEP

paramstyle

String constant stating the type of parameter marker
formatting expected by the interface. Possible values are
[2]:

'qmark' Question mark style,
e.g. '...WHERE name=?'
'numeric' Numeric, positional style,
e.g. '...WHERE name=:1'
'named' Named style,
e.g. '...WHERE name=:name'
'format' ANSI C printf format codes,
e.g. '...WHERE name=%s'
'pyformat' Python extended format codes,
e.g. '...WHERE name=%(name)s'

Great stuff. I had seen some example code which used question marks and
rather too slavishly used it as a model. As you say MySQLDb does indeed
use 'format' and so %s are the way to go ...

Two: I think it is smart enough to translate a Python None to a
MySQL NULL -- which is different from a four-character string containing
NULL (or even a zero-character string "").

.... just to confirm that yes using a Python None works fine if you wish
to put a Null into a column ...

As an auto-increment field, the recommendation would be to not even
mention the field in the INSERT SQL -- that also takes care of the
problem of specifying NULL to a non-null field!

.... yes it didn't start off that way but I was busy trying everthing I
could think of to try to make it happier.

thanks for your help it's all working now.

regards

richard.