]>
git.ipfire.org Git - location/libloc.git/blob - src/python/database.py
4 A lightweight wrapper around psycopg2.
6 Originally part of the Tornado framework. The tornado.database module
7 is slated for removal in Tornado 3.0, and it is now available separately
14 log
= logging
.getLogger("location.database")
17 class Connection(object):
19 A lightweight wrapper around MySQLdb DB-API connections.
21 The main value we provide is wrapping rows in a dict/object so that
22 columns can be accessed by name. Typical usage::
24 db = torndb.Connection("localhost", "mydatabase")
25 for article in db.query("SELECT * FROM articles"):
28 Cursors are hidden by the implementation, but other than that, the methods
29 are very similar to the DB-API.
31 We explicitly set the timezone to UTC and the character encoding to
32 UTF-8 on all connections to avoid time zone and encoding errors.
34 def __init__(self
, host
, database
, user
=None, password
=None):
36 self
.database
= database
41 "database" : database
,
43 "password" : password
,
44 "sslmode" : "require",
50 log
.error("Cannot connect to database on %s", self
.host
, exc_info
=True)
57 Closes this database connection.
59 if getattr(self
, "_db", None) is not None:
65 Closes the existing database connection and re-opens it.
69 self
._db
= psycopg2
.connect(**self
._db
_args
)
70 self
._db
.autocommit
= True
72 # Initialize the timezone setting.
73 self
.execute("SET TIMEZONE TO 'UTC'")
75 def query(self
, query
, *parameters
, **kwparameters
):
77 Returns a row list for the given query and parameters.
79 cursor
= self
._cursor
()
81 self
._execute
(cursor
, query
, parameters
, kwparameters
)
82 column_names
= [d
[0] for d
in cursor
.description
]
83 return [Row(zip(column_names
, row
)) for row
in cursor
]
87 def get(self
, query
, *parameters
, **kwparameters
):
89 Returns the first row returned for the given query.
91 rows
= self
.query(query
, *parameters
, **kwparameters
)
95 raise Exception("Multiple rows returned for Database.get() query")
99 def execute(self
, query
, *parameters
, **kwparameters
):
101 Executes the given query, returning the lastrowid from the query.
103 return self
.execute_lastrowid(query
, *parameters
, **kwparameters
)
105 def execute_lastrowid(self
, query
, *parameters
, **kwparameters
):
107 Executes the given query, returning the lastrowid from the query.
109 cursor
= self
._cursor
()
111 self
._execute
(cursor
, query
, parameters
, kwparameters
)
112 return cursor
.lastrowid
116 def execute_rowcount(self
, query
, *parameters
, **kwparameters
):
118 Executes the given query, returning the rowcount from the query.
120 cursor
= self
._cursor
()
122 self
._execute
(cursor
, query
, parameters
, kwparameters
)
123 return cursor
.rowcount
127 def executemany(self
, query
, parameters
):
129 Executes the given query against all the given param sequences.
131 We return the lastrowid from the query.
133 return self
.executemany_lastrowid(query
, parameters
)
135 def executemany_lastrowid(self
, query
, parameters
):
137 Executes the given query against all the given param sequences.
139 We return the lastrowid from the query.
141 cursor
= self
._cursor
()
143 cursor
.executemany(query
, parameters
)
144 return cursor
.lastrowid
148 def executemany_rowcount(self
, query
, parameters
):
150 Executes the given query against all the given param sequences.
152 We return the rowcount from the query.
154 cursor
= self
._cursor
()
157 cursor
.executemany(query
, parameters
)
158 return cursor
.rowcount
162 def _ensure_connected(self
):
164 log
.warning("Database connection was lost...")
169 self
._ensure
_connected
()
170 return self
._db
.cursor()
172 def _execute(self
, cursor
, query
, parameters
, kwparameters
):
173 log
.debug("SQL Query: %s" % (query
% (kwparameters
or parameters
)))
176 return cursor
.execute(query
, kwparameters
or parameters
)
177 except (OperationalError
, psycopg2
.ProgrammingError
):
178 log
.error("Error connecting to database on %s", self
.host
)
182 def transaction(self
):
183 return Transaction(self
)
187 """A dict that allows for object-like property access syntax."""
188 def __getattr__(self
, name
):
192 raise AttributeError(name
)
195 class Transaction(object):
196 def __init__(self
, db
):
199 self
.db
.execute("START TRANSACTION")
204 def __exit__(self
, exctype
, excvalue
, traceback
):
205 if exctype
is not None:
206 self
.db
.execute("ROLLBACK")
208 self
.db
.execute("COMMIT")
211 # Alias some common exceptions
212 IntegrityError
= psycopg2
.IntegrityError
213 OperationalError
= psycopg2
.OperationalError