MySQL problem

[Lad]

I have the following
program( only insert a record)
############### #
import MySQLdb
conn = MySQLdb.connect (host = "localhost",use r = "", passwd =
"",db="dilynamo bily")
cursor = conn.cursor ()
cursor.execute( """CREATE TABLE produkt1 (
id int(10) unsigned NOT NULL auto_increment,
MyNumber varchar(30) NOT NULL default '',
PRIMARY KEY (id))
""")

#MyValue=111
cursor.execute ("""INSERT INTO produkt1
(MyNumber)
VALUES(111)
""")
############### ##
It works. But If I change the program like the following ( only use a
variable MyValue in INSERT statement it does not work.
##########THIS DOES NOT WORK########
import MySQLdb,re,stri ng
conn = MySQLdb.connect (host = "localhost",use r = "", passwd =
"",db="dilynamo bily")
cursor = conn.cursor ()
cursor.execute( """CREATE TABLE produkt1 (
id int(10) unsigned NOT NULL auto_increment,
MyNumber varchar(30) NOT NULL default '',
PRIMARY KEY (id))
""")

MyValue=111
cursor.execute ("""INSERT INTO produkt1
(MyNumber)
VALUES(MyValue)
""")
############### ##
Program says
OperationalErro r: (1054, "Unknown column 'MyValue' in 'field list'")
Where is a problem. Thanks for help
Lad.

[wes weston]

Re: MySQL problem

Lad wrote:[color=blue]
> I have the following
> program( only insert a record)
> ############### #
> import MySQLdb
> conn = MySQLdb.connect (host = "localhost",use r = "", passwd =
> "",db="dilynamo bily")
> cursor = conn.cursor ()
> cursor.execute( """CREATE TABLE produkt1 (
> id int(10) unsigned NOT NULL auto_increment,
> MyNumber varchar(30) NOT NULL default '',
> PRIMARY KEY (id))
> """)
>
> #MyValue=111
> cursor.execute ("""INSERT INTO produkt1
> (MyNumber)
> VALUES(111)
> """)
> ############### ##
> It works. But If I change the program like the following ( only use a
> variable MyValue in INSERT statement it does not work.
> ##########THIS DOES NOT WORK########
> import MySQLdb,re,stri ng
> conn = MySQLdb.connect (host = "localhost",use r = "", passwd =
> "",db="dilynamo bily")
> cursor = conn.cursor ()
> cursor.execute( """CREATE TABLE produkt1 (
> id int(10) unsigned NOT NULL auto_increment,
> MyNumber varchar(30) NOT NULL default '',
> PRIMARY KEY (id))
> """)
>
> MyValue=111
> cursor.execute ("""INSERT INTO produkt1
> (MyNumber)
> VALUES(MyValue)
> """)
> ############### ##
> Program says
> OperationalErro r: (1054, "Unknown column 'MyValue' in 'field list'")
> Where is a problem. Thanks for help
> Lad.
>[/color]

Lad,
Try

str = "INSERT INTO produkt1 (MyNumber) VALUES(%d)" % (MyNumber)
cursor.execute( str)

wes

[Dennis Lee Bieber]

Re: MySQL problem

On Thu, 17 Mar 2005 16:45:57 GMT, wes weston <wweston@att.ne t> declaimed
the following in comp.lang.pytho n:
[color=blue]
>
> str = "INSERT INTO produkt1 (MyNumber) VALUES(%d)" % (MyNumber)
> cursor.execute( str)
>[/color]
Think you meant "MyValue" for the second item... However...

Try neither, the recommended method is to let the execute() do
the formatting... That way /it/ can apply the needed quoting of
arguments based upon the type of the data.

cursor.execute( "insert into produkt1 (MyNumber) values (%d)", (MyValue))

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

[wes weston]

Re: MySQL problem

Dennis Lee Bieber wrote:[color=blue]
> On Thu, 17 Mar 2005 16:45:57 GMT, wes weston <wweston@att.ne t> declaimed
> the following in comp.lang.pytho n:
>
>[color=green]
>>str = "INSERT INTO produkt1 (MyNumber) VALUES(%d)" % (MyNumber)
>>cursor.execut e(str)
>>[/color]
>
> Think you meant "MyValue" for the second item... However...
>
> Try neither, the recommended method is to let the execute() do
> the formatting... That way /it/ can apply the needed quoting of
> arguments based upon the type of the data.
>
> cursor.execute( "insert into produkt1 (MyNumber) values (%d)", (MyValue))
>[/color]

Dennis,
Do you know if this has some efficiency advantage
or is it just an agreed upon custom.
wes

[Kent Johnson]

Re: MySQL problem

wes weston wrote:[color=blue]
> Dennis Lee Bieber wrote:[color=green]
>> Try neither, the recommended method is to let the execute() do
>> the formatting... That way /it/ can apply the needed quoting of
>> arguments based upon the type of the data.
>>
>> cursor.execute( "insert into produkt1 (MyNumber) values (%d)", (MyValue))
>>[/color]
>
> Dennis,
> Do you know if this has some efficiency advantage
> or is it just an agreed upon custom.[/color]

It may have efficiency advantages if the DB caches requests. But the main advantages are that
- it correctly escapes special chars such as "
- consequently it also protects against SQL injection attacks where MyValue might contain malicious SQL.

Kent

[Dennis Lee Bieber]

Re: MySQL problem

On Fri, 18 Mar 2005 16:22:37 GMT, wes weston <wweston@att.ne t> declaimed
the following in comp.lang.pytho n:
[color=blue]
> Dennis Lee Bieber wrote:[color=green]
> >
> > cursor.execute( "insert into produkt1 (MyNumber) values (%d)", (MyValue))[/color][/color]
I meant %s there, not %d[color=blue][color=green]
> >[/color]
>
> Dennis,
> Do you know if this has some efficiency advantage
> or is it just an agreed upon custom.[/color]

A more critical reason is the module itself can handle the
quoting and escaping needed for the odder data types. Consider what
happens if:

avalue = '''"This is a 'string' with both types of 'quotes'"'''
(that is 3*', ", 'x', 'x', ", 3*')

and you use the % operator on

sql = "insert into atable (afield) values (%s)" % avalue

<print sql>

insert into atable (afield) values ("This is a 'string' with both types
of 'quotes'")

The " are taken by MySQL as the delimiters of the string value, not part
of the string data.

But if you use:

sql = "insert into atable (afield) values ('%s')" % avalue

you get

insert into atable (afield) values ('"This is a 'string' with both types
of 'quotes'"')

Note that MySQL will complain at 'string, as the ' closes the string
"This is a

Letting .execute(templa te, (values)) do the substitution will generate
the proper (without, I believe, having to put quotes around the %s):

insert into atable (afield) values ('"This is a \'string\' with both
types of \'quotes\'"')

or, if .execute() defaults to using " instead of '

insert into atable (afield) values ("\"This is a 'string' with both
types of 'quotes'\"")


Their is also, as I recall, an .executemany() that some RDBMs
support. For these, you MUST let the module do the substitution:

c.executemany(t emplate,
((record1 values),
(record2 values), ...,
(record-n values)) )

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