Python操作MySQL数据库的三种方法

2018-01-31 のび太

(1) 什么是MySQLdb?

? MySQLdb 是用于 Python 连接 MySQL 数据库的接口,它实现了 Python 数据库 API 规范 V2.0,基于 MySQL C API 上建立的。

(2) 源码安装 MySQLdb: https://pypi.python.org/pypi/MySQL-python

$ tar zxvf MySQL-python-*.tar.gz

$ cd MySQL-python-*

$ python setup.py build

$ python setup.py install 

(3) MySQLdb 的使用:

#!/usr/bin/env python

# coding=utf-8

import MySQLdb

def connectdb():

print('连接到mysql服务器...')

# 打开数据库连接

# 用户名:p, 密码:12345.,用户名和密码需要改成你自己的mysql用户名和密码,并且要创建数据库TESTDB,并在TESTDB数据库中创建好表Student

db = MySQLdb.connect("localhost","p","12345.","TESTDB")

print('连接上了!')

return db

def createtable(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# 如果存在表Sutdent先删除

cursor.execute("DROP TABLE IF EXISTS Student")

sql = """CREATE TABLE Student (

ID CHAR(10) NOT NULL,

Name CHAR(8),

Grade INT )"""

# 创建Sutdent表

cursor.execute(sql)

def insertdb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 插入语句

sql = """INSERT INTO Student

VALUES ('001', 'CZQ', 70),

('002', 'LHQ', 80),

('003', 'MQ', 90),

('004', 'WH', 80),

('005', 'HP', 70),

('006', 'YF', 66),

('007', 'TEST', 100)"""

#sql = "INSERT INTO Student(ID, Name, Grade)

#    VALUES ('%s', '%s', '%d')" %

#    ('001', 'HP', 60)

try:

# 执行sql语句

cursor.execute(sql)

# 提交到数据库执行

db.commit()

except:

# Rollback in case there is any error

print '插入数据失败!'

db.rollback()

def querydb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 查询语句

#sql = "SELECT * FROM Student

#    WHERE Grade > '%d'" % (80)

sql = "SELECT * FROM Student"

try:

# 执行SQL语句

cursor.execute(sql)

# 获取所有记录列表

results = cursor.fetchall()

for row in results:

ID = row[0]

Name = row[1]

Grade = row[2]

# 打印结果

print "ID: %s, Name: %s, Grade: %d" %

(ID, Name, Grade)

except:

print "Error: unable to fecth data"

def deletedb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 删除语句

sql = "DELETE FROM Student WHERE Grade = '%d'" % (100)

try:

# 执行SQL语句

cursor.execute(sql)

# 提交修改

db.commit()

except:

print '删除数据失败!'

# 发生错误时回滚

db.rollback()

def updatedb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 更新语句

sql = "UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s'" % ('003')

try:

# 执行SQL语句

cursor.execute(sql)

# 提交到数据库执行

db.commit()

except:

print '更新数据失败!'

# 发生错误时回滚

db.rollback()

def closedb(db):

db.close()

def main():

db = connectdb()    # 连接MySQL数据库

createtable(db)     # 创建表

insertdb(db)        # 插入数据

print 'n插入数据后:'

querydb(db) 

deletedb(db)        # 删除数据

print 'n删除数据后:'

querydb(db)

updatedb(db)        # 更新数据

print 'n更新数据后:'

querydb(db)

closedb(db)         # 关闭数据库

if __name__ == '__main__':

main()

#!/usr/bin/env python

import MySQLdb  

try:  

conn=MySQLdb.connect(host='localhost',user='root',passwd='root',db='test',port=3306)

cur=conn.cursor()  

cur.execute('select user,password from user')

cur.close()  

conn.close()  

except MySQLdb.Error,e:  

print "Mysql Error %d: %s" % (e.args[0], e.args[1])   

#!/usr/bin/env python  

#_*_ coding:utf-8 _*_  

import MySQLdb  

try:  

conn = MySQLdb.Connect(host='192.168.8.40',user='root',passwd='root',db='mysql',port=3306)

cur = conn.cursor()  

rs = cur.execute('select user,password,host from user')  

rs = cur.execute('create database if not exists python')  

conn.select_db('python')  

cur.execute('create table test(id int,info varchar(30))')  

value = [1,'hi jack']  

cur.execute('insert into test values(%s,%s)',value)  

values = []  

for i in range(20):

values.append((i,'hi jack' + str(i)))  

cur.executemany('insert into test values(%s,%s)',values)  

cur.execute('update test set info="i am jack" where id=3')  

conn.commit()  

cur.close()  

conn.close()  

except MySQLdb.Error,e:  

print 'mysql error msg: %d,%s' % (e.args[0],e.args[1])   

import MySQLdb  

try:  

conn = MySQLdb.Connect(host='192.168.8.40',user='root',passwd='root',db='mysql',port=3306,charset='utf8')

cur = conn.cursor()  

conn.select_db('python')  

count = cur.execute('select * from test')   

print 'there has %s rows record' % count  

result = cur.fetchone()  

print result  

print 'id: %s info %s' % result  

result2 = cur.fetchmany(3)  

for record in result2:  

print record  

print '=='*10  

cur.scroll(0,mode='absolute')  

result3 = cur.fetchall()  

for record in result3:  

print record[0] ,'---',record[1]  

conn.commit()  

cur.close()  

conn.close()  

except MySQLdb.Error,e:  

print 'mysql error msg: %d,%s' % (e.args[0],e.args[1]) 

查询后中文会显示乱码,但在数据库中却是正常的,发现用一个属性有可搞定:

在Python代码 

conn = MySQLdb.Connect(host='localhost', user='root', passwd='root', db='python') 中加一个属性:

改为:

conn = MySQLdb.Connect(host='localhost', user='root', passwd='root', db='python',charset='utf8') 

charset是要跟你数据库的编码一样,如果是数据库是gb2312 ,则写charset='gb2312'。

然后,这个连接对象也提供了对事务操作的支持,标准的方法

commit() 提交

rollback() 回滚

cursor用来执行命令的方法:

callproc(self, procname, args):用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数

execute(self, query, args):执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数

executemany(self, query, args):执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数

nextset(self):移动到下一个结果集

cursor用来接收返回值的方法:

fetchall(self):接收全部的返回结果行.

fetchmany(self, size=None):接收size条返回结果行.如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据.

fetchone(self):返回一条结果行.

scroll(self, value, mode='relative'):移动指针到某一行.如果mode='relative',则表示从当前所在行移动value条,如果 mode='absolute',则表示从结果集的第一行移动value条.

2. PyMySQL 的使用

(1) 什么是 PyMySQL?

? PyMySQL 是 Python 中用于连接 MySQL 服务器的一个库,它遵循 Python 数据库 API 规范 V2.0,并包含了 pure-Python MySQL 客户端库。

(2) 安装 PyMysql:

pip install PyMysql

(3) 使用 PyMySQL:

#!/usr/bin/env python

# coding=utf-8

import pymysql

def connectdb():

print('连接到mysql服务器...')

# 打开数据库连接

# 用户名:p, 密码:12345.,用户名和密码需要改成你自己的mysql用户名和密码,并且要创建数据库TESTDB,并在TESTDB数据库中创建好表Student

db = pymysql.connect("localhost","hp","Hp12345.","TESTDB")

print('连接上了!')

return db

def createtable(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# 如果存在表Sutdent先删除

cursor.execute("DROP TABLE IF EXISTS Student")

sql = """CREATE TABLE Student (

ID CHAR(10) NOT NULL,

Name CHAR(8),

Grade INT )"""

# 创建Sutdent表

cursor.execute(sql)

def insertdb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 插入语句

sql = """INSERT INTO Student

VALUES ('001', 'CZQ', 70),

('002', 'LHQ', 80),

('003', 'MQ', 90),

('004', 'WH', 80),

('005', 'HP', 70),

('006', 'YF', 66),

('007', 'TEST', 100)"""

#sql = "INSERT INTO Student(ID, Name, Grade)

#    VALUES ('%s', '%s', '%d')" %

#    ('001', 'HP', 60)

try:

# 执行sql语句

cursor.execute(sql)

# 提交到数据库执行

db.commit()

except:

# Rollback in case there is any error

print '插入数据失败!'

db.rollback()

def querydb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 查询语句

#sql = "SELECT * FROM Student

#    WHERE Grade > '%d'" % (80)

sql = "SELECT * FROM Student"

try:

# 执行SQL语句

cursor.execute(sql)

# 获取所有记录列表

results = cursor.fetchall()

for row in results:

ID = row[0]

Name = row[1]

Grade = row[2]

# 打印结果

print "ID: %s, Name: %s, Grade: %d" %

(ID, Name, Grade)

except:

print "Error: unable to fecth data"

def deletedb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 删除语句

sql = "DELETE FROM Student WHERE Grade = '%d'" % (100)

try:

# 执行SQL语句

cursor.execute(sql)

# 提交修改

db.commit()

except:

print '删除数据失败!'

# 发生错误时回滚

db.rollback()

def updatedb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 更新语句

sql = "UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s'" % ('003')

try:

# 执行SQL语句

cursor.execute(sql)

# 提交到数据库执行

db.commit()

except:

print '更新数据失败!'

# 发生错误时回滚

db.rollback()

def closedb(db):

db.close()

def main():

db = connectdb()    # 连接MySQL数据库

createtable(db)     # 创建表

insertdb(db)        # 插入数据

print 'n插入数据后:'

querydb(db) 

deletedb(db)        # 删除数据

print 'n删除数据后:'

querydb(db)

updatedb(db)        # 更新数据

print 'n更新数据后:'

querydb(db)

closedb(db)         # 关闭数据库

if __name__ == '__main__':

main() 

import pymysql  

# 打开数据库连接(ip/数据库用户名/登录密码/数据库名)  

db = pymysql.connect("localhost", "root", "root", "test")  

# 使用 cursor() 方法创建一个游标对象 cursor  

cursor = db.cursor()  

# 使用 execute()  方法执行 SQL 查询  

cursor.execute("SELECT VERSION()")  

# 使用 fetchone() 方法获取单条数据.  

data = cursor.fetchone()  

print("Database version : %s " % data)  

# 关闭数据库连接  

db.close()   

import pymysql  

# 打开数据库连接(ip/数据库用户名/登录密码/数据库名)  

db = pymysql.connect("localhost", "root", "root", "test")  

# 使用 cursor() 方法创建一个游标对象 cursor  

cursor = db.cursor()  

# SQL 插入语句  

sql = """INSERT INTO user(name) 

VALUES ('Mac')"""  

try:  

# 执行sql语句  

cursor.execute(sql)  

# 提交到数据库执行  

db.commit()  

except:  

# 如果发生错误则回滚  

db.rollback()  

# 关闭数据库连接  

db.close()   

import pymysql  

# 打开数据库连接(ip/数据库用户名/登录密码/数据库名)  

db = pymysql.connect("localhost", "root", "root", "test")  

# 使用 cursor() 方法创建一个游标对象 cursor  

cursor = db.cursor()  

# SQL 查询语句  

sql = "SELECT * FROM user"  

try:  

# 执行SQL语句  

cursor.execute(sql)  

# 获取所有记录列表  

results = cursor.fetchall()

for row in results:  

id = row[0]  

name = row[1]  

# 打印结果  

print("id=%s,name=%s" %  

(id, name))  

except:  

print("Error: unable to fecth data")  

# 关闭数据库连接  

db.close()  

import pymysql  

# 打开数据库连接(ip/数据库用户名/登录密码/数据库名)  

db = pymysql.connect("localhost", "root", "root", "test")  

# 使用 cursor() 方法创建一个游标对象 cursor  

cursor = db.cursor()  

# SQL 更新语句  

sql = "UPDATE user SET name = 'Bob' WHERE id = 1"  

try:  

# 执行SQL语句  

cursor.execute(sql)  

# 提交到数据库执行  

db.commit()  

except:  

# 发生错误时回滚  

db.rollback()  

# 关闭数据库连接  

db.close()  

import pymysql  

# 打开数据库连接(ip/数据库用户名/登录密码/数据库名)  

db = pymysql.connect("localhost", "root", "root", "test")  

# 使用 cursor() 方法创建一个游标对象 cursor  

cursor = db.cursor()  

# SQL 删除语句  

sql = "DELETE FROM user WHERE id  = 1"  

try:  

# 执行SQL语句  

cursor.execute(sql)  

# 提交修改  

db.commit()  

except:  

# 发生错误时回滚  

db.rollback()  

# 关闭数据库连接  

db.close()  

3. mysql.connector 的使用

(1) 什么是 mysql.connector?

? 由于 MySQL 服务器以独立的进程运行,并通过网络对外服务,所以,需要支持 Python 的 MySQL 驱动来连接到 MySQL 服务器。

目前,有两个 MySQL 驱动:

mysql-connector-python:是 MySQL 官方的纯 Python 驱动;

MySQL-python :是封装了 MySQL C驱动的 Python 驱动。

(2) 安装 mysql.connector:

pip install mysql-connector-python

pip install MySQL-python

(3) 使用 mysql.connector:

#!/usr/bin/env python

# coding=utf-8

import mysql.connector

def connectdb():

print('连接到mysql服务器...')

# 打开数据库连接

# 用户名:p, 密码:12345.,用户名和密码需要改成你自己的mysql用户名和密码,并且要创建数据库TESTDB,并在TESTDB数据库中创建好表Student

db = mysql.connector.connect(user="p", passwd="12345.", database="TESTDB", use_unicode=True)

print('连接上了!')

return db

def createtable(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# 如果存在表Sutdent先删除

cursor.execute("DROP TABLE IF EXISTS Student")

sql = """CREATE TABLE Student (

ID CHAR(10) NOT NULL,

Name CHAR(8),

Grade INT )"""

# 创建Sutdent表

cursor.execute(sql)

def insertdb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 插入语句

sql = """INSERT INTO Student

VALUES ('001', 'CZQ', 70),

('002', 'LHQ', 80),

('003', 'MQ', 90),

('004', 'WH', 80),

('005', 'HP', 70),

('006', 'YF', 66),

('007', 'TEST', 100)"""

#sql = "INSERT INTO Student(ID, Name, Grade)

#    VALUES ('%s', '%s', '%d')" %

#    ('001', 'HP', 60)

try:

# 执行sql语句

cursor.execute(sql)

# 提交到数据库执行

db.commit()

except:

# Rollback in case there is any error

print '插入数据失败!'

db.rollback()

def querydb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 查询语句

#sql = "SELECT * FROM Student

#    WHERE Grade > '%d'" % (80)

sql = "SELECT * FROM Student"

try:

# 执行SQL语句

cursor.execute(sql)

# 获取所有记录列表

results = cursor.fetchall()

for row in results:

ID = row[0]

Name = row[1]

Grade = row[2]

# 打印结果

print "ID: %s, Name: %s, Grade: %d" %

(ID, Name, Grade)

except:

print "Error: unable to fecth data"

def deletedb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 删除语句

sql = "DELETE FROM Student WHERE Grade = '%d'" % (100)

try:

# 执行SQL语句

cursor.execute(sql)

# 提交修改

db.commit()

except:

print '删除数据失败!'

# 发生错误时回滚

db.rollback()

def updatedb(db):

# 使用cursor()方法获取操作游标 

cursor = db.cursor()

# SQL 更新语句

sql = "UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s'" % ('003')

try:

# 执行SQL语句

cursor.execute(sql)

# 提交到数据库执行

db.commit()

except:

print '更新数据失败!'

# 发生错误时回滚

db.rollback()

def closedb(db):

db.close()

def main():

db = connectdb()    # 连接MySQL数据库

createtable(db)     # 创建表

insertdb(db)        # 插入数据

print 'n插入数据后:'

querydb(db) 

deletedb(db)        # 删除数据

print 'n删除数据后:'

querydb(db)

updatedb(db)        # 更新数据

print 'n更新数据后:'

querydb(db)

closedb(db)         # 关闭数据库

if __name__ == '__main__':

main()

#!/usr/bin/python  

#coding=utf-8  

import mysql.connector  

from mysql.connector import errorcode  

class mysqlconnectordemo(object):  

def __init__(self):  

pass  

def connect(self,conf):  

try:  

conn = mysql.connector.connect(**conf)  

print("conn success!")  

except mysql.connector.Error as err:  

if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:  

print("Something is wrong with your user name or password")  

elif err.errno == errorcode.ER_BAD_DB_ERROR:  

print("Database does not exist")  

else:  

print(err)  

else:  

conn.close()  

def query(self,conf,sql):  

try:  

conn = mysql.connector.connect(**conf)  

print("conn success!")  

cursor = conn.cursor()  

try:  

cursor.execute(sql)  

values = cursor.fetchall()#返回是一个由元组构成的list,每一个元组是一行值  

print type(values)  

for i in values:  

print i  

except mysql.connector.Error as err:  

print('query datas error!{}'.format(err))  

except mysql.connector.Error as err:  

if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:  

print("Something is wrong with your user name or password")  

elif err.errno == errorcode.ER_BAD_DB_ERROR:  

print("Database does not exist")  

else:  

print(err)  

else:  

conn.close()  

def insert(self,conf,sql):  

try:  

conn = mysql.connector.connect(**conf)  

print("conn success!")  

cursor = conn.cursor()  

try:  

cursor.execute(sql)  

conn.commit()  

cursor.close()  

except mysql.connector.Error as err:  

print('insert datas error!{}'.format(err))  

except mysql.connector.Error as err:  

if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:  

print("Something is wrong with your user name or password")  

elif err.errno == errorcode.ER_BAD_DB_ERROR:  

print("Database does not exist")  

else:  

print(err)  

else:  

conn.close()  

#!/usr/bin/python  

#coding=utf-8  

import MySQLdb  

class mysqldbdemo(object):  

def __init__(self):  

pass  

def connect(self):  

conn = MySQLdb.connect("host_ip","user","password","database")  

conn.close()  

def query(self,sql):  

conn = MySQLdb.connect("host_ip","user","password","database")  

cursor = conn.cursor()  

try:  

cursor.execute(sql)  

values = cursor.fetchall()#返回是一个由元组构成的tuple,每一个元组是一行值  

print type(values)  

for i in values:  

print i  

except:  

print "Error: unable to fecth data"  

conn.close()  

def insert(self,sql):  

conn = MySQLdb.connect("host_ip","user","password","database")  

cursor = conn.cursor()  

try:  

cursor.execute(sql)  

conn.commit()  

except:  

conn.rollback()  

conn.close()