CODEWITHSUNDEEP
pythonpython-3.xsqlite
Supremenerd88
Supremenerd88

Reputation: 47

python 3.x write bytearray to sqlite3 blob field

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?

Upvotes: 3

Views: 6240

Answers (2)

Padraic Cunningham
Padraic Cunningham

Reputation: 180481

The problem is bytearrays have no tostring method so your code is erroring in the try/except but you don't see any output as you have a blanket except and you do nothing when an exception happens so the code runs but the error silently passes.

You can use Martijn's answer to insert correctly but in general if you are using a try/except catch the exceptions you expect not every exception, if you did your tostring error would have been obvious:

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

Which would give you:

 AttributeError: 'bytearray' object has no attribute 'tostring'

Upvotes: 2

Martijn Pieters
Martijn Pieters

Reputation: 1123640

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.

Upvotes: 8

Related Questions