mysql数据操作工具函数
def dict_fetchall(cursor):
# 将查询多行数据 转为 json列表
# return all rows from a cursor as a dict list
columns = [col[0] for col in cursor.description]
return [dict(zip(columns, row)) for row in cursor.fetchall()]
mysql实践代码
import pymysql
# config
DB_CONFIG = {
# "host": "127.0.0.1",
"host": "121.43.147.156",
"port": 3308,
"user": "root",
"passwd": "cxj@123456",
# "passwd": "",
"db": "facexiaoyan",
"charset": "utf8"
}
class MySQLManager(object):
def __init__(self):
self.conn = None
self.cursor = None
self.connect()
def connect(self):
self.conn = pymysql.connect(
host=DB_CONFIG["host"],
port=DB_CONFIG["port"],
user=DB_CONFIG["user"],
passwd=DB_CONFIG["passwd"],
db=DB_CONFIG["db"],
charset=DB_CONFIG["charset"]
)
self.cursor = self.conn.cursor()
def close(self):
self.cursor.close()
self.conn.close()
print('mysql:finished')
def __def__(self):
self.cursor.close()
self.conn.close()
# 查询数据
def fetchone(self, query, n=9999999):
print(query)
self.cursor.execute(query)
for _ in range(n):
one = self.cursor.fetchone() # tuple
if one:
return one
def fetchone_dt(self, query, n=9999999):
print(query)
rowcount = self.cursor.execute(query)
columns = [i[0] for i in self.cursor.description]
length = len(columns)
for _ in range(min(n, rowcount)):
one = self.cursor.fetchone() # tuple
return {columns[i]: one[i] for i in range(length)}
def get_one(self, sql, args=None):
self.cursor.execute(sql, args)
row = self.cursor.fetchone()
result = row[0]
return result
def get_row(self, sql, args=None):
self.cursor.execute(sql, args)
result = self.cursor.fetchone()
return result
def get_rows(self, sql, args=None):
# 查询多行数据，结果：row tuple of tuple
self.cursor.execute(sql, args)
result = self.cursor.fetchall()
return result
# 返回数据多样化：json、列表
def dict_fetchall(self, sql, args=None):
# 将查询多行数据,转为每行数据生成一个dict，row_dict元素的列表
# dict：{field1:row1value1，field2:row1value2，field3:row1value3}
self.cursor.execute(sql, args)
columns = [col[0] for col in self.cursor.description]
result = [dict(zip(columns, row)) for row in self.cursor.fetchall()]
return result
def columns_list_fetchall(self, query, n=9999999):
# 将查询多行数据，全部多行数据转为：大列表套小列表，每列数据为一个小列表
print(query)
self.cursor.execute(query)
columns = [i[0] for i in self.cursor.description]
length = len(columns)
elems = self.cursor.fetchall()
columns_list = list()
for i in range(length):
columns_list.append([])
for elem in elems:
for i in range(length):
columns_list[i].append(elem[i])
result = columns_list[0] if length == 1 else columns_list
return result
def one_list_fetchall(self, sql, args=None):
# 将查询多行数据，全部多行数据转为：同一个列表
from itertools import chain
self.cursor.execute(sql, args)
elems = self.cursor.fetchall()
resultlist = list(chain.from_iterable(elems))
return resultlist
# 写入数据
def create(self, sql, args=None):
self.cursor.execute(sql, args)
self.conn.commit()
last_id = self.cursor.lastrowid
return last_id
def modify(self, sql, args=None):
self.cursor.execute(sql, args)
self.conn.commit()
# 执行多条SQL语句
def multi_modify(self, sql, args=None):
self.cursor.executemany(sql, args)
self.conn.commit()
def commit(self, sql):
print(sql)
try:
self.cursor.execute(sql)
self.conn.commit()
except Exception as e:
print(e)
def insert(self, dt, tb):
ls = [(k, v) for k, v in dt.items() if v is not None]
sql = 'INSERT %s (' % tb + ','.join(i[0] for i in ls) + \
') VALUES (' + ','.join('%r' % i[1] for i in ls) + ')'
self.commit(sql)
def update(self, dt, dt_condition, tb):
sql = 'UPDATE %s SET ' % tb + ','.join('%s=%r' % (k, v) for k, v in dt.items()) \
+ ' WHERE ' + ' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items())
self.commit(sql)
def insert_or_update(self, dt, dt_condition, tb):
"""有则更新，无则插入
dt = {
'lock_id': '1',
'company_id': '2',
'user_id': '6',
'start_time': '2022-06-01 17:06:12',
'expired_time': '2022-07-07 21:37:12'
}
ls = [('lock_id', '1'), ('company_id', '2'), ('user_id', '6'), ('start_time', '2022-06-01 17:06:12'),
('expired_time', '2022-07-07 21:37:12')]
sql = lock (lock_id,company_id,user_id,start_time,expired_time)
VALUES ('1','2','6','2022-06-01 17:06:12','2022-07-07 21:37:12')
dt_condition = {
'lock_id': '1',
'company_id': '2',
}
"""
query = 'SELECT * FROM ' + tb + ' WHERE ' + \
' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items())
if self.cursor.fetchall(query) == ():
dt.update(dt_condition)
self.insert(dt, tb)
else:
self.update(dt, dt_condition, tb)
def replace(self, dt, tb):
"""
若存在相同数据，会先删除，再重新插入
缺点：
1、需要考虑全部数据列，不然会丢失部分列
2、索引的原因，导致插入效率较低
"""
ls = [(k, v) for k, v in dt.items() if v is not None]
sql = 'REPLACE INTO %s (' % tb + ','.join(i[0] for i in ls) + \
') VALUES (' + ','.join('%r' % i[1] for i in ls) + ')'
self.commit(sql)
if __name__ == '__main__':
m = MySQLManager()
def get_column_by_list(self, table:str, out_field:str, input_field:str, list_condition:list): # 例如：查找一列（column）input_field=user_id，返回一列（column）user_name=output_field query = 'SELECT ' + out_field + ' FROM ' + table + ' WHERE ' + \ ' OR '.join(input_field + ' = ' + str(v) for v in list_condition) print(query) self.cursor.execute(query) columns = [i[0] for i in self.cursor.description] length = len(columns) elems = self.cursor.fetchall() columns_list = list() for i in range(length): columns_list.append([]) for elem in elems: for i in range(length): columns_list[i].append(elem[i]) result = columns_list[0] if length == 1 else columns_list return result
def dict_insert_or_update(self, dt, dt_condition, tb):
"""有则更新，无则插入
dt = {
'lock_id': '1',
'company_id': '2',
'user_id': '6',
'start_time': '2022-06-01 17:06:12',
'expired_time': '2022-07-07 21:37:12'
}
ls = [('lock_id', '1'), ('company_id', '2'), ('user_id', '6'), ('start_time', '2022-06-01 17:06:12'),
('expired_time', '2022-07-07 21:37:12')]
sql = lock (lock_id,company_id,user_id,start_time,expired_time)
VALUES ('1','2','6','2022-06-01 17:06:12','2022-07-07 21:37:12')
dt_condition = {
'lock_id': '1',
'company_id': '2',
}
"""
query = 'SELECT * FROM ' + tb + ' WHERE ' + \
' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items())
if self.cursor.fetchall(query) == (): # 如果 dt_condition 没查到
dt.update(dt_condition) # 将dt_condition并入dt数据中，字典append到字典
self.dict_insert(dt, tb) # 将dt数据插入
else:
self.dict_update(dt, dt_condition, tb) # 查到则更新
# user_id权限下全部锁，company_id定义的全部lock_set，将被包含的lock_set_id找出，生成selected_set列表返回m = MySQLManager()
sql_str = 'select lock_id from {} where user_info_id=1'.format('repository_userinfotolock')
authlock_in_user = set(m.columns_list_fetchall(sql_str)) # 用户有权限的 锁列表
sql_str = 'select lock_set_id from {} where company_id=2'.format('repository_lockset')
locksets = m.columns_list_fetchall(sql_str)
selected_set = list()
for lock_set_id in locksets: # 每一个锁集合对应的 锁列表
sql_str = 'select lock_id from {} where lock_set_id={}'.format('repository_locksettolock', lock_set_id)
lock_in_set = set(m.columns_list_fetchall(sql_str))
print(lock_in_set)
if lock_in_set.issubset(authlock_in_user): # 锁集合的锁列表 包含在 用户有权限的锁列表，则记录到select_set列表
selected_set.append(lock_set_id)
m.close()
python在使用MySQLdb库的时候，如下方法默认获取的cursor的结果集是tuple结构的。
con = MySQLdb.connect('host',port,'username','passwod','db_name','gbk')
curosr = con.cursor()
sql = "select * from test_table" #test_table : name,age
cusor = cursor.execute(sql)
r = cusor.fetchone()
print r[0]+'\t'+r[1]
cursor.close()
con.close()
使用DictCursor，这样得到的结果集就是字典形式的了，我们可以用key去获取数据了。
cursor = con.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute(sql)
r = cursor.fetchone()
print r['name']+'\t'+r['age']
Python任意字典写入MySQL
from pymysql.connections import Connection class Mysql: def __init__(self, password, database, host='localhost', user='root'): self.db = Connection( user=user, password=password, host=host, database=database, port=3306, charset='UTF8') self.cursor = self.db.cursor() def __del__(self): self.cursor.close() self.db.close() def commit(self, sql): print(sql) try: self.cursor.execute(sql) self.db.commit() except Exception as e: print(e) def fetchall(self, query): self.cursor.execute(query) return self.cursor.fetchall() # 有数据：tuple of tuple；无数据：() def insert(self, dict, table): ls = [(k, v) for k, v in dict.items() if v is not None] sql = 'INSERT %s (' % table + ','.join(i[0] for i in ls) + \ ') VALUES (' + ','.join('%r' % i[1] for i in ls) + ')' self.commit(sql) def update(self, dt, dt_condition, tb): sql = 'UPDATE %s SET ' % tb + ','.join('%s=%r' % (k, v) for k, v in dt.items()) \ + ' WHERE ' + ' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items()) self.commit(sql) def insert_or_update(self, dt, dt_condition, tb): """有则更新，无则插入""" query = 'SELECT * FROM ' + tb + ' WHERE ' + \ ' AND '.join('%s=%r' % (k, v) for k, v in dt_condition.items()) if self.fetchall(query) == (): dt.update(dt_condition) self.insert(dt, tb) else: self.update(dt, dt_condition, tb)
一、一对一：lock_id查lock_location
def lockidToLocklocation(lockid:int):
二、一对多：申请授权id查申请锁列表（list result：dict(lock_id:lock_location)）
def summitidToLocklist(summitid:int):
三、一对多：用户id查权限锁列表（list result：dict(lock_id:lock_location)）
def useridToLocklist(userid:int):
四、一对多：公司id查锁列表（list result：dict(lock_id:lock_location)）
def companyidToLocklist(companyid:int):
五、列表对json列表：锁id查多字段fields和values（list result：dict(field1:value1，field2：value2。。。)）
def lockidToDictlist(lockid:list):Jsonlist
select查询返回值（默认的tuple元组类型、返回dict字典类型、list列表）
默认的tuple元组类型
import pymysql
def export(table_name):
conn =pymysql.connect(host = '118.24.3.40',
user = 'jxz',password='123456',
db='jxz',port=3306,charset = 'utf8')
cur = conn.cursor()
cur.execute('select * from %s'%table_name)
print(cur.fetchall())
export('app_student')
返回dict字典类型
import pymysql
def export(table_name):
conn =pymysql.connect(host = '118.24.3.40',
user = 'jxz',password='123456',
db='jxz',port=3306,charset = 'utf8')
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
cur.execute('select * from %s'%table_name)
print(cur.fetchall())
export('app_student')
list列表
from itertools import chain ... sql="select elems from table" cursor.execute(sql) elems = cursor.fetchall() resultlist = list(chain.from_iterable(elems)) ...
