Sqlite3.Binary gives TypeError: buffer object expected

Question

I have a list which I extracted from a txt file as follows:

authors = re.match('#@(.*)', line)
if authors:
   authors_list = authors.group(1).split(",")

Now I need to insert these into my database, which should be done as follows:

for a in authors_list:
    c.execute("INSERT INTO authors(Name) VALUES (?)", (a))

But when I do that I get the following error:

ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 17 supplied.

So I did some research and found that I should change my variable a into a tuple (a, ) as such:

c.execute("INSERT INTO authors(Name) VALUES (?)", (a,))

And I also tried this:

c.execute("INSERT INTO authors(Name) VALUES (?)", ((a,)))

But got the following error:

ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

So I did more research and found that I should use sqlite3's Binary function. So I tried the following:

c.execute("INSERT INTO authors(Name) VALUES (?)", (sqlite3.Binary((a,))))

And got the following error:

TypeError: buffer object expected

I keep going back and forth between all three errors no mater what combination I try. Any help would be appreciated. I don't know what I'm doing wrong.

Martijn Pieters · Accepted Answer · 2015-04-13 19:56:01Z

1

You are passing in a tuple, not a bytestring:

sqlite3.Binary((a,))

Create a tuple with the result of sqlite3.Binary(), having passed in just a:

(sqlite3.Binary(a),)

The whole statement is then run as:

c.execute("INSERT INTO authors(Name) VALUES (?)",
          (sqlite3.Binary(a),))

However, if this is supposed to be text, you'd normally decode the bytes to a string instead of trying to insert binary data:

c.execute("INSERT INTO authors(Name) VALUES (?)",
          (a.decode('utf8'),))

This does assume that your text is encoded using the UTF-8 codec; adjust the decoding as needed to match your data.

edited Apr 13, 2015 at 19:56

answered Apr 13, 2015 at 19:48

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

1 Comment

BKS Over a year ago

The worked beautifully! Thanks Martijn. :) I don't know why I didn't see that!

Collectives™ on Stack Overflow

Sqlite3.Binary gives TypeError: buffer object expected

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related