0

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?

  • Do you want a string or a blob? – CL. Jan 04 '16 at 21:11
  • I want to insert a string into a blob – shonlev123 Jan 05 '16 at 12:31
  • A string is not a blob. Do you want to have a string value or a blob value? What is the purpose of the values in this column? – CL. Jan 05 '16 at 12:32
  • I want to convert my string to binary data so I can insert it into a blob column – shonlev123 Jan 05 '16 at 14:29
  • You can insert the string directly into the blob column. – CL. Jan 05 '16 at 15:09
  • This is what I did in the example I gave but it didn't work. – shonlev123 Jan 06 '16 at 09:49
  • Works for me. Would you please tell use what actually happens? – CL. Jan 06 '16 at 09:55
  • I can see it in the blob column but as a string and not as a binary data inside a blob. When I look at this db in 'SqliteBrowser' I see in the blob column the actual string, and I want to see 'blob' and to see the string only when I click on it. I hope it's clear. – shonlev123 Jan 06 '16 at 10:36
  • The code you've shown inserts a blob. Try `SELECT DISTINCT typeof(BlobColumn) FROM MyTable;`. You have a problem with SQLiteBrowser. – CL. Jan 06 '16 at 12:29

1 Answers1

0

First, your syntax for execute() is wrong:

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

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 (?)'
MarkWatney
  • 144
  • 6
  • When I open the database using SqliteBrowser tool, I see that it didn't saved in a blob column. – shonlev123 Jan 04 '16 at 14:40
  • `execute()` works just fine with a list instead of a tuple. – CL. Jan 04 '16 at 21:11
  • @shonlev123 I think I mixed something up with the `execute()` function. Can you try something else instead of the SqliteBrowser? If you open a terminal and go to your database and open it with `sqlite3 database.db` followed by your `SELECT` statement? Which Python and SQLite version are you using? – MarkWatney Jan 05 '16 at 17:04