Ticket #87: postgresql.py

File postgresql.py, 6.6 KB (added by Jason Huggins, 15 years ago)
Line 
1"""
2PostgreSQL database backend for Django.
3
4Requires psycopg 1: http://initd.org/projects/psycopg1
5"""
6
7from django.core.db import base, typecasts
8import psycopg as Database
9
10DatabaseError = Database.DatabaseError
11
12class DatabaseWrapper:
13    def __init__(self):
14        self.connection = None
15        self.queries = []
16
17    def cursor(self):
18        from django.conf.settings import DATABASE_USER, DATABASE_NAME, DATABASE_HOST, DATABASE_PASSWORD, DEBUG, TIME_ZONE
19        if self.connection is None:
20            if DATABASE_NAME == '' or DATABASE_USER == '':
21                from django.core.exceptions import ImproperlyConfigured
22                raise ImproperlyConfigured, "You need to specify both DATABASE_NAME and DATABASE_USER in your Django settings file."
23            conn_string = "user=%s dbname=%s" % (DATABASE_USER, DATABASE_NAME)
24            if DATABASE_PASSWORD:
25                conn_string += " password=%s" % DATABASE_PASSWORD
26            if DATABASE_HOST:
27                conn_string += " host=%s" % DATABASE_HOST
28            self.connection = Database.connect(conn_string)
29            self.connection.set_isolation_level(1) # make transactions transparent to all cursors
30        cursor = self.connection.cursor()
31        cursor.execute("SET TIME ZONE %s", [TIME_ZONE])
32        if DEBUG:
33            return base.CursorDebugWrapper(cursor, self)
34        return cursor
35
36    def commit(self):
37        return self.connection.commit()
38
39    def rollback(self):
40        if self.connection:
41            return self.connection.rollback()
42
43    def close(self):
44        if self.connection is not None:
45            self.connection.close()
46            self.connection = None
47
48def dictfetchone(cursor):
49    "Returns a row from the cursor as a dict"
50    return cursor.dictfetchone()
51
52def dictfetchmany(cursor, number):
53    "Returns a certain number of rows from a cursor as a dict"
54    return cursor.dictfetchmany(number)
55
56def dictfetchall(cursor):
57    "Returns all rows from a cursor as a dict"
58    return cursor.dictfetchall()
59
60def get_last_insert_id(cursor, table_name, pk_name):
61    cursor.execute("SELECT CURRVAL('%s_%s_seq')" % (table_name, pk_name))
62    return cursor.fetchone()[0]
63
64def get_date_extract_sql(lookup_type, table_name):
65    # lookup_type is 'year', 'month', 'day'
66    # http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
67    return "EXTRACT('%s' FROM %s)" % (lookup_type, table_name)
68
69def get_date_trunc_sql(lookup_type, field_name):
70    # lookup_type is 'year', 'month', 'day'
71    # http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
72    return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
73
74def get_table_list(cursor):
75    "Returns a list of table names in the current database."
76    cursor.execute("""
77        SELECT c.relname
78        FROM pg_catalog.pg_class c
79        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
80        WHERE c.relkind IN ('r', 'v', '')
81            AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
82            AND pg_catalog.pg_table_is_visible(c.oid)""")
83    return [row[0] for row in cursor.fetchall()]
84
85def get_relations(cursor, table_name):
86    """
87    Returns a dictionary of {field_index: (field_index_other_table, other_table)}
88    representing all relationships to the given table. Indexes are 0-based.
89    """
90    cursor.execute("""
91        SELECT con.conkey, con.confkey, c2.relname
92        FROM pg_constraint con, pg_class c1, pg_class c2
93        WHERE c1.oid = con.conrelid
94            AND c2.oid = con.confrelid
95            AND c1.relname = %s
96            AND con.contype = 'f'""", [table_name])
97    relations = {}
98    for row in cursor.fetchall():
99        try:
100            # row[0] and row[1] are like "{2}", so strip the curly braces.
101            relations[int(row[0][1:-1]) - 1] = (int(row[1][1:-1]) - 1, row[2])
102        except ValueError:
103            continue
104    return relations
105
106# Register these custom typecasts, because Django expects dates/times to be
107# in Python's native (standard-library) datetime/time format, whereas psycopg
108# use mx.DateTime by default.
109try:
110    Database.register_type(Database.new_type((1082,), "DATE", typecasts.typecast_date))
111except AttributeError:
112    raise Exception, "You appear to be using psycopg version 2, which isn't supported yet, because it's still in beta. Use psycopg version 1 instead: http://initd.org/projects/psycopg1"
113Database.register_type(Database.new_type((1083,1266), "TIME", typecasts.typecast_time))
114Database.register_type(Database.new_type((1114,1184), "TIMESTAMP", typecasts.typecast_timestamp))
115Database.register_type(Database.new_type((16,), "BOOLEAN", typecasts.typecast_boolean))
116
117OPERATOR_MAPPING = {
118    'exact': '=',
119    'iexact': 'ILIKE',
120    'contains': 'LIKE',
121    'icontains': 'ILIKE',
122    'ne': '!=',
123    'gt': '>',
124    'gte': '>=',
125    'lt': '<',
126    'lte': '<=',
127    'startswith': 'LIKE',
128    'endswith': 'LIKE',
129    'istartswith': 'ILIKE',
130    'iendswith': 'ILIKE',
131}
132
133# This dictionary maps Field objects to their associated PostgreSQL column
134# types, as strings. Column-type strings can contain format strings; they'll
135# be interpolated against the values of Field.__dict__ before being output.
136# If a column type is set to None, it won't be included in the output.
137DATA_TYPES = {
138    'AutoField':         'serial',
139    'BooleanField':      'boolean',
140    'CharField':         'varchar(%(maxlength)s)',
141    'CommaSeparatedIntegerField': 'varchar(%(maxlength)s)',
142    'DateField':         'date',
143    'DateTimeField':     'timestamp with time zone',
144    'EmailField':        'varchar(75)',
145    'FileField':         'varchar(100)',
146    'FloatField':        'numeric(%(max_digits)s, %(decimal_places)s)',
147    'ImageField':        'varchar(100)',
148    'IntegerField':      'integer',
149    'IPAddressField':    'inet',
150    'ManyToManyField':   None,
151    'NullBooleanField':  'boolean',
152    'OneToOneField':     'integer',
153    'PhoneNumberField':  'varchar(20)',
154    'PositiveIntegerField': 'integer CHECK (%(name)s >= 0)',
155    'PositiveSmallIntegerField': 'smallint CHECK (%(name)s >= 0)',
156    'SlugField':         'varchar(50)',
157    'SmallIntegerField': 'smallint',
158    'TextField':         'text',
159    'TimeField':         'time',
160    'URLField':          'varchar(200)',
161    'USStateField':      'varchar(2)',
162    'XMLField':          'text',
163}
164
165# Maps type codes to Django Field types.
166DATA_TYPES_REVERSE = {
167    16: 'BooleanField',
168    21: 'SmallIntegerField',
169    23: 'IntegerField',
170    25: 'TextField',
171    869: 'IPAddressField',
172    1043: 'CharField',
173    1082: 'DateField',
174    1083: 'TimeField',
175    1114: 'DateTimeField',
176    1184: 'DateTimeField',
177    1266: 'TimeField',
178    1700: 'FloatField',
179}
180
181EMPTY_STR_EQUIV = ''
Back to Top