Viii. operation of the Python database MySQL

PYTHON Operations Database has some basic operations, more cumbersome, this article, with a package of database operations

#!/usr/bin/env python## Copyright facebook## Licensed under the Apache License, Version 2.0 (the "License"); You may# don't use this file except in compliance with the License. obtain# a copy of the License at## http://www.apache.org/licenses/LICENSE-2.0## unless required by applicable Law or agreed into writing, software# distributed under the License is distributed on a "as is" BASIS, without# Warranti ES or CONDITIONS of any KIND, either express OR implied. See the# License for the specific language governing permissions and limitations# under the License. "" A lightweight wrapper around MySQLdb. "" " Import copyimport mysqldb.constantsimport mysqldb.convertersimport mysqldb.cursorsimport itertoolsimport    Loggingimport timeclass Connection (object): "" "A lightweight wrapper around MySQLdb DB-API connections. The main value we provide is wrapping rows in a dict/object so, columns can be accessed by name. Typical usage:: db = database. Connection ("LocalhosT "," mydatabase ") for article in Db.query (" SELECT * from articles "): Print Article.title Cursors is    Hidden by the implementation, but other than that, the methods is very similar to the DB-API. We explicitly set the timezone to UTC and the character encoding to UTF-8 on all connections to avoid time zone and enc    Oding errors. "" "Def __init__ (self, host, database, User=none, Password=none, max_idle_time=7*3600): Self.hos t = host Self.database = database Self.max_idle_time = Max_idle_time args = Dict (conv=conversions, US                    E_unicode=true, charset= "UTF8", db=database, init_command= ' SET time_zone = "."            Sql_mode= "traditional") if user is not none:args["user"] = user if password are not None: args["passwd"] = password # We accept a path to a MySQL socket file or a host (:p ort) string if "/" in H ost:args["unix_sOcket "] = host Else:self.socket = None pair = Host.split (": ") If Len (pair) = = 2: args["host"] = Pair[0] args["port"] = Int (pair[1]) else:args["hos T "] = host args[" port "] = 3306 self._db = None Self._db_args = args Self._last_use_tim E = Time.time () try:self.reconnect () except Exception:logging.error ("Cannot connect To MySQL in%s ", Self.host, Exc_info=true) def __del__ (self): Self.close () def close        (self): "" "Closes this database connection." ""        If GetAttr (self, "_db", none) are not none:self._db.close () self._db = None def reconnect (self):        "" "Closes the existing database connection and re-opens it." "" Self.close () try:from dbutils import pooleddb Pool_con = Pooleddb.pooleddb (Creator=mysqldb, MI Ncached=1, maxcached=10, maxshared=10, maxconnections=20, Blocking=false, maxusage=100, **self._db_args) self._db = P Ool_con.connection () Self._db.cursor (). Connection.autocommit (True) except:self._db = MYSQLDB.C Onnect (**self._db_args) Self._db.autocommit (True) def iter (self, Query, *parameters): "" "Returns an IT        Erator for the given query and parameters. "" self._ensure_connected () cursor = MySQLdb.cursors.SSCursor (self._db) try:self._execute (cursor, q                uery, parameters) Column_names = [d[0] for D in Cursor.description] for row in cursor:        Yield Row (Zip (column_names, Row)) finally:cursor.close () def query (self, query, *parameters):        "" "Returns a row list for the given query and parameters." " cursor = Self._cursor () try:self._execute (cursor, query, parameters) Column_names = [D[0] fo R d in Cursor.descriptION] return [Row (Itertools.izip (column_names, Row)) for row in cursor] Finally:cursor.close (        def get (self, Query, *parameters): "" Returns the first row returned for the given query. "" "            rows = self.query (query, *parameters) if not Rows:return None elif len (rows) > 1:  Raise Exception ("Multiple rows returned for Database.get () query") Else:return Rows[0] # ROWCOUNT A more reasonable default return value than Lastrowid, # but for historical compatibility execute () must return Lastro    Wid.        def execute (Self, query, *parameters): "" "executes the given query, returning the LASTROWID from the query." " return Self.execute_lastrowid (Query, *parameters) def execute_lastrowid (self, Query, *parameters): "" "Execut        Es the given query, returning the LASTROWID from the query. "" cursor = Self._cursor () try:self._execute (cursor, query, parAmeters) return Cursor.lastrowid finally:cursor.close () def execute_rowcount (self, query,        *parameters): "" "executes the given query, returning the rowcount from the query." "        cursor = Self._cursor () try:self._execute (cursor, query, parameters) return Cursor.rowcount Finally:cursor.close () def executemany (self, Query, parameters): "" "Executes the given query        Against all the given param sequences.        We return the Lastrowid from the query.        "" "Return Self.executemany_lastrowid (query, parameters) def executemany_lastrowid (self, Query, parameters):        "" "executes the given query against all the given param sequences.        We return the Lastrowid from the query. "" "cursor = Self._cursor () try:cursor.executemany (query, parameters) return CURSOR.L Astrowid finally:cursor.close () def Executemany_roWcount (self, Query, parameters): "" "executes the given query against all the given param sequences.        We return the rowcount from the query. "" "cursor = Self._cursor () try:cursor.executemany (query, parameters) return CURSOR.R Owcount finally:cursor.close () def _ensure_connected (self): # Mysql By default closes client  Connections that is idle for # 8 hours, but the client library does is not a report this fact until # you try to  Perform a query and it fails. Protect against this # case by preemptively closing and reopening the connection # if it had been idle for t        Oo Long (7 hours by default). if (self._db is None or (Time.time ()-self._last_use_time > Self.max_idle_time)): Self.reconnect () Self._last_use_time = Time.time () def _cursor (self): self._ensure_connected () return SELF._DB.C Ursor () def _execute (self, cursor, qUery, parameters): Try:return cursor.execute (query, parameters) except Operationalerror:            Logging.error ("Error connecting to MySQL on%s", Self.host) Self.close () Raise finally:    Cursor.close () class Row (dict): "" "A dict that allows for Object-like property access syntax." " def __getattr__ (self, name): Try:return self[name] except keyerror:raise attributeer  ROR (name) # Fix the access conversions to properly recognize Unicode/binaryfield_type = MySQLdb.constants.FIELD_TYPEFLAG = MySQLdb.constants.FLAGCONVERSIONS = Copy.copy (MySQLdb.converters.conversions) field_types = [Field_type. BLOB, Field_type. STRING, Field_type. Var_string]if ' VARCHAR ' in VARs (field_type): Field_types.append (Field_type. VARCHAR) for field_type in field_types:conversions[field_type] = [(FLAG. BINARY, str)] + conversions[field_type]# Alias some common MySQL exceptionsintegrityerror = Mysqldb.integritYerroroperationalerror = Mysqldb.operationalerror 

Use the following methods:

def db ():    return database. Connection (Host=mysql_host, database=mysql_db, User=mysql_user, password=mysql_password) db = db ()

So very convenient to call the query

When inserting data, use

Db.execute ("insert INTO ' userplan ' (' hash ') values (%s)", hash)

Returns the number of rows affected,

To use when querying:

Db.query ("select ' id ' from ' userplan ' where hash =%s",pichash)

Original link

Viii. operation of the Python database MySQL