Error when inserting strings into SQLite database from Python code

Question

When I want to insert a string from Python code to SQLite database I get this error:

sqlite3.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.

This is the insert statement:

cur.execute("insert into links (url, title, ...) values (:url, :title, ...)", locals())

The string came into existence as follows:

soup = BeautifulSoup(html.read(), fromEncoding="utf-8")
html.close()
for i in soup.findAll('a'):
  url = i['href']
  title = i.renderContents()

Could you advise me how to insert the string into SQLite database?

EDIT: I found out that url string was OK when inserting to another table. The type of url string was unicode. The problem is when inserting title string. The type of title string is str.

I tried:

title = unicode(i.renderContents())

but this ends with error:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 44: ordinal not in range(128)

thank you

use href=True keyword parameter; there could be a elements without href attribute. You might get KeyError otherwise 2. Usage of locals() outside debugging is frowned upon. — jfs
– jfs, Commented Oct 25, 2011 at 23:09
@J.F.Sebastian That's good to catch KeyError though this wasn't the case. Why is usage of locals() not recommended outside debugging? — xralf
– xralf, Commented Oct 26, 2011 at 8:49
The reason I heard is that locals() either hides problems with the structure of your code or at best it makes the code less readable and more brittle. — jfs
– jfs, Commented Oct 26, 2011 at 9:31

Roger Binns · Accepted Answer · 2011-10-25 21:51:21Z

3

SQLite only stores unicode strings. It is most likely the URL that isn't unicode so you'll need to convert it.

You can store the URL as a blob (binary) instead but that will make life more complicated later.

answered Oct 25, 2011 at 21:51

Roger Binns

3,3582 gold badges30 silver badges33 bronze badges

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

1 Comment

xralf Over a year ago

You're right. My string wasn't unicode. It was title string, not url string. See question edit.

jfs · Accepted Answer · 2011-10-26 09:20:40Z

1

Though it is not strictly necessary for an url you could store it as Unicode.

BeautifulSoup works with Unicode.

>>> from BeautifulSoup import BeautifulSoup
>>> soup = BeautifulSoup("""<a href="ascii">""", fromEncoding="utf-8")
>>> isinstance(soup('a', href=True)[0]['href'], unicode)
True

>>> soup = BeautifulSoup("""<a href="αβγ">""", fromEncoding="utf-8")
>>> soup('a', href=True)[0]['href']
u'\u03b1\u03b2\u03b3'

In both cases the url is unicode.

You could call isinstance() or type() to find out what type the url has.

You could specify encoding=None to get Unicode:

i.renderContents(encoding=None)

In general it might be helpful to use dir(obj), help(obj.method) in a interactive Python console. See also Printing Document.

edited Oct 26, 2011 at 9:20

answered Oct 25, 2011 at 22:43

jfs

417k210 gold badges1k silver badges1.7k bronze badges

1 Comment

xralf Over a year ago

The isinstance and type helped to find out that there wasn't problem with url string but the problem was with title string. See question edit.

Collectives™ on Stack Overflow

Error when inserting strings into SQLite database from Python code

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related