Ticket #87: sqlite3.py

File sqlite3.py, 6.0 KB (added by Jason Huggins, 15 years ago)
Line 
1"""
2SQLite3 backend for django.  Requires pysqlite2 (http://pysqlite.org/).
3"""
4
5from django.core.db import base, typecasts
6from django.core.db.dicthelpers import *
7from pysqlite2 import dbapi2 as Database
8DatabaseError = Database.DatabaseError
9
10# Register adaptors ###########################################################
11
12Database.register_converter("bool", lambda s: str(s) == '1')
13Database.register_converter("time", typecasts.typecast_time)
14Database.register_converter("date", typecasts.typecast_date)
15Database.register_converter("datetime", typecasts.typecast_timestamp)
16
17# Database wrapper ############################################################
18
19def utf8rowFactory(cursor, row):
20    def utf8(s):
21        if type(s) == unicode:
22            return s.encode("utf-8")
23        else:
24            return s
25    return [utf8(r) for r in row]
26
27class DatabaseWrapper:
28    def __init__(self):
29        self.connection = None
30        self.queries = []
31
32    def cursor(self):
33        from django.conf.settings import DATABASE_NAME, DEBUG
34        if self.connection is None:
35            self.connection = Database.connect(DATABASE_NAME, detect_types=Database.PARSE_DECLTYPES)
36            # register extract and date_trun functions
37            self.connection.create_function("django_extract", 2, _sqlite_extract)
38            self.connection.create_function("django_date_trunc", 2, _sqlite_date_trunc)
39        cursor = self.connection.cursor(factory=SQLiteCursorWrapper)
40        cursor.row_factory = utf8rowFactory
41        if DEBUG:
42            return base.CursorDebugWrapper(cursor, self)
43        else:
44            return cursor
45
46    def commit(self):
47        self.connection.commit()
48
49    def rollback(self):
50        if self.connection:
51            self.connection.rollback()
52
53    def close(self):
54        if self.connection is not None:
55            self.connection.close()
56            self.connection = None
57
58class SQLiteCursorWrapper(Database.Cursor):
59    """
60    Django uses "format" style placeholders, but pysqlite2 uses "qmark" style.
61    This fixes it -- but note that if you want to use a literal "%s" in a query,
62    you'll need to use "%%s" (which I belive is true of other wrappers as well).
63    """
64
65    def execute(self, query, params=[]):
66        query = self.convert_query(query, len(params))
67        return Database.Cursor.execute(self, query, params)
68
69    def executemany(self, query, params=[]):
70        query = self.convert_query(query, len(params[0]))
71        return Database.Cursor.executemany(self, query, params)
72
73    def convert_query(self, query, num_params):
74        # XXX this seems too simple to be correct... is this right?
75        return query % tuple("?" * num_params)
76
77# Helper functions ############################################################
78
79def get_last_insert_id(cursor, table_name, pk_name):
80    return cursor.lastrowid
81
82def get_date_extract_sql(lookup_type, table_name):
83    # lookup_type is 'year', 'month', 'day'
84    # sqlite doesn't support extract, so we fake it with the user-defined
85    # function _sqlite_extract that's registered in connect(), above.
86    return 'django_extract("%s", %s)' % (lookup_type.lower(), table_name)
87
88def _sqlite_extract(lookup_type, dt):
89    try:
90        dt = typecasts.typecast_timestamp(dt)
91    except (ValueError, TypeError):
92        return None
93    return str(getattr(dt, lookup_type))
94
95def get_date_trunc_sql(lookup_type, field_name):
96    # lookup_type is 'year', 'month', 'day'
97    # sqlite doesn't support DATE_TRUNC, so we fake it as above.
98    return 'django_date_trunc("%s", %s)' % (lookup_type.lower(), field_name)
99
100def _sqlite_date_trunc(lookup_type, dt):
101    try:
102        dt = typecasts.typecast_timestamp(dt)
103    except (ValueError, TypeError):
104        return None
105    if lookup_type == 'year':
106        return "%i-01-01 00:00:00" % dt.year
107    elif lookup_type == 'month':
108        return "%i-%02i-01 00:00:00" % (dt.year, dt.month)
109    elif lookup_type == 'day':
110        return "%i-%02i-%02i 00:00:00" % (dt.year, dt.month, dt.day)
111
112def get_table_list(cursor):
113    raise NotImplementedError
114
115def get_relations(cursor, table_name):
116    raise NotImplementedError
117
118# Operators and fields ########################################################
119
120OPERATOR_MAPPING = {
121    'exact':        '=',
122    'iexact':       'LIKE',
123    'contains':     'LIKE',
124    'icontains':    'LIKE',
125    'ne':           '!=',
126    'gt':           '>',
127    'gte':          '>=',
128    'lt':           '<',
129    'lte':          '<=',
130    'startswith':   'LIKE',
131    'endswith':     'LIKE',
132    'istartswith':  'LIKE',
133    'iendswith':    'LIKE',
134}
135
136# SQLite doesn't actually support most of these types, but it "does the right
137# thing" given more verbose field definitions, so leave them as is so that
138# schema inspection is more useful.
139DATA_TYPES = {
140    'AutoField':                    'integer',
141    'BooleanField':                 'bool',
142    'CharField':                    'varchar(%(maxlength)s)',
143    'CommaSeparatedIntegerField':   'varchar(%(maxlength)s)',
144    'DateField':                    'date',
145    'DateTimeField':                'datetime',
146    'EmailField':                   'varchar(75)',
147    'FileField':                    'varchar(100)',
148    'FloatField':                   'numeric(%(max_digits)s, %(decimal_places)s)',
149    'ImageField':                   'varchar(100)',
150    'IntegerField':                 'integer',
151    'IPAddressField':               'char(15)',
152    'ManyToManyField':              None,
153    'NullBooleanField':             'bool',
154    'OneToOneField':                'integer',
155    'PhoneNumberField':             'varchar(20)',
156    'PositiveIntegerField':         'integer unsigned',
157    'PositiveSmallIntegerField':    'smallint unsigned',
158    'SlugField':                    'varchar(50)',
159    'SmallIntegerField':            'smallint',
160    'TextField':                    'text',
161    'TimeField':                    'time',
162    'URLField':                     'varchar(200)',
163    'USStateField':                 'varchar(2)',
164    'XMLField':                     'text',
165}
166
167DATA_TYPES_REVERSE = {}
168
169EMPTY_STR_EQUIV = ''
Back to Top