Using split by tab in Python adds a character to text which raises error in MySQL

Question

I want to read lines of a tab delimited file and split each line by tab. So here is the data:

File.txt:

524550170  {"text":He just proved he has no idea what he's talking about}  Sat Oct 31 15:40:00 2015

And here is my code:

con = mdb.connect('localhost', 'u', 'p', 'testdb');
cur = con.cursor()
cur.execute("CREATE TABLE user_profiles(user_id INT, json_source VARCHAR(1000), user_timestamp TIMESTAMP)")
with open('~/File.txt', 'r') as f:
    for row in f:
        data = row.split('\t')
        query="""insert into user_profiles (user_id, json_source, user_timestamp) values ('%s', '%s', '%s')""" %(data[0], data[1].encode('utf-8'), data[2])
        cur.execute(query)

But when I run the code and print the data[1] it adds a \ to "he's" part of the text and changes it to "he\'s". And when I want to add this into a MySQL table, it gives me a 1064 error, as following:

(1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'s talking about.' at line 2')

Any idea what is wrong here?

It's `\'` because the string is `'You have...near \'s talking about.'`(see the `''`). I think your SQL command is wrong. — Remi Guan, Nov 01 '15 at 03:27
[Here](http://stackoverflow.com/a/23515348/5299236) is what does *1064 error* mean. — Remi Guan, Nov 01 '15 at 03:33
Are there actually any tabs in that row? You didn't paste any into the question... — MattDMo, Nov 01 '15 at 03:40

score 0 · Accepted Answer · answered Nov 01 '15 at 03:38

You are manually constructing the query via string formatting - this is not only dangerous in terms of security (you are becoming vulnerable to SQL injections), but also leads to problems like this.

Let your database driver worry about it and make a parameterized query:

query = """
    INSERT INTO user_profiles 
        (user_id, json_source, user_timestamp) 
    VALUES 
        (%s, %s, %s)
"""
cur.execute(query, (data[0], data[1].encode('utf-8'), data[2]))

Using split by tab in Python adds a character to text which raises error in MySQL

1 Answers1