Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 239 lines (201 sloc) 8.289 kb
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
1 #!/usr/bin/env python
2 #
3 # Copyright 2009 Facebook
4 #
5 # Licensed under the Apache License, Version 2.0 (the "License"); you may
6 # not use this file except in compliance with the License. You may obtain
7 # a copy of the License at
8 #
9 # http://www.apache.org/licenses/LICENSE-2.0
10 #
11 # Unless required by applicable law or agreed to in writing, software
12 # distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
13 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
14 # License for the specific language governing permissions and limitations
15 # under the License.
16
17 """A lightweight wrapper around MySQLdb."""
18
58a7ff1 @bdarnell Turn on __future__ division too.
bdarnell authored
19 from __future__ import absolute_import, division, with_statement
da6d821 @bdarnell Standardize future imports for all files in the package.
bdarnell authored
20
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
21 import copy
22 import itertools
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
23 import logging
805ac65 @bdarnell Work around the "MySQL server has gone away" issue by closing and reopen...
bdarnell authored
24 import time
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
25
38908bf @bdarnell Make tornado.database importable when MySQLdb is not available.
bdarnell authored
26 try:
27 import MySQLdb.constants
28 import MySQLdb.converters
29 import MySQLdb.cursors
30 except ImportError:
31 # If MySQLdb isn't available this module won't actually be useable,
32 # but we want it to at least be importable (mainly for readthedocs.org,
33 # which has limitations on third-party modules)
34 MySQLdb = None
c152b78 @bdarnell While I'm touching every file, run autopep8 too.
bdarnell authored
35
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
36 class Connection(object):
37 """A lightweight wrapper around MySQLdb DB-API connections.
38
39 The main value we provide is wrapping rows in a dict/object so that
96c2f2f @bdarnell Finish automatic documentation
bdarnell authored
40 columns can be accessed by name. Typical usage::
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
41
42 db = database.Connection("localhost", "mydatabase")
43 for article in db.query("SELECT * FROM articles"):
44 print article.title
45
46 Cursors are hidden by the implementation, but other than that, the methods
47 are very similar to the DB-API.
48
49 We explicitly set the timezone to UTC and the character encoding to
50 UTF-8 on all connections to avoid time zone and encoding errors.
51 """
805ac65 @bdarnell Work around the "MySQL server has gone away" issue by closing and reopen...
bdarnell authored
52 def __init__(self, host, database, user=None, password=None,
c152b78 @bdarnell While I'm touching every file, run autopep8 too.
bdarnell authored
53 max_idle_time=7 * 3600):
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
54 self.host = host
55 self.database = database
805ac65 @bdarnell Work around the "MySQL server has gone away" issue by closing and reopen...
bdarnell authored
56 self.max_idle_time = max_idle_time
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
57
58 args = dict(conv=CONVERSIONS, use_unicode=True, charset="utf8",
59 db=database, init_command='SET time_zone = "+0:00"',
60 sql_mode="TRADITIONAL")
61 if user is not None:
62 args["user"] = user
63 if password is not None:
64 args["passwd"] = password
65
66 # We accept a path to a MySQL socket file or a host(:port) string
67 if "/" in host:
68 args["unix_socket"] = host
69 else:
70 self.socket = None
71 pair = host.split(":")
72 if len(pair) == 2:
73 args["host"] = pair[0]
74 args["port"] = int(pair[1])
75 else:
76 args["host"] = host
77 args["port"] = 3306
78
79 self._db = None
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
80 self._db_args = args
805ac65 @bdarnell Work around the "MySQL server has gone away" issue by closing and reopen...
bdarnell authored
81 self._last_use_time = time.time()
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
82 try:
83 self.reconnect()
17eed4f @bdarnell Replace all bare "except:" blocks with "except Exception:" so we don't
bdarnell authored
84 except Exception:
ca8002f Send all logging to the root logger instead of per-module loggers.
Ben Darnell authored
85 logging.error("Cannot connect to MySQL on %s", self.host,
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
86 exc_info=True)
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
87
88 def __del__(self):
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
89 self.close()
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
90
91 def close(self):
92 """Closes this database connection."""
7546f8d Ask for more Facebook Connect fields by default
Bret Taylor authored
93 if getattr(self, "_db", None) is not None:
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
94 self._db.close()
95 self._db = None
96
97 def reconnect(self):
98 """Closes the existing database connection and re-opens it."""
99 self.close()
100 self._db = MySQLdb.connect(**self._db_args)
101 self._db.autocommit(True)
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
102
103 def iter(self, query, *parameters):
104 """Returns an iterator for the given query and parameters."""
805ac65 @bdarnell Work around the "MySQL server has gone away" issue by closing and reopen...
bdarnell authored
105 self._ensure_connected()
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
106 cursor = MySQLdb.cursors.SSCursor(self._db)
107 try:
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
108 self._execute(cursor, query, parameters)
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
109 column_names = [d[0] for d in cursor.description]
110 for row in cursor:
111 yield Row(zip(column_names, row))
112 finally:
113 cursor.close()
114
115 def query(self, query, *parameters):
116 """Returns a row list for the given query and parameters."""
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
117 cursor = self._cursor()
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
118 try:
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
119 self._execute(cursor, query, parameters)
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
120 column_names = [d[0] for d in cursor.description]
121 return [Row(itertools.izip(column_names, row)) for row in cursor]
122 finally:
123 cursor.close()
124
125 def get(self, query, *parameters):
126 """Returns the first row returned for the given query."""
127 rows = self.query(query, *parameters)
128 if not rows:
129 return None
130 elif len(rows) > 1:
131 raise Exception("Multiple rows returned for Database.get() query")
132 else:
133 return rows[0]
134
7f6c66e @bdarnell Add a version of database.Connection.execute that returns the rowcount.
bdarnell authored
135 # rowcount is a more reasonable default return value than lastrowid,
136 # but for historical compatibility execute() must return lastrowid.
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
137 def execute(self, query, *parameters):
138 """Executes the given query, returning the lastrowid from the query."""
7f6c66e @bdarnell Add a version of database.Connection.execute that returns the rowcount.
bdarnell authored
139 return self.execute_lastrowid(query, *parameters)
140
141 def execute_lastrowid(self, query, *parameters):
142 """Executes the given query, returning the lastrowid from the query."""
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
143 cursor = self._cursor()
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
144 try:
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
145 self._execute(cursor, query, parameters)
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
146 return cursor.lastrowid
147 finally:
148 cursor.close()
149
7f6c66e @bdarnell Add a version of database.Connection.execute that returns the rowcount.
bdarnell authored
150 def execute_rowcount(self, query, *parameters):
151 """Executes the given query, returning the rowcount from the query."""
152 cursor = self._cursor()
153 try:
154 self._execute(cursor, query, parameters)
155 return cursor.rowcount
156 finally:
157 cursor.close()
158
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
159 def executemany(self, query, parameters):
160 """Executes the given query against all the given param sequences.
161
162 We return the lastrowid from the query.
163 """
7f6c66e @bdarnell Add a version of database.Connection.execute that returns the rowcount.
bdarnell authored
164 return self.executemany_lastrowid(query, parameters)
165
166 def executemany_lastrowid(self, query, parameters):
167 """Executes the given query against all the given param sequences.
168
169 We return the lastrowid from the query.
170 """
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
171 cursor = self._cursor()
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
172 try:
173 cursor.executemany(query, parameters)
174 return cursor.lastrowid
175 finally:
176 cursor.close()
177
7f6c66e @bdarnell Add a version of database.Connection.execute that returns the rowcount.
bdarnell authored
178 def executemany_rowcount(self, query, parameters):
179 """Executes the given query against all the given param sequences.
180
181 We return the rowcount from the query.
182 """
183 cursor = self._cursor()
184 try:
185 cursor.executemany(query, parameters)
186 return cursor.rowcount
187 finally:
188 cursor.close()
189
805ac65 @bdarnell Work around the "MySQL server has gone away" issue by closing and reopen...
bdarnell authored
190 def _ensure_connected(self):
191 # Mysql by default closes client connections that are idle for
192 # 8 hours, but the client library does not report this fact until
193 # you try to perform a query and it fails. Protect against this
194 # case by preemptively closing and reopening the connection
195 # if it has been idle for too long (7 hours by default).
196 if (self._db is None or
197 (time.time() - self._last_use_time > self.max_idle_time)):
198 self.reconnect()
199 self._last_use_time = time.time()
200
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
201 def _cursor(self):
805ac65 @bdarnell Work around the "MySQL server has gone away" issue by closing and reopen...
bdarnell authored
202 self._ensure_connected()
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
203 return self._db.cursor()
204
205 def _execute(self, cursor, query, parameters):
206 try:
207 return cursor.execute(query, parameters)
208 except OperationalError:
ca8002f Send all logging to the root logger instead of per-module loggers.
Ben Darnell authored
209 logging.error("Error connecting to MySQL on %s", self.host)
dd6cadf Auto-reconnect on MySQL connection failur
btaylor authored
210 self.close()
211 raise
212
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
213
214 class Row(dict):
215 """A dict that allows for object-like property access syntax."""
216 def __getattr__(self, name):
217 try:
218 return self[name]
219 except KeyError:
220 raise AttributeError(name)
221
38908bf @bdarnell Make tornado.database importable when MySQLdb is not available.
bdarnell authored
222 if MySQLdb is not None:
223 # Fix the access conversions to properly recognize unicode/binary
224 FIELD_TYPE = MySQLdb.constants.FIELD_TYPE
225 FLAG = MySQLdb.constants.FLAG
226 CONVERSIONS = copy.copy(MySQLdb.converters.conversions)
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
227
38908bf @bdarnell Make tornado.database importable when MySQLdb is not available.
bdarnell authored
228 field_types = [FIELD_TYPE.BLOB, FIELD_TYPE.STRING, FIELD_TYPE.VAR_STRING]
229 if 'VARCHAR' in vars(FIELD_TYPE):
230 field_types.append(FIELD_TYPE.VARCHAR)
e2181b0 @rbu Make iteration over sql fields clearer
rbu authored
231
38908bf @bdarnell Make tornado.database importable when MySQLdb is not available.
bdarnell authored
232 for field_type in field_types:
233 CONVERSIONS[field_type] = [(FLAG.BINARY, str)] + CONVERSIONS[field_type]
e2181b0 @rbu Make iteration over sql fields clearer
rbu authored
234
2afa973 @finiteloop Move Tornado project to Github
finiteloop authored
235
38908bf @bdarnell Make tornado.database importable when MySQLdb is not available.
bdarnell authored
236 # Alias some common MySQL exceptions
237 IntegrityError = MySQLdb.IntegrityError
238 OperationalError = MySQLdb.OperationalError
Something went wrong with that request. Please try again.