Python2.7 Generate sql query with format() and buffer object from sqlite

Question

I try to build an sql query string with .format() and binary content (injections are no problem). The minimal example needs an example image in the same path.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

try:
    con = lite.connect(':memory:')

    cur = con.cursor()
    binary = lite.Binary(open("woman.jpg", "rb").read())

    cur.execute("CREATE TABLE 'Images' ('Data' BLOB)") 
    cur.execute("INSERT INTO Images(Data) VALUES (?)", (binary,) )

    query = "INSERT INTO Images(Data) VALUES ({0})".format(binary)
    cur.execute(query) # <- doesn't work obviously

    con.commit()    

except lite.Error, e:

    if con:
        con.rollback()

    print "Error %s:" % e.args[0]
    sys.exit(1)

finally:

    if con:
        con.close()

Just like the insert image example from http://zetcode.com/db/sqlitepythontutorial/ but with .format() query generation

Is there a way to achieve this with .format()? I want to use a consistent way of inserting data into the db.

Thank you.

Ber · Accepted Answer · 2013-03-07 16:23:38Z

2

You can format you BLOB data as a BLOB LITERAL value according to the SQLite docs, e.g.

X'53514C697465'

The binary data can be encoded as a hex digit string ahd then be inserted using format.

with open(path, 'rb') as f:
    "X'{0}'".format(r.read().encode('hex'))

edited Mar 7, 2013 at 16:23

answered Mar 7, 2013 at 8:57

Ber

42k16 gold badges79 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Janne Karila Over a year ago

Note also that the data must not be wrapped with Binary for this to work. binary = open("woman.jpg", "rb").read() works.

somebody Over a year ago

So the final correct way is: "X'{0}'".format(open(path, "rb").read().encode('hex') Thank you very much, too easy.

Collectives™ on Stack Overflow

Python2.7 Generate sql query with format() and buffer object from sqlite

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related