class CVESQL:
def __init__(self):
self.host = mysql_host
self.port = mysql_port
self.username = mysql_username
self.password = mysql_password
self.dbname = mysql_db
self.chrset = mysql_chrset
self.conn = PooledDB(MySQLdb,
host=self.host,
user=self.username,
passwd=self.password,
db=self.dbname,
port=int(self.port),
charset=self.chrset).connection()
def getbysql(self, sql, args):
'''
Get by sql
:param sql:
:param param:
:return:
'''
try:
cursor = self.conn.cursor()
cursor.execute(sql, args)
res = cursor.fetchall()
cursor.close()
return res
except Exception as e:
print e
return "None"
finally:
cursor.close()
def insertbysql(self, sql, args):
'''
:param sql:
:param args:
:return:
'''
try:
cursor = self.conn.cursor()
#import pdb;pdb.set_trace()
cursor.execute(sql, args)
print "Inserting data >>>", args
self.conn.commit()
except Exception as e:
print "Error encountered >>> ", e
finally:
cursor.close()
def __del__(self):
self.conn.close()
class heimdalldb(object):
def __init__(self):
'''
Read db config
'''
config = ConfigParser.ConfigParser()
config.read('./utils/dbcfg.conf')
self.host = config.get("MYSQLDB", "host")
self.port = config.get("MYSQLDB", "port")
self.username = config.get("MYSQLDB", "username")
self.password = config.get("MYSQLDB", "password")
self.dbname = config.get("MYSQLDB", "dbname")
self.chrset = config.get("MYSQLDB", "chrset")
self.conn = PooledDB(MySQLdb,host=self.host,user=self.username,passwd=self.password,db=self.dbname,port=int(self.port),charset=self.chrset).connection()
def insertbysql(self, sql):
'''
insert by sql
'''
try:
cursor = self.conn.cursor()
cursor.execute(sql)
self.conn.commit()
except Exception as e:
return "None"
finally:
cursor.close()
def getbysql(self, sql):
'''
Get by sql
:param sql:
:param param:
:return:
'''
try:
cursor = self.conn.cursor()
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
return res
except Exception as e:
return "None"
finally:
cursor.close()
def __del__(self):
self.conn.close()
class MyPymysqlPool(object):
def __init__(self,
dbhost=conf.get('mysql', 'DB_HOST'),
dbport=conf.getint('mysql', 'DB_PORT'),
dbname=conf.get('mysql', 'DB_NAME'),
dbuser=conf.get('mysql', 'DB_USER'),
dbpasswd=conf.get('mysql', 'DB_PASSWORD'),
charset='utf8',
maxconnections=20):
"""
数据库构造函数，从连接池中取出连接，并生成操作游标
:param dbhost: 数据库地址
:param dbport: 数据库端口
:param dbname: 数据库名称
:param dbuser: 数据库账号
:param dbpasswd: 数据库密码
:param charset: 数据库字符集
:param maxconnections:连接池通常允许的最大连接数，0或None表示任意数量的连接
"""
# mincached 最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接,0表示启动时没有连接
# maxcached 最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接，0或None表示无限制的池大小
# use_unicode=False, cursorclass=DictCursor（返回字典格式）
# ping:确定何时使用ping()检查连接(0=None=never,1=default=每当从池中获取时,2=创建游标时,4 =执行查询时,7=always)
self.conn = PooledDB(creator=pymysql,
mincached=0,
maxcached=5,
maxconnections=maxconnections,
host=dbhost,
port=dbport,
user=dbuser,
passwd=dbpasswd,
db=dbname,
charset=charset,
ping=4).connection()
self.cursor = self.conn.cursor()
def close_conn(self):
"""
关闭游标 ，关闭连接
"""
# 关闭游标
self.cursor.close()
# 关闭连接
self.conn.close()
def commit_query(self, query):
"""
提交查询语句
:param query: 数据库查询语句
:return: 返回执行行数
"""
# self.conn.ping(reconnect=True)
try:
# 执行sql语句
rows_num = self.cursor.execute(query)
# 提交到数据库执行
self.conn.commit()
num = int(rows_num) if rows_num not in (None, 'NoneType', 'null',
'Null', '', ' ') else 0
LOG.debug("The result rows count is : {}".format(num))
return num
except BaseException as e:
LOG.error("The mysql {0} exec sql error: {1}".format(query, e))
# 发生错误时回滚
self.conn.rollback()
finally:
self.close_conn()
def executed_one(self, query):
"""
查询一条数据
:param query: 数据库查询语句
:return: 返回字符串，默认取第一列第一行
"""
LOG.debug("The mysql exec :" + query)
num = self.commit_query(query)
result = ''
if num > 0:
result = self.cursor.fetchone()[0]
LOG.debug("The result is : {}".format(result))
return result
def executed_all(self, query):
"""
查询所有数据
:param query: 数据库查询语句
:return: 二维数组
"""
LOG.debug("The mysql exec :" + query)
num = self.commit_query(query)
result = list()
if num > 0:
result = [list(fc) for fc in self.cursor.fetchall()]
LOG.debug("The result is : {}".format(result))
return result
def executed_rows(self, query):
"""
获取执行sql的总数
:param query: 数据库查询语句
:return: 返回执行行数
"""
LOG.debug("The mysql exec :" + query)
num = self.commit_query(query)
LOG.debug("The result is :" + str(num))
return num
def executed_many(self, query, value_tuple):
"""
批量执行 SQL语句，用于批量插入或批量更新
:param query: 插入语句/更新语句
:param value_tuple: 二维元组
:return:
"""
try:
# 执行sql语句
rows_num = self.cursor.executemany(query, value_tuple)
# 提交到数据库执行
self.conn.commit()
num = int(rows_num) if rows_num else 0
LOG.debug("The result rows count is : {}".format(num))
return num
except BaseException as e:
LOG.error("The mysql {0} exec sql error: {1}".format(query, e))
# 发生错误时回滚
self.conn.rollback()
def select(self, table, column='*', *condition):
"""
查询所有数据
:param table: 需要查询的表
:param column: 需要查询的列，为空时查询所有， col1,col2
:param condition: 查询条件
:return: 返回数组,元素是字典
"""
query_list = ["SELECT", column, "FROM", table, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_all(query)
def select_join(self, table, column, equivalent, *condition):
"""
查询有关联的表len(table.split(,)) = len(equivalent) + 1
:param table: 需要join的表，txx1,txx2,txx3
:param column: 需要查询的列名， txx1.col1,txx2.col2,...
:param equivalent: on等式 txx1.col1 = tx2.col2,txx3.col1=txx4.clo3
:param condition: 查询条件 txx.xxcol1="xx" and txx.xxcol2 = "xx"
:return: 二维数组
"""
tables, equivalents = table.split(','), equivalent.split(',')
if len(tables) == (len(equivalents) + 1):
join_sql = list()
for i in range(len(equivalents)):
if i == 0:
join_sql.extend([
tables[i], "JOIN ", tables[i + 1], "ON", equivalents[i]
])
else:
join_sql.extend(
["JOIN ", tables[i + 1], "ON ", equivalents[i]])
join_sql.append(';')
query_list = ["SELECT", column, "FROM"]
query_list.extend(join_sql)
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
else:
raise IndexError('table and equivalent the index not equality!')
return self.executed_all(query)
def select_col(self, table, column, *condition):
"""
单独查询某列
:param table: 需要查询的表
:param column: 需要查询的列，只能查询一列
:param condition: 查询条件
:return: 返回数组，展示一列的查询结果
"""
query_list = ["SELECT", column, "FROM", table, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return [result[0] for result in self.executed_all(query)]
def select_one(self, table, column, *condition):
"""
单独查询某个值
:param table: 需要查询的表
:param column: 需要查询的列，只能查询一列
:param condition: 查询条件
:return: 返回对应字段值，多条数据时只取第一条
"""
query_list = ["SELECT", column, "FROM", table, "LIMIT 1;"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_one(query)
def select_rows(self, table, column='*', *condition):
"""
查询执行行数
:param table: 需要查询的表
:param column: 需要查询的列，为空时查询所有， col1,col2
:param condition: 查询条件
:return: 返回查询条数
"""
query_list = ["SELECT", column, "FROM", table, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_rows(query)
def insert(self, table, value_dict):
"""
插入语句
:param table: 需要插入的表
:param value_dict: 插入信息 {"列名1"：值1，"列名2"：值2}
:return: 返回执行条数
"""
values = ','.join([QUOTATION(value) for value in value_dict.values()])
clos = ','.join(value_dict.keys())
query = ' '.join([
"INSERT", "INTO ", table, "(", clos, ")", "VALUE", "(", values,
")", ";"
])
return self.executed_rows(query)
def insert_many(self, table, col_list, values_tuple):
"""
插入多条语句 'insert into test (sdf,asd,aaa) VALUES ("%s","%s","%s")', ((1,2,3),(2,3,4),(3,4,5))
:param table: 表名
:param col_list: 列名，数组或元组 (sdf,asd,aaa)
:param values_tuple: 插入值，二维元组，二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
:return: 执行行数
"""
col_num = len(col_list)
query = ' '.join([
"INSERT", "INTO ", table, "(", ','.join(col_list), ")", "VALUE",
"(", (',%s' * col_num).lstrip(','), ")", ";"
])
LOG.debug("The mysql exec :" + query)
values_list = list()
for value in values_tuple:
if col_num != len(value):
raise IndexError(
'the index of col_list and the index of values_tuple is not equality!'
)
values_list.append(tuple(value))
return self.executed_many(query, values_list)
def update_many(self, table, col_list, values_tuple, col_condition_list):
"""
更新多条语句，元组顺序一致，查询条件在 元组最后
update user_tb set name=%s, sex=%s where id=%s and s_id=%s,
(('小孙', '男'， 2， 1),
('小白', '女'， 3， 2),
('小猪', '男'， 4， 1),
('小牛', '男'， 5， 3),
('小唐', '女'， 6， 2))
:param table: 表名
:param col_list: 列名，数组或元组 (sdf,asd,aaa)
:param values_tuple: 插入值，二维元组，二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
:param col_condition_list: 查询条件列名
:return: 执行行数
"""
query_list = list()
query_list.append("UPDATE")
query_list.append(table)
query_list.append("SET")
query_list.append(','.join(
['{}=%s'.format(col_1) for col_1 in col_list]))
if col_condition_list:
query_list.append('WHERE')
query_list.append(' AND '.join(
['{}=%s'.format(col_2) for col_2 in col_condition_list]))
query_list.append(';')
values_list = list()
col = col_list
col.extend(col_condition_list)
col_num = len(col)
for value in values_tuple:
if col_num != len(value):
raise IndexError(
'the index of col_list and the index of values_tuple is not equality!'
)
values_list.append(tuple(value))
query = ' '.join(query_list)
LOG.debug("The mysql exec :" + query)
return self.executed_many(query, values_list)
def insert_update_many(self, table, col_list, values_tuple):
"""
插入或更新多条语句
'insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);
这个语法和适合用在需要判断记录是否存在，不存在则插入存在则更新的记录
:param table: 表名
:param col_list: 列名，数组或元组 (sdf,asd,aaa)
:param values_tuple: 插入值，二维元组，二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
:return: 执行行数
"""
col_condition = ','.join(
['{0}=VALUES({0})'.format(t) for t in col_list])
col_num = len(col_list)
for value in values_tuple:
if col_num != len(value):
raise IndexError(
'the index of col_list and the index of values_tuple is not equality!'
)
query = [
"INSERT", "INTO ", table, "(", ','.join(col_list), ")", "VALUE",
','.join(values_tuple), 'ON', 'DUPLICATE', 'KEY', 'UPDATE',
col_condition, ";"
]
self.executed_rows(' '.join(query))
def update_case(self, table, col_list, values_tuple, col_condition,
condition_tuple, *condition):
"""
更新数据库语句
:param table: 需要更新的表
:param col_list: 更新列名列表
:param values_tuple: 更新值二维元组
:param col_condition: CASE列名
:param condition_tuple: CASE值，元组
:param condition: 查询条件
:return: 返回执行条数
"""
query_list = ["UPDATE", table, "SET"]
for values in values_tuple:
if len(col_list) != len(values):
raise IndexError(
'the index of col_list and the index of values_tuple is not equality!'
)
update_value = list()
for i in range(len(col_list)):
case_value = list()
case_value.append('{0} = CASE {1}'.format(col_list[i],
col_condition))
for value in values_tuple:
case_value.append('WHEN {0} THEN {1}'.format(
col_list[i], value[i]))
case_value.append('END')
update_value.append(' '.join(case_value))
query_list.append(','.join(update_value))
query_list.append('WHERE')
query_list.append(col_condition)
query_list.append('IN {}'.format(condition_tuple))
if len(condition) > 0:
query_list.append('AND')
query_list.append(' AND '.join(condition))
query_list.append(';')
query = ' '.join(query_list)
return self.executed_rows(query)
def update(self, table, value, *condition):
"""
更新数据库语句
:param table: 需要更新的表
:param value: 更新值 列名1=值1，列名2=值2
:param condition: 查询条件
:return: 返回执行条数
"""
query_list = ["UPDATE", table, "SET", value, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_rows(query)
def update_dict(self, table, value_dict, *condition):
"""
更新数据库语句
:param table: 需要更新的表
:param value_dict: 更新值 {"列名1"：值1，"列名2"：值2}
:param condition: 查询条件
:return: 返回执行条数
"""
# 判断value是否需要添加引号
value_list = [
col + '=' + QUOTATION(value) for col, value in value_dict.items()
]
value = ','.join(value_list)
query_list = ["UPDATE", table, "SET", value, ";"]
if len(condition) > 0:
query_list.insert(-1, 'WHERE')
query_list.insert(-1, ' AND '.join(condition))
query = ' '.join(query_list)
return self.executed_rows(query)
def delete(self, table, condition):
"""
删除语句
:param table: 需要删除的表
:param condition: 查询条件
:return: 返回执行条数
"""
query_list = ["DELETE", "FROM", table, "WHERE", condition, ";"]
query = ' '.join(query_list)
return self.executed_rows(query)
def drop_database(self, database_name):
"""
删除数据库
:param database_name: 要删除的数据库名称
:return: 返回执行条数
"""
query_list = ['drop', 'database', database_name, ';']
query = ' '.join(query_list)
return self.executed_rows(query)
class Connection(object):
"""A lightweight wrapper around MySQLdb DB-API connections.
The main value we provide is wrapping rows in a dict/object so that
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 are hidden by the implementation, but other than that, the methods
are 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 encoding errors.
"""
def __init__(self, host, database, user=None, password=None,
max_idle_time=7*3600):
self.host = host
self.database = database
self.max_idle_time = max_idle_time
args = dict(conv=CONVERSIONS, use_unicode=True, charset="utf8",
db=database, init_command='SET time_zone = "+0:00"',
sql_mode="TRADITIONAL")
if user is not None:
args["user"] = user
if password is not None:
args["passwd"] = password
# We accept a path to a MySQL socket file or a host(:port) string
if "/" in host:
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["host"] = host
args["port"] = 3306
self._db = None
self._db_args = args
self._last_use_time = time.time()
try:
self.reconnect()
except:
logging.error("Cannot connect to MySQL on %s", self.host,
exc_info=True)
def __del__(self):
self.close()
def close(self):
"""Closes this database connection."""
if getattr(self, "_db", None) is not None:
self._db.close()
self._db = None
def reconnect(self):
"""Closes the existing database connection and re-opens it."""
self.close()
#self._db = MySQLdb.connect(**self._db_args)
self._db = PooledDB(creator=MySQLdb, **self._db_args).connection(0)
#self._db.autocommit(True)
def iter(self, query, *parameters):
"""Returns an iterator for the given query and parameters."""
self._ensure_connected()
cursor = MySQLdb.cursors.SSCursor(self._db)
try:
self._execute(cursor, query, 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] for 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]
def execute(self, query, *parameters):
"""Executes 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 executemany(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.lastrowid
finally:
cursor.close()
def _ensure_connected(self):
# Mysql by default closes client connections that are idle for
# 8 hours, but the client library does not 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 has been idle for too 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.cursor()
def _execute(self, cursor, query, parameters):
try:
cursor.execute(query, parameters)
self._db.commit()
return
except OperationalError:
logging.error("Error connecting to MySQL on %s", self.host)
self.close()
raise
class Db(object):
def __init__(self, db_host, db_port, db_name, db_user, db_passwd, charset):
self.conn = PooledDB(
creator=pymysql,
maxconnections=10, # 连接池允许的最大连接数， 0和none表示没有限制
mincached=2, # 初始化时，连接池至少创建的空闲连接，0表示不创建
maxcached=5, # 连接池空闲的最多连接数，0和none表示不限制
blocking=True, # 连接池中如果没有可用共享连接后是否阻塞等待，True表示等待，反之则为报错弹出
host= db_host,
port=int(db_port),
user= db_user,
passwd= db_passwd,
database= db_name,
charset= charset
).connection()
self.cursor = self.conn.cursor()
# 新增数据
def db_insert(self, tableName, dataDict):
str_field = ""
str_value = ""
for filed,value in dataDict.items():
str_field += "`" + filed + "`,"
if (type(value) == type("kkk")):
str_value += "'" + str(value) + "'" + ","
elif(type(value) == type(123)):
str_value += str(value) + ","
sql = "INSERT INTO `"+ tableName +"`(" + str_field[:-1] + ")VALUE(" + str_value[:-1] + ")"
self.cursor.execute(sql)
self.conn.commit()
get_rows = self.cursor.rowcount
if get_rows == 1 :
return True
else:
return False
# print(str_value)
# 更新数据
def db_updata(self):
pass;
# 提取数据 return 元组
def db_getdata(self, tableName, field):
sql = "SELECT " + field + " FROM " + tableName;
print(sql)
self.cursor.execute(sql)
data_tuple= self.cursor.fetchall()
return data_tuple
# 删除数据
def db_deldata(self):
pass;
# 查询数据
def db_selectdata(self):
pass;
# 回收数据库资源
def __del__(self):
self.cursor.close()
self.conn.close()
class OperationDB:
_instance_lock = threading.Lock()
def __init__(self, db_type=settings.DB_TYPE, db_name=None, env="dev"):
self.db_type = db_type
# if db_type == 'oracle':
# tns = cx_Oracle.makedsn(host,port,ins_name)
# self.db = cx_Oracle.connect(username,passwd,tns)
# cx_Oracle.connect()
# else:
# # 创建数据库连接
# self.db = pymysql.connect(
# host = host,
# port = port,
# user = username,
# passwd = passwd,
# db = ins_name,
# charset = 'utf8',
# # 加上cursorclass之后就可以直接把字段名捞出来，和字段值组成键值对的形式
# cursorclass = pymysql.cursors.DictCursor
# )
db_config = set_db_config(db_type, db_name, env)
if self.db_type == "mysql":
self.db = PooledDB(pymysql, 5, **db_config).connection()
elif self.db_type == "sqlite":
self.db = PooledDB(sqlite3, 5, **db_config).connection()
elif self.db_type == "redis":
pool = redis.ConnectionPool(**db_config)
self.db = redis.Redis(connection_pool=pool)
else:
self.db = PooledDB(cx_Oracle, 5, **db_config).connection()
# 创建游标
if self.db_type != "redis":
self.cur = self.db.cursor()
def __new__(cls, *args, **kwargs):
'''
实现单例模式
:param args:
:param kwargs:
:return:
'''
if not hasattr(cls, "_instance"):
with cls._instance_lock:
if not hasattr(cls, "_instance"):
cls._instance = super().__new__(cls)
return cls._instance
#获取一条数据
def search_one(self, sql, param=None):
if self.db_type == "sqlite":
self.cur.execute(sql)
res = self.cur.fetchone()
elif self.db_type == "redis":
#注意此时的sql并不是sql语句了，而是一个key值
res = self.db.get(sql).decode("utf-8")
else:
self.cur.execute(sql, param)
res = self.cur.fetchone()
if res and (self.db_type == 'oracle' or self.db_type == "sqlite"):
res = self.makeDictFactory(*res)
return res
#获取所有数据
def search_all(self, sql, param=None):
self.cur.execute(sql, param)
res = self.cur.fetchall()
if self.db_type == 'oracle':
res = self.makeDictFactory(*res)
return res
#新增/删除/更新数据
def sql_DML(self, sql, param=None):
try:
self.cur.execute(sql, param)
self.db.commit()
except:
self.db.rollback()
#将返回的结果和字段名映射成字典
def makeDictFactory(self, *args):
columnNames = [d[0] for d in self.cur.description]
if isinstance(args[0], list):
return [dict(z) for z in [zip(columnNames, data) for data in args]]
return dict(zip(columnNames, args))
#关闭游标和数据库连接
def close(self):
self.cur.close()
self.db.close()
class Connection(object):
def __init__(self, database, host, user, mincached=5, maxcached=10, maxshared=0):
self.database = database
self.host = host
self.user = user
self.mincached = mincached
self.maxcached = maxcached
self.maxshared = maxshared
args = dict(
database=database,
user=user,
mincached=mincached,
maxcached=maxcached,
maxshared=maxshared,
)
self.max_idle_time = 7 * 3600
self._db = None
self._db_args = args
self._last_use_time = time.time()
try:
self.reconnect()
except:
logging.error("Cannot connect to postgres on %s", self.host,
exc_info=True)
def __del__(self):
self.close()
def close(self):
if getattr(self, "_db", None) is not None:
self._db.close()
self._db = None
def reconnect(self):
"""Closes the existing database connection and re-opens it."""
self.close()
self._db = PooledDB(psycopg2, **self._db_args).connection()
def _ensure_connected(self):
# PostgreSQL by default closes client connections that are idle for
# 8 hours, but the client library does not 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 has been idle for too 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.cursor()
def _execute(self, cursor, query, parameters):
try:
cursor.execute(query, parameters)
self._db.commit()
return
except OperationalError:
logging.error("Error connecting to postgres on %s", self.host)
self.close()
raise
def execute_lastrowid(self, query, parameters):
"""Executes 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.lastrowid
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.rowcount
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] for 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]
def execute(self, query, parameters=[]):
"""Executes the given query, returning the lastrowid from the query."""
return self.execute_lastrowid(query, parameters)
class Connection(object):
"""A lightweight wrapper around MySQLdb DB-API connections.
The main value we provide is wrapping rows in a dict/object so that
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 are hidden by the implementation, but other than that, the methods
are 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 encoding errors.
"""
def __init__(self,
host,
database,
user=None,
password=None,
max_idle_time=7 * 3600):
self.host = host
self.database = database
self.max_idle_time = max_idle_time
args = dict(conv=CONVERSIONS,
use_unicode=True,
charset="utf8",
db=database,
init_command='SET time_zone = "+0:00"',
sql_mode="TRADITIONAL")
if user is not None:
args["user"] = user
if password is not None:
args["passwd"] = password
# We accept a path to a MySQL socket file or a host(:port) string
if "/" in host:
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["host"] = host
args["port"] = 3306
self._db = None
self._db_args = args
self._last_use_time = time.time()
try:
self.reconnect()
except:
logging.error("Cannot connect to MySQL on %s",
self.host,
exc_info=True)
def __del__(self):
self.close()
def close(self):
"""Closes this database connection."""
if getattr(self, "_db", None) is not None:
self._db.close()
self._db = None
def reconnect(self):
"""Closes the existing database connection and re-opens it."""
self.close()
#self._db = MySQLdb.connect(**self._db_args)
self._db = PooledDB(creator=MySQLdb, **self._db_args).connection(0)
#self._db.autocommit(True)
def iter(self, query, *parameters):
"""Returns an iterator for the given query and parameters."""
self._ensure_connected()
cursor = MySQLdb.cursors.SSCursor(self._db)
try:
self._execute(cursor, query, 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] for 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]
def execute(self, query, *parameters):
"""Executes 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 executemany(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.lastrowid
finally:
cursor.close()
def _ensure_connected(self):
# Mysql by default closes client connections that are idle for
# 8 hours, but the client library does not 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 has been idle for too 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.cursor()
def _execute(self, cursor, query, parameters):
try:
cursor.execute(query, parameters)
self._db.commit()
return
except OperationalError:
logging.error("Error connecting to MySQL on %s", self.host)
self.close()
raise
class Connection(object):
def __init__(self,
database,
host,
user,
mincached=5,
maxcached=10,
maxshared=0):
self.database = database
self.host = host
self.user = user
self.mincached = mincached
self.maxcached = maxcached
self.maxshared = maxshared
args = dict(
database=database,
user=user,
mincached=mincached,
maxcached=maxcached,
maxshared=maxshared,
)
self.max_idle_time = 7 * 3600
self._db = None
self._db_args = args
self._last_use_time = time.time()
try:
self.reconnect()
except:
logging.error("Cannot connect to postgres on %s",
self.host,
exc_info=True)
def __del__(self):
self.close()
def close(self):
if getattr(self, "_db", None) is not None:
self._db.close()
self._db = None
def reconnect(self):
"""Closes the existing database connection and re-opens it."""
self.close()
self._db = PooledDB(psycopg2, **self._db_args).connection()
def _ensure_connected(self):
# PostgreSQL by default closes client connections that are idle for
# 8 hours, but the client library does not 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 has been idle for too 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.cursor()
def _execute(self, cursor, query, parameters):
try:
cursor.execute(query, parameters)
self._db.commit()
return
except OperationalError:
logging.error("Error connecting to postgres on %s", self.host)
self.close()
raise
def execute_lastrowid(self, query, parameters):
"""Executes 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.lastrowid
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.rowcount
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] for 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]
def execute(self, query, parameters=[]):
"""Executes the given query, returning the lastrowid from the query."""
return self.execute_lastrowid(query, parameters)
class ConnectionPoolV1(object):
def __init__(self, host: str, port: int, user: str, passwd: str, db: str,
charset: str, _lock):
"""
数据库连接池 V1版本
:param host: 要连接的主机IP地址，比如 "127.0.0.1"
:param port: 要连接的主机端口，比如 3306
:param user: 数据库用户名，比如 "root"
:param passwd: 数据库用户密码，比如 "123456"
:param db: 要使用的数据库名，比如 "my_database"
:param charset: 默认字符集，比如 "utf8mb4"
"""
DeprecationWarning("现在V1版本的数据库连接池由于没有加锁会出错，被迫加了锁，性能降低")
self.conn = PooledDB(creator=pymysql,
mincached=1,
maxcached=20,
host=host,
port=port,
user=user,
passwd=passwd,
db=db,
charset=charset).connection()
self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
self._lock = _lock
def insert(self, sql, arg_tuple) -> int:
"""
执行插入SQL语句
:param sql: SQL模板语句
:param arg_tuple: SQL模板语句参数
:return: 被影响的行数
"""
# with self._lock:
# _ = self.cur.execute(sql, arg_tuple)
# self.conn.commit()
self._lock.acquire()
_ = self.cur.execute(sql, arg_tuple)
self.conn.commit()
self._lock.release()
return _
def delete(self, sql, arg_tuple) -> int:
"""
执行删除SQL语句
:param sql: SQL模板语句
:param arg_tuple: SQL模板语句参数
:return: 被影响的行数
"""
# with self._lock:
# _ = self.cur.execute(sql, arg_tuple)
# self.conn.commit()
self._lock.acquire()
_ = self.cur.execute(sql, arg_tuple)
self.conn.commit()
self._lock.release()
return _
def update(self, sql, arg_tuple) -> int:
"""
执行更改SQL语句
:param sql: SQL模板语句
:param arg_tuple: SQL模板语句参数
:return: 被影响的行数
"""
# with self._lock:
# _ = self.cur.execute(sql, arg_tuple)
# self.conn.commit()
self._lock.acquire()
_ = self.cur.execute(sql, arg_tuple)
self.conn.commit()
self._lock.release()
return _
def fetch_all(self, sql: str, arg_tuple: tuple):
"""
执行查询语句并返回所有查询结果（如果有的话），若无结果返回空tuple
:param sql: SQL模板语句，比如 SELECT `id` FROM `tbl_user` WHERE `tbl_user`.`user_name`=%s;
:param arg_tuple: 构建真正查询语句时，传递给SQL模板语句的tuple，比如('alice',)
:return: 查询结果list，list中的每一个元素（dict类型）对应数据库里的一行
"""
# with self._lock:
# self.cur.execute(sql, arg_tuple)
self._lock.acquire()
self.cur.execute(sql, arg_tuple)
self.conn.commit()
self._lock.release()
return self.cur.fetchall()
def fetch_one(self, sql: str, arg_tuple: tuple):
"""
执行查询语句并返回第一条查询结果（如果有的话）,若无结果返回 * None *
:param sql: SQL模板语句，比如 SELECT `id` FROM `tbl_user` WHERE `tbl_user`.`user_name`=%s;
:param arg_tuple: 构建真正查询语句时，传递给SQL模板语句的tuple，比如('alice',)
:return: 查询结果dict
"""
# with self._lock:
# self.cur.execute(sql, arg_tuple)
self._lock.acquire()
self.cur.execute(sql, arg_tuple)
self.conn.commit()
self._lock.release()
return self.cur.fetchone()
def close(self):
"""关闭数据库连接池，估计暂时用不到"""
self.conn.close()
self.cur.close()