Python与数据库连接

新建表boss

create table 创建表

Code

import pymysql

con = pymysql.connect(host='localhost',\
                      user='root',\
                      password='',\
                      port=3306,\
                      db='business')
cursor=con.cursor()
cursor.execute('''create table if not exists boss(
                    id int auto_increment primary key,
                    name varchar(20)not null,
                    salary int not null)''')
1
2
3
4
5
6
7
8
9
10
11
12

添加数据

inser into添加数据
con.commit() #提交

Code

cursor.execute('''
        insert into boss(name,salary)
        values('Jack',91),('Harden',1300),('Pony',200)
        ''')
con.commit()#提交
1
2
3
4
5

删除数据

delete from删除数据

Code

# 删除
cursor.execute('delete from boss where salary < 100')
con.commit()
1
2
3

更新

update boss set salary = 2000 where name = 'Pony'

# 更新
cursor.execute("update boss set salary = 2000 where name = 'Pony'")
con.commit()
1
2
3

数据库封装

报错：AttributeError: 'UsingMysql' object has no attribute '_log_time'
解决方法：https://www.php1.cn/detail/ChengGongJieJue__4c4b7b31.html

from timeit import default_timer

import pymysql

host='losthost'
port=3306
db='business'
user='root'
password=''

# 类似参数化（PyMySQL操作数据库）
def get_connection():
    conn = pymysql.connect(host=host,port=port,db=db,user=user,password=password)
    return conn


# 使用with优化代码


class UsingMysql(object):
    def __init__(self,commit=True,log_time=True,log_label='总用时'):
        '''
        paramcommit是否在最后提交事务（设置为False的时候方便单元测试）
        paramlog_time：是否打印程序运行总时间
        paramlog_label:自定义log的文字
        '''
        self.log_time = log_time
        self.commit = commit
        self._log_label = log_label

    def __enter__(self):  # 如果需要记录时间
        if self._log_time is True:
            self._start = default_timer()
            # 在进入的时候自动获取连接和cursor
            conn = get_connection()
        cursor= conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit=False
        self._conn = conn
        self._cursor= cursor
        return self

    # 资源释放
    def __exit__(self, *exc_info):
        # 提交事务
        if self._commit:
            self._conn.commit()
        #在退出的时候自动关闭连接和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print('- % s: %.6f秒 ' % (self._log_label, diff))   #总用时

    # 不必要的封装
    # 一系列封装的业务方法
    #返回count
    # def get_count(self,sql,params=None,count_key='count(id)'):
    #     self.cursor.execute(sql.params)
    #     data = self.cursor.fetchone()
    #     if not data:
    #         return 0
    #     return data[count_key]

    # def fetch_one(self, sql,params=None):

if __name__ == '__main__':
    with UsingMysql(log_time=True) as um:
        um._cursor.execute('select count(id) as total from boss')
        data = um._cursor.fetchone()  # 2
        print("当前记录数量：%d" % data['total'])
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

预期结果：

参考文章：https://blog.csdn.net/BJ1599449/article/details/117026500