MishaVacic MishaVacic - 1 year ago 64
MySQL Question

MySQLdb placeholder implementation does not work

Python’s MySQLdb module should implement placeholders using format specifiers in the SQL statement string. I am following an exemple from the MYSQL CookBook

import sys
import MySQLdb
import Cookbook

try:
conn = Cookbook.connect ()
print("Connected")
except MySQLdb.Error as e:
print("Cannot connect to server")
print("Error code:", e.args[0])
print("Error message:", e.args[1])
sys.exit (1)

cursor = conn.cursor ()
cursor.execute ("""
INSERT INTO profile (name,birth,color,foods,cats)
VALUES(%s,%s,%s,%s,%s)
""",("Josef", "1971-01-01", None, "eggroll", 4))


But when I check from the shell

mysql> SELECT * FROM profile WHERE name LIKE 'J%';
+----+--------+------------+-------+----------------+------+
| id | name | birth | color | foods | cats |
+----+--------+------------+-------+----------------+------+
| 7 | Joanna | 1952-08-20 | green | lutefisk,fadge | 0 |
+----+--------+------------+-------+----------------+------+


It is obvious that nothing is inserted.Why?
If I add cursor.commit as suggested

cursor.commit()
AttributeError: 'Cursor' object has no attribute 'commit'

Answer Source

You are not committing the transaction.

Add conn.commit() in the end after executing the query.

cursor = conn.cursor()
cursor.execute (""" 
            INSERT INTO profile (name,birth,color,foods,cats)
            VALUES(%s,%s,%s,%s,%s)
            """,("Josef", "1971-01-01", None, "eggroll", 4))
conn.commit()
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download