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实践代码

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
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()




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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






?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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列表返回




?
1
2
3
4
5
6
7
8
9
10
11
12
13
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结构的。




?
1
2
3
4
5
6
7
8
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去获取数据了。　　




?
1
2
3
4
cursor = con.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute(sql)
r = cursor.fetchone()
print r['name']+'\t'+r['age']




　


Python任意字典写入MySQL


　




?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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元组类型




?
1
2
3
4
5
6
7
8
9
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字典类型




?
1
2
3
4
5
6
7
8
9
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列表　　




?
1
2
3
4
5
6
7
from itertools import chain
...
sql="select elems from table"
cursor.execute(sql)
elems = cursor.fetchall()
resultlist = list(chain.from_iterable(elems))
...




　　


　　


 


 


　　






            

            
posted @ 
 
pearlcity 
阅读(47) 
评论(0) 
编辑 
收藏 
举报


        

	    
	    
    









    
    

    
    



    

        
    

        

            
                
                
            
        

    



    相关博文：

    

            

                ·
                关于调测 参数 各种经验代码
            

            

                ·
                mongodb学习经验
            

            

                ·
                MySQL存储 pymysql模块 
            

            

                ·
                2022/6/18总结——Python之pymysql
            

            

                ·
                08、python操作mysql
            

    





    

    阅读排行：
    

 ·          Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
        

 ·          被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
        

 ·          没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
        

 ·          园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
        

 ·          【自荐】一款简洁、开源的在线白板工具 Drawnix
        



    
    



	

	

	
	

	

	

	





    

    
    








  

  
点击右上角即可分享

  微信分享提示