Relationship between SQLite and Python 2.X

Question

I am a little puzzled about the relationship between sqlite3 and python 2.x

Here is the output:

$ python
Python 2.7.2+ (default, Jul 20 2012, 22:12:53) 
[GCC 4.6.1] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.7.7'

The '3.7.7' is supposed to be the version of sqlite on my Ubuntu OS.

However, when I tried

sqlite3 test.db 
bash: sqlite3: command not found

which means sqlite3 is not installed on this machine.

Then how can I use PRAGMA Statements like "PRAGMA encoding = "UTF-8";" when creating database in python?

• abarnert: You're really asking two questions here; one is about Ubuntu's packaging system, and the other is about sqlite3 (and Python). For the second one: Why do you want to set PRAGMA encoding = "UTF-8" in the first place? SQLite defaults to UTF-8, and when Python creates a new database, it always uses that default.

Answer 1

First:

The '3.7.7' is supposed to be the version of sqlite on my Ubuntu OS.

No. It's the version of the sqlite library that Python was built against.

If Python was built against a static library, there's no reason to expect that to be related in any way to anything else on your system. Even if it was built against a shared library, it doesn't have to be the same shared library that's used by your distro's default sqlite package (although it typically will be).

And, even if Python was built against the same shared lib that's your distro's default shared lib: Ubuntu, like many distros, allows you to install most libfoo.so shared libraries without installing the foo command-line tool.

In particular, if you look at the Ubuntu packages (at least for recent versions), there are sqlite shared libs in the libsqlite3 package, and both the python and sqlite3 depend on that libsqlite3 package.

So, if you want the sqlite3 command-line tool, you have to install it; you don't get it automatically by installing Python.

---

Meanwhile:

Is there any way to use PRAGMA encoding = "UTF-8" inside a python code?

Sure. A PRAGMA is just a SQL statement that affects the rest of the session. It doesn't matter whether you execute it in the command-line tool, or in a Python execute command, or by loading a SQL script, etc. Just execute it.

However, as you point out in the comments, according to the docs, this particular pragma won't have any effect unless you can run it before connecting to the database. Because DB-API 2.0 (the API that the Python sqlite3 module, and most other Python database modules, are all built to) doesn't have any way of executing statements in an unconnected state, there's no easy way around this.

You can, of course, look into the various alternate (generally not as well tested, and slower) sqlite3 implementations for Python, or fork and modify the source to pysqlite (the module that is periodically included into the stdlib as sqlite3).

If you want to do this, the place to look is the pysqlite_connection_init function in connection.c. You probably don't want to add general-purpose pre-connect pragma support, but rather a way to control the encoding via another parameter to connect. See the open docs for sqlite3 to see what you can usefully do here. (And then you probably want to file a bug and submit a patch. It won't ever make it into Python 2.x, but anything that gets accepted in pysqlite will eventually end up in Python 3.x.)

But the easier way to do what you want is exactly what you were trying to do: create the database outside of Python. Just install the sqlite3 package in your favorite way (apt-get, one of the GUI wrappers, whatever) and use it.

---

One last thing: From what I can tell, depending on your version, Python's module will either use UTF-8 explicitly, or use the default encoding, which will be UTF-8. So, you don't need to do anything here.

• Gaby Solis: Well, I think I need to first connect to a databse like conn = sqlite3.connect('example.db') from docs.python.org/2/library/sqlite3.html . Then the database is already created with default encoding. "It is not possible to change the text encoding of a database after it has been created and any attempt to do so will be silently ignored." sqlite.org/pragma.html If you execute A PRAGMA, it wont affect the existing database.
• abarnert: @GabySolis: You're right, Python's DB-API 2.0 doesn't provide any way to run commands before creating/loading the main database, or to create a database directly out of a dump file. So, if you want to create a database with any commands run beforehand, you'll have to do so outside of Python.