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='localhos t', user='abc,passw d='def',
db='ghi',compre ss=1)

cursor = conn.cursor(cur sorclass=MySQLd b.cursors.DictC ursor)
sqlQuery = "INSERT INTO EVA_COMPANY
(COM_ADDRESS,CO M_AUTOID,COM_CO MPANY_ADDRESS,C OM_STRUCT_OWNER _CODE)
VALUES (?,?,?,?) "
print sqlQuery
sql = cursor.execute( sqlQuery,("test ","NULL","Teste r1017",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_NA ME` varchar(128) NOT NULL,
`COM_TRADING_NA ME` varchar(128) default NULL,
`COM_ADDRESS` varchar(256) NOT NULL,
`COM_POSTAL_ADD RESS` varchar(256) default NULL,
`COM_CONTACT_NA ME` varchar(56) default NULL,
`COM_CONTACT_PO SITION` varchar(56) default NULL,
`COM_CONTACT_TE LEPHONE` varchar(16) default NULL,
`COM_CONTACT_FA X` varchar(16) default NULL,
`COM_CONTACT_EM AIL` varchar(256) default NULL,
`COM_STRUCT_OWN ER_CODE` int(9) unsigned default NULL,
`COM_INDUSTRY_C ODE` varchar(16) default NULL,
`COM_INDUSTRY_D ESC` 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_OW NER_CODE`),
KEY `IDX_COM0` (`COM_COMPANY_N AME`),
CONSTRAINT `FK_COS_COM0` FOREIGN KEY (`COM_STRUCT_OW NER_CODE`)
REFERENCES `eva_code_comp_ struct_ownershi p` (`COS_AUTOID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNA MIC;

.... 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","Teste r1017",5))
File
"C:\bin\install ed\Python2.4.1\ Lib\site-packages\MySQLd b\cursors.py",
line 132, in execute
self.errorhandl er(self, TypeError, m)
File
"C:\bin\install ed\Python2.4.1\ Lib\site-packages\MySQLd b\connections.p y",
line 33, in defaulterrorhan dler
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.

[shearichard]

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

Dennis Lee Bieber wrote:

On 19 Feb 2006 16:26:15 -0800, sh*********@gma il.com declaimed the
following in comp.lang.pytho n:

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,CO M_AUTOID,COM_CO MPANY_ADDRESS,C OM_STRUCT_OWNER _CODE)
VALUES (?,?,?,?) "
One: I believe MySQLdb uses %s placeholders, not ?

import MySQLdb
MySQLdb.paramst yle

'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 ...

print sqlQuery
sql = cursor.execute( sqlQuery,("test ","NULL","Teste r1017",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 "").

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

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!

.... 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.

--

> =============== =============== =============== =============== == <
> wl*****@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wu******@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <