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))
...