Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

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