python 3.x write bytearray to sqlite3 blob field

Question

I have some information that I want to save into a blob field into a sqlite DB.

for example:

out = [83, 105, 108, 105, 99, 111, 110, 32, 86, 97, 108, 108, 101, 121, 32, 40, 84, 86, 32, 115, 101, 114, 105, 101, 115, 41, 13, 10, 70, 114, 111, 109, 32, 87, 105, 107, 105, 112, 101, 100, 105, 97, 44, 32, 116, 104, 101, 32, 102, 114, 101, 101, 32, 101, 110, 99, 121, 99, 108, 111, 112, 101, 100, 105, 97, 13, 10, 83, 105, 108, 105, 99, 111, 110, 32, 86, 97, 108, 108, 101, 121, 32, 105, 115, 32, 97, 110, 32, 65, 109, 101, 114, 105, 99, 97, 110, 32, 116, 101, 108, 101, 118, 105, 115, 105, 111, 110, 32, 99, 111, 109, 101, 100, 121, 32, 115, 101, 114, 105, 101, 115, 32, 99, 114, 101, 97, 116, 101]

blob = bytearray(out)
print(blob)

in these case the output is:

bytearray(b'Silicon Valley (TV series)\r\nFrom Wikipedia, the free encyclopedia\r\nSilicon Valley is an American television comedy series create')

(I've copied it from wikipedia)

I need to insert a new record with this information. I have this code:

import sqlite3

out = [83, 105, 108, 105, 99, 111, 110, 32, 86, 97, 108, 108, 101, 121, 32, 40, 84, 86, 32, 115, 101, 114, 105, 101, 115, 41,
13, 10, 70, 114, 111, 109, 32, 87, 105, 107, 105, 112, 101, 100, 105, 97, 44, 32, 116, 104, 101, 32, 102, 114, 101, 101,
 32, 101, 110, 99, 121, 99, 108, 111, 112, 101, 100, 105, 97, 13, 10, 83, 105, 108, 105, 99, 111, 110, 32, 86, 97, 108,
108, 101, 121, 32, 105, 115, 32, 97, 110, 32, 65, 109, 101, 114, 105, 99, 97, 110, 32, 116, 101, 108, 101, 118, 105, 115
, 105, 111, 110, 32, 99, 111, 109, 101, 100, 121, 32, 115, 101, 114, 105, 101, 115, 32, 99, 114, 101, 97, 116, 101]

blob = bytearray(out)
print(blob)

Con = sqlite3.connect('info.db')
Cur = Con.cursor()

Cur.execute("create table t_info (Primary_Key INTEGER PRIMARY KEY ASC, Info_Value BLOB )")

try:
    Cur.execute("insert into t_info (Primary_Key, Info_Value) values (1, X'" + blob.tostring() + "')")
except:
    print('error')

Can you tell me where my mistake is?

Answer 1

Your blanket try:..except is masking the fact that bytearray objects have no .tostring() method. Even if there was such a method, converting the bytes to a string is the wrong approach here.

Don't use string interpolation. Use SQL parameters:

Cur.execute("insert into t_info (Primary_Key, Info_Value) values (1, ?)", (blob,))

This lets the database library handle the type for you, and protects you against SQL injection attacks at the same time.

The ? in the query is a SQL parameter; each value from the second argument is used to fill a parameter.

Demo:

>>> Cur.execute("insert into t_info (Primary_Key, Info_Value) values (1, ?)", (blob,))
<sqlite3.Cursor object at 0x10a0ca810>
>>> Con.commit()
>>> Cur = Con.cursor()
>>> Cur.execute('select * from t_info')
<sqlite3.Cursor object at 0x10a0caab0>
>>> list(Cur)
[(1, b'Silicon Valley (TV series)\r\nFrom Wikipedia, the free encyclopedia\r\nSilicon Valley is an American television comedy series create')]

Note that on querying, the column is returned as a bytes object.