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

刺骨的言语ヽ痛彻心扉 2022-06-01 11:28 172阅读 0赞

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