How to insert string into the BLOB column in Python

Question

I am trying to insert string into BLOB column using sqlite3 in Python. I saw some examples using binary file but I am trying to do it without files. Here is my code-

import sqlite3

c = sqlite3.connect(path_to_db)

# my string
ablob = 'Hello world!'
sql_statement = 'INSERT into table VALUES (?)'
c.execute(sql_statement, [sqlite3.Binary(ablob)])
c.commit()
c.close()

After execution, new column with the string is added but not in a blob column. Does anyone know what is the problem?

Answer 1

First, your syntax for execute() is wrong:

c.execute(sql_statement, [sqlite3.Binary(ablob)])

• You should use parentheses for the tuple instead of []
• if you provide only one element in the tupl sqlite3.Binary(ablob) to execute() you should add a comma to be recognized as tuple. See answers to sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied

The correct syntax would be:

c.execute(sql_statement, (sqlite3.Binary(ablob),))

I tested the following code:

import sqlite3

c = sqlite3.connect(path_to_db)

# my string
ablob = 'Hello world!'
sql_statement = 'INSERT into table VALUES (?)'
c.execute(sql_statement, (sqlite3.Binary(ablob),))
c.commit()
c.close()
return 0

And it works for me. I created the table with the command

CREATE TABLE table (blob BLOB);

and after executing the script there is an entry in this table:

sqlite> SELECT * from table;
Hello world!

Finally, why do you use sql_statement = '''INSERT into table VALUES (?)'''? I removed two apostrophes each and the script works as expected with this line, too:

sql_statement = 'INSERT into tab VALUES (?)'