473,320 Members | 1,841 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MySQL problem

Lad
I have the following
program( only insert a record)
################
import MySQLdb
conn = MySQLdb.connect (host = "localhost",user = "", passwd =
"",db="dilynamobily")
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,string
conn = MySQLdb.connect (host = "localhost",user = "", passwd =
"",db="dilynamobily")
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
OperationalError: (1054, "Unknown column 'MyValue' in 'field list'")
Where is a problem. Thanks for help
Lad.

Jul 18 '05 #1
5 1537
Lad wrote:
I have the following
program( only insert a record)
################
import MySQLdb
conn = MySQLdb.connect (host = "localhost",user = "", passwd =
"",db="dilynamobily")
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,string
conn = MySQLdb.connect (host = "localhost",user = "", passwd =
"",db="dilynamobily")
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
OperationalError: (1054, "Unknown column 'MyValue' in 'field list'")
Where is a problem. Thanks for help
Lad.


Lad,
Try

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

wes

Jul 18 '05 #2
On Thu, 17 Mar 2005 16:45:57 GMT, wes weston <ww*****@att.net> declaimed
the following in comp.lang.python:

str = "INSERT INTO produkt1 (MyNumber) VALUES(%d)" % (MyNumber)
cursor.execute(str)
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))

-- ================================================== ============ <
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/> <

Jul 18 '05 #3
Dennis Lee Bieber wrote:
On Thu, 17 Mar 2005 16:45:57 GMT, wes weston <ww*****@att.net> declaimed
the following in comp.lang.python:

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


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


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

Jul 18 '05 #4
wes weston wrote:
Dennis Lee Bieber wrote:
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))


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


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
Jul 18 '05 #5
On Fri, 18 Mar 2005 16:22:37 GMT, wes weston <ww*****@att.net> declaimed
the following in comp.lang.python:
Dennis Lee Bieber wrote:

cursor.execute("insert into produkt1 (MyNumber) values (%d)", (MyValue)) I meant %s there, not %d
Dennis,
Do you know if this has some efficiency advantage
or is it just an agreed upon custom.


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(template, (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(template,
((record1 values),
(record2 values), ...,
(record-n values)) )

-- ================================================== ============ <
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/> <

Jul 18 '05 #6

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

Similar topics

0
by: JL | last post by:
Platform: Linux Red Hat RHEL 3 (and red hat 9) Installed MySQL from source. As a matter of fact, installed all LAMPS from source, and the mysql socket file was arranged in a place other than...
0
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary...
3
by: Kirk Soodhalter | last post by:
Hi, This started as a phpmyadmin problem, but has somehow morphed into a mysql problem. I don't know how to fix it. I am posting the conversation from a php newsgroup since it started there. ...
0
by: Plymouth Acclaim | last post by:
Hi guys, We have a problem with Dual AMD64 Opteron/MySQL 4.0.18/Mandrake 10 for a very high volume site. We are evaluating the performance on our new server AMD64 and it seems it's slow compared...
1
by: Alex Hunsley | last post by:
I am trying to install the DBD::mysql perl module. However, it claims I need mysql.h: cpan> install DBD::mysql CPAN: Storable loaded ok Going to read /home/alex/.cpan/Metadata Database was...
1
by: smsabu2002 | last post by:
Hi, I am facing the build problem while installing the DBD-MySql perl module (ver 2.9008) using both GCC and CC compilers in HP-UX machine. For the Build using GCC, the compiler error is...
1
by: jrs_14618 | last post by:
Hello All, This post is essentially a reply a previous post/thread here on this mailing.database.myodbc group titled: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode I was...
110
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst...
39
by: alex | last post by:
I've converted a latin1 database I have to utf8. The process has been: # mysqldump -u root -p --default-character-set=latin1 -c --insert-ignore --skip-set-charset mydb mydb.sql # iconv -f...
10
by: Caffeneide | last post by:
I'm using a php script which performs three xml queries to other three servers to retrieve a set of ids and after I do a query to mysql of the kind SELECT * FROM table WHERE id IN ('set of ids');...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.