python中通过pymysql模块操作数据库对数据库进行增删改查

1.pymysql操作数据库创造三张表 员工表：员工ID、姓名、出生日期、email、部门ID

部门表：部门ID、部门名称

工资表：id、工资日、员工ID、基本工资、奖金、实发工资 （1）在一台虚拟机上安装mysql数据库并创建库名，授权用户。 （2）编写python脚本创建相应的表

import pymysql conn = pymysql.connect( //创建到数据库服务器的连接 host = '192.168.1.11', port = 3306, user = 'tom', password = '123456', db = "db1", charset = 'utf8' ) cur = conn.cursor() //创建游标 create_dep = '''CREATE TABLE departments( dep_id INT PRIMARY KEY,dep_name VARCHAR(20))''' create_emp = '''CREATE TABLE employee(emp_id INT,emp_name varchar(20), brit_date DATE,email VARCHAR(50),dep_id INT,FOREIGN KEY(emp_id) REFERENCES departments(dep_id))''' create_gz = '''CREATE TABLE gzb(id INT,gz_data DATE,emp_id INT ,basic INT,awards INT,PRIMARY KEY(id) ,FOREIGN KEY(emp_id) REFERENCES employee(emp_id))''' cur.execute(create_dep) //执行游标里的sql语句 cur.execute(create_emp) cur.execute(create_gz) cur.close() //关闭游标 conn.close() //关闭连接

回到数据库查看是否创建成功 (3)编写python脚本向第二张表插入数据.

import pymysql conn = pymysql.connect( host = '192.168.1.11', port = 3306, user = 'tom', password = '123456', db = "db1", charset = 'utf8' ) cur = conn.cursor() insert1 = 'INSERT INTO departments VALUES(%s,%s)' hr = [(1,'人事部')] deps = [(2,'ops'),(3,'dev'),(4,'qa'),(5,'market'),(6,'sales')] cur.executemany(insert1,hr) cur.executemany(insert1,deps) conn.commit() cur.close() conn.close()

回到数据库查看数据是否插入成功 (4) python编写查表里的数据.

import pymysql conn = pymysql.connect( host = '192.168.1.11', port = 3306, user = 'tom', password = '123456', db = "db1", charset = 'utf8' ) cur = conn.cursor() select1 = 'SELECT * FROM departments' cur.execute(select1) result1 = cur.fetchone() print(result1) result2 = cur.fetchmany(2) print(result2) result3 = cur.fetchall() print(result3) cur.execute(select1)

效果如下 (5)python编写移动游标的方式来查数据

import pymysql conn = pymysql.connect( host = '192.168.1.11', port = 3306, user = 'tom', password = '123456', db = "db1", charset = 'utf8' ) cur = conn.cursor() select1 = 'SELECT * FROM departments' cur.execute(select1) cur.scroll(2,mode='absolute') //absolute总是从开头算起 result1 = cur.fetchone() print(result1) cur.scroll(1) //从当前位置算起 result2 = cur.fetchone() print(result2) conn.commit() cur.close() conn.close()

效果如下 （6）python编写修改数据库中字段的值

import pymysql conn = pymysql.connect( host = '192.168.1.11', port = 3306, user = 'tom', password = '123456', db = "db1", charset = 'utf8' ) cur = conn.cursor() update1 = 'UPDATE departments SET dep_name=%s WHERE dep_name=%s' cur.execute(update1,('生产部','人事部')) conn.commit() cur.close() conn.close()

回到数据库查看是否更改成功 ``(7)编写删除数据库中的某字段的值

import pymysql conn = pymysql.connect( host = '192.168.1.11', port = 3306, user = 'tom', password = '123456', db = "db1", charset = 'utf8' ) cur = conn.cursor() delete1 = 'DELETE FROM departments WHERE dep_id=%s' cur.execute(delete1,(6)) conn.commit() cur.close() conn.close()

回到数据库查看结果 欢迎大家观看我的视频教程：Python入门到进阶