引入pymysql包
pip install pymysql
封装一个操作mysql的类
# 连接数据库操作
# python mysql 操作类
import pymysql
class DBHelper:
def __init__(self, host="127.0.0.1", port=3306, user="root", password="root",
database="effect"): # 构造函数
try:
self.conn = pymysql.connect(host=host, port=port, user=user, password=password, database=database,
charset='utf8')
self.cursor = self.conn.cursor()
except Exception as e:
print(e)
# 返回执行execute()方法后影响的行数
def execute(self, sql):
self.cursor.execute(sql)
rowcount = self.cursor.rowcount
return rowcount
# 删除并返回影响行数
def delete(self, **kwargs):
table = kwargs['table']
where = kwargs['where']
sql = 'DELETE FROM %s where %s' % (table, where)
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 提交到数据库执行
self.conn.commit()
# 影响的行数
rowcount = self.cursor.rowcount
except:
# 发生错误时回滚
self.conn.rollback()
return rowcount
# 新增并返回新增ID
def insert(self, **kwargs):
table = kwargs['table']
del kwargs['table']
sql = 'insert into %s(' % table
fields = ""
values = ""
for k, v in kwargs.items():
fields += "%s," % k
values += "'%s'," % v
fields = fields.rstrip(',')
values = values.rstrip(',')
sql = sql + fields + ")values(" + values + ")"
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 提交到数据库执行
self.conn.commit()
# 获取自增id
res = self.cursor.lastrowid
except:
# 发生错误时回滚
self.conn.rollback()
return res
# 修改数据并返回影响的行数
def update(self, **kwargs):
table = kwargs['table']
# del kwargs['table']
kwargs.pop('table')
where = kwargs['where']
kwargs.pop('where')
sql = 'update %s set ' % table
for k, v in kwargs.items():
sql += "%s='%s'," % (k, v)
sql = sql.rstrip(',')
sql += ' where %s' % where
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 提交到数据库执行
self.conn.commit()
# 影响的行数
rowcount = self.cursor.rowcount
except:
# 发生错误时回滚
self.conn.rollback()
return rowcount
# 查-一条条数据
def selectTopone(self, **kwargs):
table = kwargs['table']
field = 'field' in kwargs and kwargs['field'] or '*'
where = 'where' in kwargs and 'where ' + kwargs['where'] or ''
order = 'order' in kwargs and 'order by ' + kwargs['order'] or ''
sql = 'select %s from %s %s %s limit 1' % (field, table, where, order)
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 使用 fetchone() 方法获取单条数据.
data = self.cursor.fetchone()
except:
# 发生错误时回滚
self.conn.rollback()
return data
# 查所有数据
def selectAll(self, **kwargs):
table = kwargs['table']
field = 'field' in kwargs and kwargs['field'] or '*'
where = 'where' in kwargs and 'where ' + kwargs['where'] or ''
order = 'order' in kwargs and 'order by ' + kwargs['order'] or ''
sql = 'select %s from %s %s %s ' % (field, table, where, order)
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 使用 fetchone() 方法获取单条数据.
data = self.cursor.fetchall()
except:
# 发生错误时回滚
self.conn.rollback()
return data
调用方式
import MysqlExecutor
if __name__ == '__main__':
conn = MysqlExecutor.DBHelper('127.0.0.1', 3306, 'root', 'root', 'effect')
# insert测试
# cs = conn.insert(table="test", name = "张三",age = 11)
# delete 测试
# cs = conn.delete(table="test", where="id = 2")
# update 测试
# cs = conn.update(table="test", name="徐凤年", age=99, where="id = 1")
# select 测试
results = conn.selectAll(table="test")
for row in results:
id = row[0]
name = row[1]
age = row[2]
print("id = %s,name = %s, age = %s" %(id,name,age))
引用: 参考文章