CODEWITHSUNDEEP
pythonsqlite
BKS
BKS

Reputation: 2333

Sqlite3.Binary gives TypeError: buffer object expected

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.

Upvotes: 0

Views: 1446

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123720

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.

Upvotes: 1

Related Questions