By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,156 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,156 IT Pros & Developers. It's quick & easy.

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

P: n/a
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_ST RUCT_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.

Feb 20 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi Dennis - Thanks for your help with this ....

Dennis Lee Bieber wrote:
On 19 Feb 2006 16:26:15 -0800, sh*********@gmail.com declaimed the
following in comp.lang.python:
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_ST RUCT_OWNER_CODE)
VALUES (?,?,?,?) "
One: I believe MySQLdb uses %s placeholders, not ?
import MySQLdb
MySQLdb.paramstyle

'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","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 "").


.... 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.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wu******@dm.net | Bestiaria Support Staff <
> ================================================== ============ <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.netcom.com/> <


Feb 20 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.