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

(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()  
​