python中pymysql_Python3 中 pymysql 的易用封装

安装 pymysql

pip install pymysql

执行 excute

def execute(self, sql_cmd):

try:

self._getCursor().execute(sql_cmd)

self._conn.commit()

data = self._getCursor().fetchall()

return data

except Exception:

self._conn.rollback()

return None

插入 Insert

def insert(self, table, ins_data):

sql = ''

for data in ins_data:

key = ','.join(data.keys())

values = map(self._deal_values, data.values())

ins_data = ', '.join(values)

sql = "INSERT INTO {table}({key}) VALUES ({val})".format(

table=table,

key=key,

val=ins_data

)

return self.execute(sql)

删除 Delete

def delete(self, table, condition={}):

condition_list = self._deal_values(condition)

condition_data = ' and '.join(condition_list)

sql = "DELETE FROM {table} {where} {condition}".format(

table=table,

where='' if condition_data == '' else 'WHERE',

condition=condition_data

)

return self.execute(sql)

查询 Select

条件查询

def select(self, table, filed, value):

sql = "SELECT * FROM {table} WHERE {filed} = '{value}'".format(table=table, filed=filed, value=value)

return self.execute(sql)

查询全部

def select_all(self, table):

sql = "SELECT * FROM {table}".format(table=table)

return self.execute(sql)

修改 Update

def update(self, table, data, condition=None):

update_list = self._deal_values(data)

update_data = ",".join(update_list)

if condition is not None:

condition_list = self._deal_values(condition)

condition_data = ' AND '.join(condition_list)

sql = "UPDATE {table} SET {values} WHERE {condition}".format(table=table, values=update_data, condition=condition_data)

else:

sql = "UPDATE {table} SET {values}".format(table=table, values=update_data)

return self.execute(sql)

SQLUtils 类

import pymysql

class SQLUtils:

_conn = None

_cursor = None

def __init__(self, db_host=_config['SQL']['host'], db_user=_config['SQL']['user'], db_password=_config['SQL']['password'], db_name=_config['SQL']['database']):

try:

self._conn = pymysql.connect(db_host, db_user, db_password, db_name)

self._cursor = self._conn.cursor()

if self._conn is None or self._cursor is None:

raise Exception('Error while connect to database.')

except Exception as e:

raise e

def __del__(self):

self._cursor.close()

self._cursor = None

self._conn.close()

self._conn = None

def _getConnection(self):

if self._conn is not None:

return self._conn

def _getCursor(self):

if self._cursor is not None:

return self._cursor

def _deal_values(self, value):

if isinstance(value, str):

value = ("'{value}'".format(value=value))

elif isinstance(value, dict):

result = []

for key, value in value.items():

value = self._deal_values(value)

res = "{key}={value}".format(key=key, value=value)

result.append(res)

return result

else:

value = (str(value))

return value

def execute(self, sql_cmd):

try:

self._getCursor().execute(sql_cmd)

self._conn.commit()

data = self._getCursor().fetchall()

return data

except Exception:

self._conn.rollback()

return None

def insert(self, table, ins_data):

sql = ''

for data in ins_data:

key = ','.join(data.keys())

values = map(self._deal_values, data.values())

ins_data = ', '.join(values)

sql = "INSERT INTO {table}({key}) VALUES ({val})".format(

table=table,

key=key,

val=ins_data

)

return self.execute(sql)

def update(self, table, data, condition=None):

update_list = self._deal_values(data)

update_data = ",".join(update_list)

if condition is not None:

condition_list = self._deal_values(condition)

condition_data = ' AND '.join(condition_list)

sql = "UPDATE {table} SET {values} WHERE {condition}".format(table=table, values=update_data, condition=condition_data)

else:

sql = "UPDATE {table} SET {values}".format(table=table, values=update_data)

return self.execute(sql)

def select(self, table, filed, value):

sql = "SELECT * FROM {table} WHERE {filed} = '{value}'".format(table=table, filed=filed, value=value)

return self.execute(sql)

def select_all(self, table):

sql = "SELECT * FROM {table}".format(table=table)

return self.execute(sql)

def delete(self, table, condition={}):

condition_list = self._deal_values(condition)

condition_data = ' AND '.join(condition_list)

sql = "DELETE FROM {table} {where} {condition}".format(

table=table,

where='' if condition_data == '' else 'WHERE',

condition=condition_data

)

return self.execute(sql)