Insert unicode value into sqlite from python code

Question

I have the following query for inserting values into sqlite3 db, which is made in python code;

insert into 'SplCharTest' ('Id','name','address','telefon') values (1.0, u'cust01', u'addr01  strasse 48908', 2131234213.0)

I am getting the following error when I try to execute this query;

sqlite3CursorObj.execute(dataInsertQueryForSheet)
sqlite3.OperationalError: near "'cust01'": syntax error

I noticed that the presence of u (unicode) in front of 'cust01' is making the problem. Could anybody please tell me how to insert unicode value into sqlite db. Simple usage of 'str' is not possible as I am getting the unicode values from an excel file using openpyxl package and there could be special characters.

Below given is my python code

dataInsertQueryForSheet = "insert into '"+tableNameForSheet+"' ("+columnNamesCSV+") values (%s)" % unicode(rowDataList)[1:(rowDataStrLen-1)]
sqlite3CursorObj.execute(dataInsertQueryForSheet)

Here variable tableNameForSheet contains the table name, columnNamesCSV contains all the columns names in CSV format and rowDataList contains all values to be inserted as an array list.

Any help will be very much appreciated. Thanks in advance.

You probably don't want to to use floats for telephone numbers especially since [sqlite will give at least 16 digits in an integer](http://stackoverflow.com/questions/3261220/storing-currency-values-in-sqlite3/3261239#3261239)]. Better still, use text type as there is nothing numerical about phone numbers (except for them being strings of digits). — msw, Jul 30 '14 at 17:30

score 4 · Accepted Answer · answered Jul 30 '14 at 17:10

SQLite always uses Unicode.

The easiest way to use strings while avoiding formatting problems or SQL injection attacks is to use parameters:

sql = "insert into "+tableNameForSheet+" ("+columnNamesCSV+") values (?,?,?,?)"
parameters = [1, u'cust01', u'addr01  strasse 48908', "2131234213"]
cursor.execute(sql, parameters)

Insert unicode value into sqlite from python code

1 Answers1