PyMySQL Insert NULL or a String

python mysql pymysql
Cronos87 picture Cronos87 · Feb 24, 2015 · Viewed 18.1k times · Source

I tried to insert a field (title) with PyMySQL that can be NULL or a string. But it doesn't work.

query = """
    INSERT INTO `chapter` (title, chapter, volume)
    VALUES ("%s", "%s", %d)
"""

cur.execute(query % (None, "001", 1))
cur.execute(query % ("Title", "001", 1))

This code inserts None into the database. If I remove the double quote around the first %s, it throws an error:

pymysql.err.InternalError: (1054, "Unknown column 'None' in 'field list'")

What can I do to insert NULL?

Answer

Retard picture Retard · Feb 24, 2015

1) Never use string formatting for SQL.

2) Try the following:

query = """
INSERT INTO `chapter` (title, chapter, volume)
VALUES (%s, %s, %s)
"""
cur.execute(query, (None, "001", 1))

Related questions

Using python to write mysql query to csv, need to show field names

I have the following: import MySQLdb as dbapi import sys import csv dbServer='localhost' dbPass='supersecretpassword' dbSchema='dbTest' dbUser='root' dbQuery='SELECT * FROM pbTest.Orders;' db=dbapi.connect(host=dbServer,user=dbUser,passwd=dbPass) cur=db.cursor() cur.execute(…

Read More
Pymysql Insert Into not working

I'm running this from PyDev in Eclipse... import pymysql conn = pymysql.connect(host='localhost', port=3306, user='userid', passwd='password', db='fan') cur = conn.cursor() print "writing to db" cur.execute("INSERT INTO cbs_transactions(leagueID) VALUES ('test val')") print "wrote …

Read More
What is PyMySQL and how does it differ from MySQLdb? Can it affect Django deployment?

I just solved some problems in my Django 1.3 app by using PyMySQL instead of MySQLdb. I followed this tutorial on how to make the switch: http://web-eng-help.blogspot.com/2010/09/install-mysql-5-for-python-26-and.html Now I want to know what PyMySQL …

Read More