CODEWITHSUNDEEP
pythonsqlite
Mtoypc
Mtoypc

Reputation: 494

SQLite3 request doesn't work because of ' character

I created a python script to create a sqlite3 database with one table. This table is composed of 2 columns : one for the id (int) and the other for the associated sentence (text). Here is the code :

#!/usr/bin/python

import sqlite3

i=0
con = sqlite3.connect("database.db")
cur = con.cursor()  
cur.execute('''CREATE TABLE data(data_id int, sentence text)''')  

with open("data.txt", "r") as ins:
        for line in ins:
        i = i + 1
        cur.execute("insert into data(data_id, sentence) values ("+str(i)+",'"+line+"')")

con.commit()
con.close()

As you can see, this code will read the file data.txt which is a file that contains sentences on each line.

When I try to execute the script, I get this error :

Traceback (most recent call last):
  File "scriptData.py", line 13, in <module>
    cur.execute("insert into data(data_id, sentence) values ("+str(i)+",'"+line+"')")
sqlite3.OperationalError: near "che": syntax error

This is due to the fact that in my file data.txt, some lines have the ' character which is a problem. I tried to protect this character by doing \' in my data.txt file but it didn't work.
The problem is that I need to keep ' in my sentences. I'm not a python programmer so my knowledge is very limited. Do you have any idea to make this script work ?

Upvotes: 1

Views: 359

Answers (2)

Colonel Thirty Two
Colonel Thirty Two

Reputation: 26609

Do not concatenate data into SQL queries. Doing so is dangerous; malicious users can perform SQL injection attacks. Use query parameters instead.

cur.execute("insert into data(data_id, sentence) values (?, ?)", (i, line))

Also note that the query can be made more efficient by using executemany instead of a for loop:

cur.execute("insert into data(data_id, sentence) values (?, ?)", enumerate(ins))

Upvotes: 3

Eugene Soldatov
Eugene Soldatov

Reputation: 10145

You need to use escaping to prevent such situation when you pass params to SQL queries:

cur.execute("insert into data(data_id, sentence) values (?, ?)", (i, line))

Upvotes: 4

Related Questions