Python与数据库连接

新建表boss

create table 创建表
在这里插入图片描述

Code

import pymysqlcon = 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)''')

添加数据

inser into添加数据
con.commit() #提交
在这里插入图片描述

Code

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

删除数据

delete from删除数据
在这里插入图片描述

Code

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

更新

update boss set salary = 2000 where name = 'Pony'
在这里插入图片描述

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

数据库封装

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

from timeit import default_timerimport pymysqlhost='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_timeself.commit = commitself._log_label = log_labeldef __enter__(self):  # 如果需要记录时间if self._log_time is True:self._start = default_timer()# 在进入的时候自动获取连接和cursorconn = get_connection()cursor= conn.cursor(pymysql.cursors.DictCursor)conn.autocommit=Falseself._conn = connself._cursor= cursorreturn self# 资源释放def __exit__(self, *exc_info):# 提交事务if self._commit:self._conn.commit()#在退出的时候自动关闭连接和cursorself._cursor.close()self._conn.close()if self._log_time is True:diff = default_timer() - self._startprint('- % 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()  # 2print("当前记录数量：%d" % data['total'])