CODEWITHSUNDEEP
pythonpython-3.xsqliteencoding
Sergej Herbert
Sergej Herbert

Reputation: 905

Set the (default) encoding of a SQLite database through python

I want to create a SQLite database through the python driver. As the application will run on different operating systems I want to explicitly specify the (default) encoding of text within the SQLite database.

Apparently I can only do this before a database is created with this command (see SQLite docs):

PRAGMA encoding = "UTF-8";

My problem is that the Python way to create/connect to a database does not specify (at least to my understanding) a way to set PRAGMAs (such as encoding) before the database is created (Python docs)

Is there therefore any way to specify the encoding through the python SQlite driver before/while a database is created?

The only workaround I currently see, which seems a bit hacky, is to run Shell commands through python, but since the SQLite CLI is not installed on the machines, this is no option.

Upvotes: 6

Views: 9405

Answers (1)

Jonathan R
Jonathan R

Reputation: 3948

You can create the database and change the encoding afterwards

>>> import sqlite3
>>> conn = sqlite3.connect('example.db')
>>> c = conn.cursor()
>>> c.execute('pragma encoding')
<sqlite3.Cursor object at 0x7fa641241e30>
>>> rows = c.fetchall()
>>> for row in rows:
...     print(row)
... 
('UTF-8',)
>>> c.execute('pragma encoding=UTF16')
<sqlite3.Cursor object at 0x7fa641241b20>
>>> c.execute('pragma encoding')
<sqlite3.Cursor object at 0x7fa641241e30>
>>> rows = c.fetchall()
>>> for row in rows:
...     print(row)
... 
('UTF-16le',)

Note that it is required to edit the database to make these changes permanent, eg:

>>> sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
...                                         id integer PRIMARY KEY,
...                                         name text NOT NULL,
...                                         begin_date text,
...                                         end_date text
...                                     ); """
>>> c.execute(sql_create_projects_table)
<sqlite3.Cursor object at 0x7f441ce90e30>
>>> rows = c.fetchall()
>>> for row in rows:
...     print(row)
...
>>> sql = ''' INSERT INTO projects(name,begin_date,end_date)
...               VALUES(?,?,?) '''
>>> project = ('Cool App with SQLite & Python', '2015-01-01', '2015-01-30');
>>> c.execute(sql, project)
<sqlite3.Cursor object at 0x7f441ce90e30>

If you do not at least add a table, the encoding will fall back to its default. Hope this helps.

Upvotes: 6

Related Questions