[Tutor] MySQLdb error while inserting records
Kent Johnson
kent37 at tds.net
Sun Mar 6 20:40:44 CET 2005
Victor Bouffier wrote:
>
> Hi all,
>
> I consider myself fairly proficient with SQL, but I'm still getting the
> hang of the MySQL API. I am working through the examples in "Open Source
> Web Development with LAMP: Using Linux, Apache, MySQL, Perl, and PHP" by
> James Lee and Brent Ware, and trying to make the third "P" to be Python. :)
>
> There is an example in the book which is written in Perl, used to insert
> new records to MySQL. I tried it as it is written and it works fine. I
> have migrated to Python another example that gets all of the records in
> the age_information table (SELECT query), and it is getting the data
> successfully. However, I am having problems with the program to insert
> records.
According to this page
http://sourceforge.net/docman/display_doc.php?docid=26238&group_id=22307
MySQLdb uses the 'format' style of passing parameters to SQL, not the questionmark style you have
used below. So change query to
> 32 query = '''
> 33 INSERT INTO age_information
> 34 (lastname, firstname, age)
> 35 VALUES (%s, %s, %s)
> 36 '''
Kent
>
> The following is my modified code in Python:
>
> ------------------------------
> 1 #!/usr/bin/python
> 2 # connect.py
> 3
> 4 import sys
> 5 import MySQLdb
> 6
> 7 if len(sys.argv) != 4:
> 8 print "You have to enter lastname, firstname and age\n"
> 9 sys.exit(1)
> 10
> 11 # This is to change the age type from str to int
> 12 last, first, strAge = sys.argv[1:]
> 13 age = int(strAge)
> 14
> 15 # Some debugging lines
> 16 print last, first, age
> 17 print type(last), type(first), type(age)
> 18 print
> 19 #sys.exit()
> 20
> 21
> 22 try:
> 23 conn = MySQLdb.connect(host='localhost',
> 24 user='apache', passwd='LampIsCool', db='people')
> 25 except:
> 26 print "Could not connect\n"
> 27 sys.exit(1)
> 28
> 29 c = conn.cursor()
> 30
> 31 # prepare the SQL, exit() if the preparation fails
> 32 query = '''
> 33 INSERT INTO age_information
> 34 (lastname, firstname, age)
> 35 VALUES (?, ?, ?)
> 36 '''
> 37
> 38 # execute the SQL
> 39 records = c.execute(query, (last, first, age))
> 40 if records >= 1:
> 41 print "Succesfully inserted %d records" % records
> 42 else:
> 43 print "Could not insert anything, sorry."
> 44
> 45 c.commit()
> 46 c.close()
> 47 conn.close()
> 48
> ------------------------------
>
> Executing the script from the command line, I get the following output:
>
>
> ------------------------------
> $ ./insert.py Cool Joe 13
> Cool Joe 13
> <type 'str'> <type 'str'> <type 'int'>
>
> Traceback (most recent call last):
> File "./insert.py", line 39, in ?
> records = c.execute(query, (last, first, age))
> File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in
> execute
> return self._execute(query, args)
> File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 110,
> in _execute
> self.errorhandler(self, TypeError, m)
> File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line
> 33, in defaulterrorhandler
> raise errorclass, errorvalue
> TypeError: not all arguments converted during string formatting
> ------------------------------
>
> I was getting this same error so I entered lines 12 and 13 to change the
> age from type string to integer. No change.
> Do you know what am I missing?
>
> Thanks to all.
>
More information about the Tutor
mailing list