“Python操作MySQL数据库”的版本间的差异

2018年5月17日 (四) 14:33的版本

操作MySQL数据库

安装PyMySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。在命令行中使用：

pip3 install PyMySQL

数据库连接

接下来我们在 MySQL 中创建 cloud 数据库，并创建 user 数据表，表结构如下：

mysql> create database cloud;
    
Query OK, 1 row affected (0.00 sec)

mysql> use cloud;
Database changed
mysql>  CREATE TABLE IF NOT EXISTS `user`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `password` VARCHAR(40) NOT NULL,
   PRIMARY KEY ( `id` )  
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql>  CREATE UNIQUE INDEX uname on user(name);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

插入一些数据：

mysql> INSERT INTO `user` (name,password) VALUES ('ma', '123456');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO `user` (name,password) VALUES ('ma', '123457');
ERROR 1062 (23000): Duplicate entry 'ma' for key 2

</nowiki> Python连接数据库代码：

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","root","000000","cloud" )
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

执行以上脚本输出结果如下：

Database version : 5.0.22-community-nt

创建数据库表

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","root","000000","cloud" )
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYER")
 
# 使用预处理语句创建表
sql = """CREATE TABLE EMPLOYER (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
 
cursor.execute(sql)
 
# 关闭数据库连接
db.close()

数据库插入操作

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","root","000000","cloud" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

数据库查询操作

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
fetchall(): 接收全部的返回结果行.
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

实例：

查询EMPLOYEE表中salary（工资）字段大于1000的所有数据：

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","root","000000","cloud" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
       # 打印结果
      print ("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")
 
# 关闭数据库连接
db.close()

数据库更新操作

数据库删除操作

执行事务

首先需要安装MySQLDb

http://trac.edgewall.org/wiki/MySqlDb

MySQLDb模块的主要方法：

commit() ：提交事务。
rollback() ：回滚事务。
callproc(self, procname, args)：用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数。
execute(self, query, args)：执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数。
executemany(self, query, args)：执行单条sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数。

nextset(self)：移动到下一个结果集。
fetchall(self)：接收全部的返回结果行。
fetchmany(self, size=None)：接收size条返回结果行，如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据。
fetchone(self)：返回一条结果行。
scroll(self, value, mode='relative')：移动指针到某一行。如果mode='relative'则表示从当前所在行移动value条，如果 mode='absolute'则表示从结果集的第一行移动value条。

查询记录

import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',user='root',passwd='root',db='test',port=3306)
    cur=conn.cursor()
    cur.execute('select * from user')
    cur.close()
    conn.close()
except MySQLdb.Error,e:
     print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

插入数据

import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',user='root',passwd='root',port=3306)
    cur=conn.cursor()
    cur.execute('create database if not exists python')
    conn.select_db('python')
    cur.execute('create table test(id int,info varchar(20))')
    value=[1,'hi rollen']
    cur.execute('insert into test values(%s,%s)',value)
    values=[]
    for i in range(20):
        values.append((i,'hi rollen'+str(i)))
    cur.executemany('insert into test values(%s,%s)',values)
    cur.execute('update test set info="I am rollen" where id=3')
    conn.commit()
    cur.close()
    conn.close()
except MySQLdb.Error,e:
     print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

返回文件和数据格式化（输入与输出）