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

[shearichard at gmail.com]

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

Dennis Lee Bieber wrote:
> On 19 Feb 2006 16:26:15 -0800, shearichard at 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_STRUCT_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.




> --
>  > ============================================================== <
>  >   wlfraed at ix.netcom.com  | Wulfraed  Dennis Lee Bieber  KD6MOG <
>  >      wulfraed at dm.net     |       Bestiaria Support Staff       <
>  > ============================================================== <
>  >           Home Page: <http://www.dm.net/~wulfraed/>            <
>  >        Overflow Page: <http://wlfraed.home.netcom.com/>        <