推广网站建设,网站内存不足,wordpress调用最近更新文章,erp系统界面说明#xff1a;这里仅仅为了演示python操作MySQL数据库#xff0c;真实环境中#xff0c;最好把CURD分别封装为对应的方法。并将这些方法在类中封装#xff0c;体现python面向对象的特征。python链接MySQL数据库
建表
create database mydb;
use mydb;
create table EMP…说明这里仅仅为了演示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(hosthost, useruser, passwordpassword, dbdbname)
# 调用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 finsert 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 mydbdb pymysql.connect(hosthost, useruser, passwordpassword, dbdbname)cursor db.cursor()empno 7788sql fselect * 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 mydbdb pymysql.connect(hosthost, useruser, passwordpassword, dbdbname)cursor db.cursor()deptno 10sql fselect * 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(hosthost, useruser, passwordpassword, dbdbname)
# 调用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 fupdate 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(hosthost, useruser, passwordpassword, dbdbname)
# 调用cursor()方法创建一个对象cursor
cursor db.cursor()# SQL 语句
empno 1111
sql fdelete 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()
