473,544 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.

Feb 20 '06 #1
1 7451
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/> <


Feb 20 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4628
by: J. Muenchbourg | last post by:
I'm getting an "Arguments are of the wrong type, out of acceptable range, in conflicts with each other " error , pointing to the sql statement in this block: Dim rstime Set rstime = Server.CreateObject("ADODB.Recordset") rstime.Open "SELECT * FROM TimestarResorts where resortname='" & request.querystring("resortname") & "' and roomsize='"...
0
1032
by: ttomic | last post by:
I have asp.net application written in jscript. On POST request, during Page_Load, I receive string which I evaluate using jscript eval method. String looks like: my_fun("arg") where my_fun is some method in aspx. body of my_fun looks like: function my_fun(s:String) : String { var v:Typ0 = new Typ0();
4
1579
by: dbuchanan | last post by:
Hello, I have an audit table into which I insert information about the use of the application. This works sometimes and other times fails. I cannot find any reason for it failing. It is always given the information. Here are the errors I receive; --------------------------- Exception Message: Procedure or function usp_UseAudit_ins has...
0
1256
by: wan | last post by:
Hi, Any idea of why a proper running Application in VS 2003 , could not run after converted to VS 2005 with the error "Could not load file or assembly , . Strong name validation failed".... The error message as below : System.IO.FileLoadException was unhandled Message="Could not load file or assembly 'Xenbiz.Common.Calculator,...
42
3389
by: Holger | last post by:
Hi guys Tried searching for a solution to this, but the error message is so generic, that I could not get any meaningfull results. Anyways - errormessage: ---------------------------------------------------- TypeError: addFile() takes exactly 1 argument (2 given) ----------------------------------------------------
7
12712
by: Girish Sahani | last post by:
Hi, Please check out the following loop,here indexList1 and indexList2 are a list of numbers. for index1 in indexList1: for index2 in indexList2: if ti1 == ti2 and not index1 != indexList1.pop(): index1+=1 index2+=1 continue
1
5885
by: OtisUsenet | last post by:
Hi, I have a bookmarklet that works perfectly in Firefox, IE, Konqueror, and Opera, but in Safari 2.0.3 (417.9.2) it doesn't work. I enabled debugging and I can see "TypeError - Undefined value" reported in the Javascript console, but I'm not sure where the error is. The bookmarklet in question is here: javascript:if...
17
5239
by: Petyr David | last post by:
Just looking for the simplest. right now my perl script returns an error messge to the user if the date string is invalid. would like to do this before accessing the server. TX
7
24396
by: lukertin | last post by:
I have a 2-D array stored as an object, when i go to call up the array using my $testvar = @ {$self->Peptides}; it gives me the error Can't use string ("0") as an ARRAY ref while "strict refs" in use. Currently I am trying to print the objects (the code is in the package code).
0
7420
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7764
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7368
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7704
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
4911
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3407
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1837
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
983
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
657
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.