Python操作MySQL

 

1.安装PyMySQL

使用pip命令安装PyMySQL库。

pip install PyMySQL

2.创建数据库

使用脚本create_employee.sql创建数据库mytestdb，以及创建数据表employee。

create_employee.sql代码如下：

create database if not exists mytestdb character set utf8;

use mytestdb;

drop table if exists `employee`;

create table `employee`(
  `id` int(11) not null auto_increment,
  `name` varchar(40) default null,
  `age` int default null,
  `sex` char(1) default null,
  `income` float default null,
  primary key (`id`)
)engine=innodb default charset=utf8;

mysql> source C:/Users/INT 3/Desktop/create_employee.sql;

3.数据库插入操作

import pymysql
#打开数据库连接
db = pymysql.connect(host='127.0.0.1', user='root',password='123456',
                     database='mytestdb',port=3306, charset='utf8')
#使用cursor()方法获取操作游标
cursor = db.cursor()
#SQL插入语句
sql = "insert into employee(name, age, sex, income) values('王五', 26, 'F', 9000)"
try:
    cursor.execute(sql)
    db.commit()
except Exception as e:
    print(e)
    db.rollback()
finally:
    db.close()

也可以使用变量向SQL语句中传递参数： 

import pymysql
#打开数据库连接
db = pymysql.connect(host='127.0.0.1', user='root',password='123456',
                     database='mytestdb',port=3306, charset='utf8')
cursor = db.cursor()
sql = "insert into employee(name, age, sex, income) values('%s', '%d', '%s', '%d')"%('张三',30, 'M', 6000)
try:
    cursor.execute(sql)
    db.commit()
except Exception as e:
    print(e)
    db.rollback()
finally:
    db.close()

4.批量插入操作

cursor.executemany批量执行sql语句。

import pymysql

db = pymysql.connect(host='127.0.0.1', user='root', password='123456', port=3306,
                     database='mytestdb', charset='utf8')
cursor = db.cursor()
sql = "insert into employee(name, age, sex, income)values (%s, %s, %s, %s)"
ls = []
employ1 = ('张三', 22, 'M', 7000)
employ2 = ('李四', 25, 'F', 8000)
ls.append(employ1)
ls.append(employ2)
try:
    #cursor.executemany批量执行sql语句
    cursor.executemany(sql, ls)
    #提交到数据库执行
    db.commit()
except Exception as e:
    print(e)
    db.rollback()
finally:
    db.close()

5.查询操作

python查询Mysql，使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据。
rowcount是一个只读属性，并返回execute()方法后影响的行数。

import pymysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='mytestdb', port=3306, charset='utf8')
#使用cursor方法操作游标
cursor = db.cursor()
sql = "select * from employee where income > '%d'" %(2000)
try:
    #执行sql语句
    cursor.execute(sql)
    #获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        name = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        #打印结果
        print("id=%s, name=%s, age=%d, sex=%s, income=%d" %(id, name, age, sex, income))
except Exception as e:
    print(e)
finally:
    db.close()

6.更新操作

#数据库更新操作
import pymysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456',
                     database='mytestdb', port=3306, charset='utf8')
cursor = db.cursor()
sql = "update employee set age = '%d' where id = '%s'"%(28, 1)
try:
    #执行sql语句
    cursor.execute(sql)
    #提交到数据库执行
    db.commit()
except Exception as e:
    print(e)
    db.rollback()
finally:
    db.close()

7.删除操作

import pymysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456',
                     database='mytestdb', port=3306, charset='utf8')
cursor = db.cursor()
sql = "delete from employee where id='%s'"%(1)
try:
    cursor.execute(sql)
    db.commit()
except Exception as e:
    print(e)
    #发生错误时回滚
    db.rollback()
finally:
    db.close()

8.执行事务

#执行事务
import pymysql

db = pymysql.connect(host='127.0.0.1', user='root', password='123456',
                     database='mytestdb', port=3306, charset='utf8')
cursor = db.cursor()
#SQL删除记录语句
sql = "delete from employee where age > '%d'"%(20)
try:
    #执行SQL语句
    cursor.execute(sql)
    #向数据库提交
    db.commit()
except:
    #发生错误时回滚
    db.rollback()