python, mysql, inserting string into table, error 1054

Question

I am having the problem OperationalError: (1054, "Unknown column 'Ellie' in 'field list'") With the code below, I'm trying to insert data from json into a my sql database. The problem happens whenever I try to insert a string in this case "Ellie" This is something do to with string interpolation I think but I cant get it to work despite trying some other solutions I have seen here..

CREATE TABLE

con = MySQLdb.connect('localhost','root','','tweetsdb01')
cursor = con.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS User(user_id BIGINT NOT NULL PRIMARY KEY, username varchar(25) NOT NULL,user varchar(25) NOT NULL) CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB")
con.commit()

INSERT INTO

def populate_user(a,b,c):
    con = MySQLdb.connect('localhost','root','','tweetsdb01')
    cursor = con.cursor()
    cursor.execute("INSERT INTO User(user_id,username,user) VALUES(%s,%s,%s)"%(a,b,c))
    con.commit() 
    cursor.close()

READ FILE- this calls the populate method above

def read(file):
    json_data=open(file)
    tweets = []
    for i in range(10):
        tweet = json.loads(json_data.readline())

    populate_user(tweet['from_user_id'],tweet['from_user_name'],tweet['from_user'])

if it is the cursor, during open error means. In the cursor select statement where clause it is expecting numeric value, but you passed character value. — IoN
– IoN, Commented Nov 2, 2018 at 0:00

unutbu · Accepted Answer · 2012-11-14 09:45:10Z

4

Use parametrized SQL:

cursor.execute("INSERT INTO User(user_id,username,user) VALUES (%s,%s,%s)", (a,b,c))

(Notice the values (a,b,c) are passed to the function execute as a second argument, not as part of the first argument through string interpolation). MySQLdb will properly quote the arguments for you.

PS. As Vajk Hermecz notes, the problem occurs because the string 'Ellie' is not being properly quoted.

When you do the string interpolation with "(%s,)" % (a,) you get (Ellie,) whereas what you really want is ('Ellie',). But don't bother doing the quoting yourself. It is safer and easier to use parametrized SQL.

edited Nov 14, 2012 at 9:45

answered Nov 14, 2012 at 9:30

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guye Incognito Over a year ago

ok, Thanks! I was also having a utf encoding problem.. I think both issues in this new domain for me, at the same time was making me crazy!!

Pitto Over a year ago

What if I have int, date and other types in my mysql? %s will do just fine?

Vajk Hermecz · Accepted Answer · 2012-11-14 09:37:10Z

Your problem is that you are adding the values into the query without any escaping.... Now it is just broken. You could do something like:

cursor.execute("INSERT INTO User(user_id,username,user) VALUES(\"%s\",\"%s\",\"%s\")"%(a,b,c))

But that would just introduce SQL INJECTION into your code.

NEVER construct SQL statements with concatenating query and data. Your parametrized queries...

The proper solution here would be:

cursor.execute("INSERT INTO User(user_id,username,user) VALUES(%s,%s,%s)", (a,b,c))

So, the problem with your code was that you have used the % operator which does string formatting, and finally you just gave one parameter to cursor.execute. Now the proper solution, is that instead of doing the string formatting yourself, you give the query part to cursor.execute in the first parameter, and provide the tuple with arguments in the second parameter.

Collectives™ on Stack Overflow

python, mysql, inserting string into table, error 1054

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related