python操作mysql数据库

说明：这里仅仅为了演示python操作MySQL数据库，真实环境中，最好把CURD分别封装为对应的方法。并将这些方法在类中封装，体现python面向对象的特征。python链接MySQL数据库

建表

create database mydb;
use mydb;
create table EMP(EMPNO int(4) not null,ENAME varchar(10),JOB varchar(9),MGR int(4),HIREDATE date,SAL int(7 ),COMM int(7 ),DEPTNO int(2));alter table EMP add constraint PK_EMP primary key (EMPNO);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, str_to_date('17-12-1980', '%d-%m-%Y'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, str_to_date('20-02-1981', '%d-%m-%Y'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, str_to_date('22-02-1981', '%d-%m-%Y'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, str_to_date('02-04-1981', '%d-%m-%Y'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('28-09-1981', '%d-%m-%Y'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, str_to_date('01-05-1981', '%d-%m-%Y'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, str_to_date('09-06-1981', '%d-%m-%Y'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, str_to_date('19-04-1987', '%d-%m-%Y'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, str_to_date('17-11-1981', '%d-%m-%Y'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, str_to_date('08-09-1981', '%d-%m-%Y'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, str_to_date('23-05-1987', '%d-%m-%Y'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, str_to_date('03-12-1981', '%d-%m-%Y'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, str_to_date('03-12-1981', '%d-%m-%Y'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, str_to_date('23-01-1982', '%d-%m-%Y'), 1300, null, 10);
commit;

添加操作

# 导入mysql模块
import pymysql# 链接数据库IP
host = "127.0.0.1"
# 数据库用户名
user = "root"
# 数据库密码
password = "admin"
# 数据库名
dbname = "mydb"
# 获取数据库连接
db = pymysql.connect(host=host, user=user, password=password, db=dbname)
# 调用cursor()方法创建一个对象cursor
cursor = db.cursor()# SQL 插入语句
empno = 1111
eneme = "admin"
job = "CLERK"
mgr = 7788
sal = 3000
comm = 100
hiredate = "2000-09-09"
deptno = 10# 注意数据库中的字符串需要使用单引号哦
sql = f"insert into emp(empno,ename,job,mgr,sal,comm,hiredate,deptno) \values \({empno},'{eneme}','{job}',{mgr},{sal},{comm},'{hiredate}',{deptno})"
print(sql)try:# 执行sqlcursor.execute(sql)# 提交事务db.commit()except Exception as e:print(e.args)# 如有异常事务db.rollback()finally:# 关闭资源cursor.close()db.close()

查询单条数据

import pymysqlhost = "localhost"
user = "root"
password = "admin"
dbname = "mydb"db = pymysql.connect(host=host, user=user, password=password, db=dbname)cursor = db.cursor()empno = 7788sql = f"select * from emp where empno ={empno}"try:cursor.execute(sql)# 返回单条数据,会将数据封装到元祖中results = cursor.fetchone()# 输出： (7788, 'SCOTT', 'ANALYST', 7566, datetime.date(1987, 4, 19), 3000, None, 20)print(results)except Exception as e:# 异常处理print(e.args)finally:# 关闭资源cursor.close()db.close()

查询多条数据

import pymysqlhost = "localhost"
user = "root"
password = "admin"
dbname = "mydb"db = pymysql.connect(host=host, user=user, password=password, db=dbname)cursor = db.cursor()deptno = 10sql = f"select * from emp where deptno ={deptno}"try:cursor.execute(sql)# 返回多条条数据,会将每条数据封装到元组中，多条数据又封装到元组中results = cursor.fetchall()# 遍历for emp in results:print(emp)except Exception as e:# 异常处理print(e.args)finally:# 关闭资源cursor.close()db.close()

运行结果：

(1111, 'admin', 'CLERK', 7788, datetime.date(2000, 9, 9), 3000, 100, 10)
(7782, 'CLARK', 'MANAGER', 7839, datetime.date(1981, 6, 9), 2450, None, 10)
(7839, 'KING', 'PRESIDENT', None, datetime.date(1981, 11, 17), 5000, None, 10)
(7934, 'MILLER', 'CLERK', 7782, datetime.date(1982, 1, 23), 1300, None, 10)

修改操作

# 导入mysql模块
import pymysql# 链接数据库IP
host = "127.0.0.1"
# 数据库用户名
user = "root"
# 数据库密码
password = "admin"
# 数据库名
dbname = "mydb"
# 获取数据库连接
db = pymysql.connect(host=host, user=user, password=password, db=dbname)
# 调用cursor()方法创建一个对象cursor
cursor = db.cursor()# SQL 插入语句
empno = 1111
eneme = "ADMIN"
job = "MANGER"
mgr = 7788
sal = 4000
comm = 200
hiredate = "2000-10-10"
deptno = 10# 注意数据库中的字符串需要使用单引号哦
sql = f"""update emp set ename = '{eneme}',job = '{job}', mgr={mgr},sal={sal},comm={comm},hiredate ='{hiredate}',deptno={deptno}whereempno = {empno}"""
try:# 执行sqlcursor.execute(sql)# 提交事务db.commit()except Exception as e:print(e.args)# 如有异常事务db.rollback()finally:# 关闭资源cursor.close()db.close()

根据主键删除操作

# 导入mysql模块
import pymysql# 链接数据库IP
host = "127.0.0.1"
# 数据库用户名
user = "root"
# 数据库密码
password = "admin"
# 数据库名
dbname = "mydb"
# 获取数据库连接
db = pymysql.connect(host=host, user=user, password=password, db=dbname)
# 调用cursor()方法创建一个对象cursor
cursor = db.cursor()# SQL 语句
empno = 1111
sql = f"delete from emp where empno ={empno}"try:# 执行sqlcursor.execute(sql)# 提交事务db.commit()except Exception as e:print(e.args)# 如有异常事务db.rollback()finally:# 关闭资源cursor.close()db.close()