Insert fields into MySQL using Python

Question

When I try and enter the following command into MySQL:

sql = """INSERT INTO event(summary, location, start, end, created, uid, description, calid) VALUES ('Dom seeing Carl - Test', 'City', '2016-11-30 22:30:00+00:00', '2016-11-30 23:00:00+00:00', '2016-11-29 00:39:13+00:00', '80462778A326E04EBD831336D01F2A2F051BA9E0A50E254ABADB036AF2BBE902040000008200E00074C5B7101A82E008000000005096FE958A49D201000000000000000010000000', 'New build happening…', '2')"""

I get and Unicode error.

I have tried that same insert directly into MySQL using workbench console and it works flawlessly, I can not understand why is Python complaining. The Python code I am using is:

db = MySQLdb.connect("192.168.100.1","root","test123","calendar1" )
cursor = db.cursor()
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

Table definitions:

# Field, Type, Null, Key, Default, Extra
uid, longtext, NO, , , 
summary, longtext, NO, , , 
location, varchar(255), NO, , , 
start, varchar(45), NO, , , 
end, varchar(45), NO, , , 
description, longtext, NO, , , 
created, varchar(45), NO, , , 
calid, int(11), NO, , ,

Traceback error:

TypeError: query() argument 1 must be string or read-only buffer, not tuple

and does this datetime format 2016-11-30 22:30:00+00:00 was inserted in mysql? — Shivkumar kondi, Feb 16 '17 at 06:12

score 0 · Answer 1 · edited May 23 '17 at 12:34

You can try to use this other format:

db = MySQLdb.connect("192.168.100.1","root","test123","calendar1" )
cursor = db.cursor()
try:
    cursor.execute('INSERT INTO 
                    event(summary, location, start, end, created, uid, description, calid) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', 
                    ('Dom seeing Carl - Test', 'City', '2016-11-30 22:30:00+00:00', '2016-11-30 23:00:00+00:00', '2016-11-29 00:39:13+00:00', '80462778A326E04EBD831336D01F2A2F051BA9E0A50E254ABADB036AF2BBE902040000008200E00074C5B7101A82E008000000005096FE958A49D201000000000000000010000000', 'New build happening…', '2'))
    db.commit()
except:
    db.rollback()
db.close()

This question is related and gives good points on why using this format is recommended.

Could you show the full traceback as well as the table definition? — valeas, Feb 18 '17 at 08:03

prateeknischal · Answer 2 · 2017-02-20T06:05:58.150

0

The following should work:
@valeas's answer is correct but the %s should be enclosed in qoutes as sql would not be able to comprehend the string once strings are substituted in the placeholders %s as it will be treated as variables in the final SQL statement.

try:
    cursor.execute("insert into event(summary, location, start, end,
        created, uid, description, calid) values('%s', '%s', '%s',
        '%s', '%s', '%s', '%s', '%s')"%('Dom seeing Carl - Test', 'City', '2016-11-30 22:30:00+00:00', '2016-11-30 23:00:00+00:00', '2016-11-29 00:39:13+00:00', '80462778A326E04EBD831336D01F2A2F051BA9E0A50E254ABADB036AF2BBE902040000008200E00074C5B7101A82E008000000005096FE958A49D201000000000000000010000000', 'New build happening…', '2'))
    db.commit()
except:
    db.rollback()

edited Feb 20 '17 at 06:05

answered Feb 20 '17 at 05:42

prateeknischal

752
4
12

Yes that does work, but as per the resolution here: [link]http://stackoverflow.com/questions/775296/python-mysql-parameterized-queries it should work with the , instead % – Dominik Feb 20 '17 at 22:22
Yes, it works without the %s as in the link you posted, but I am unable to reproduce your error. Can you provide addition information about the table as requested by others. eg:`show create table event` – prateeknischal Feb 21 '17 at 07:10
Table definitions are added to the initial question at the top. – Dominik Feb 21 '17 at 22:46
Which field are you using as a primary key, any constraints? I need to recreate the db to try to reproduce the error. – prateeknischal Feb 22 '17 at 07:21
It is a basic test example, it is a database with only one table, no PK no constraints. – Dominik Feb 23 '17 at 01:18

Insert fields into MySQL using Python

2 Answers2