Python操作MySQL数据库
来自CloudWiki
目录
操作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 EMPLOYER(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]))