Python基础--数据库操作(MySQL)

最新推荐文章于 2022-07-30 16:27:53 发布

忆昔如风

最新推荐文章于 2022-07-30 16:27:53 发布

阅读量246

点赞数

分类专栏： Python 数据库

本文链接：https://blog.csdn.net/wuq757693255/article/details/83034473

版权

Python 同时被 2 个专栏收录

11 篇文章 1 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

1.Python DB API

python 访问数据库的统一接口规范
https://www.python.org/dev/peps/pep-0249/

1.1 Python DB API 包含的内容：

数据库连接对象connection
数据库交互对象cursor
数据库异常类 exceptions

1.2 使用Python DB API 访问数据库流程：

开始–>创建connection–>获取cursor–>执行sql语句–关闭cursor–关闭connection–结束。

2.python mysql开发环境

python代码编辑器、python2.7–包含
Python-MySQL connector (sourceforge.net/projects/mysql-python)
MySQL服务器、MySQL客户端工具SQLyog

2.1安装Python-MySQL connector方法及问题处理

（本人环境是Win7 64位、Python2.7,MySQL 5.7）

方法1：下载点击exe安装即可，安装报错找不到python注册表，参考这个网址解决问题。https://www.crifan.com/python_install_module_mysql_mysqldb/
方法2：直接使用pip install MySQL-Python ，如果报错需先下载VCForPython27.msi安装 http://aka.ms/vcpython27 ，再使用MySQL安装工具安装MySQL Connector/C 6.1.3
方法3：下载MySQL_python‑1.2.5‑cp27‑none‑win_amd64.whl，然后执行pip install MySQL_python-1.2.5-cp27-none-win_amd64.whl ，下载地址：
https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python
其它系统环境下载安装同理，python2.7及python3建议使用PyMySQL 驱动。
下载地址 https://github.com/PyMySQL/PyMySQL

2.2 测试数据库驱动安装环境

import MySQLdb
print MySQLdb

3、DB API- 数据库连接对象connection

连接对象：建立python客户端与数据库的网络连接
创建方法：MySQLdb.Connect(host,port,user,passwd,db,charset)参数类型除了port是数字，其余都是字符串，host为MySQL服务器地址，db为数据库名称，charset为连接编码
connection对象支持的方法：

方法名	说明
cursor()	使用该连接创建并返回游标
commit()	提交当前事务
rollback()	回滚当前事务
close()	关闭连接

测试代码：

#test_connection
import MySQLdb
conn = MySQLdb.Connect(host = '127.0.0.1',port= 3306,user='xxx',passwd='xxx',db='',charset='utf8')
cursor=conn.cursor()
print conn
print cursor
cursor.close()
conn.close()

4. DB API- 数据库游标对象cursor

游标对象：用于执行查询和获取结果
cursor对象支持的方法

方法名	说明
execute(op[,args])	执行一个数据库查询和命令
fetchone()	取得结果集的下一行
fetchmany()	取取结果集的下几行
fetchall()	取取结果集剩下的所有行
rowcount()	最近一次执行execute返回数据的行数或者影响行数
close()	关闭游标对象

execute方法执行SQL 将数据结果从数据库获取到客户端
fetch*()方法：移动rownumber，返回数据

5. select查询数据

创建一个user表，且加6条数据:

CREATE TABLE `user` (
`userid` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARBINARY(100) DEFAULT NULL,
PRIMARY KEY (`userid`)
)ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

import MySQLdb
conn = MySQLdb.Connect(host = '127.0.0.1',port=3306 ,user='root',passwd='root',db='xxx',charset='utf8')
cursor=conn.cursor()
sql='select * from user'
cursor.execute(sql)
print cursor.rowcount #结果
rs = cursor.fetchone()
print rs
rs = cursor.fetchmany(2)
print rs
rs=cursor.fetchall()
print rs
cursor.close()
conn.close()

逐行运行后分别得到的输出结果：

6
(1L, 'name1')
((2L, 'name2'), (3L, 'name3'))
((4L, 'name4'), (5L, 'name5'), (13L, 'name6'))

如果需要打印cursor.fetchall()中所有的数据内容：

rs=cursor.fetchall()
for row in rs:
    print "id=%s, username=%s"%row

6. 更新数据库 insert/update/delete

注意！执行更新数据库的sql语句时如果出现异常需要回滚事务，没有异常提交事务！！

事务是指：访问和更新数据库的一个程序执行单元
事务的特点如下：

原子性-事务中包括的操作要么都做，要么都不做
一致性：事务必须使数据库从一致性状态变到另外一个一致性状态
隔离性：一个事务的执行不能被其它事务干扰
持久性：事务一旦提交，它对数据库的改变就是永久性的

日常数据库开发怎样使用事务

关闭自动commit:设置conn.autocommit(False)
正常结束事务时则conn.commit()
异常结束事务的话就调用conn.rollback()

#-*-coding:utf-8 -*-
import MySQLdb
conn = MySQLdb.Connect(host = '127.0.0.1',port=3306 ,user='root',passwd='root',db='imooc',charset='utf8')
cursor=conn.cursor()
#sql='select * from user'
sql_insert = "insert into user(userid,username) values(7,'name7')"
sql_upadate = "update user set username='name66' where userid=6"
sql_delete = "delete from user where userid<3"
sql_deleteerr= "delete from user where userd<3"

try:
    cursor.execute(sql_insert)
    print cursor.rowcount #影响行数,1
    cursor.execute(sql_upadate)
    print cursor.rowcount #1
    cursor.execute(sql_delete)
    #cursor.execute(sql_deleteerr)
    print cursor.rowcount #影响行数,2
    conn.commit()
except Exception as e:
    print e
    conn.rollback()

cursor.close()
conn.close()

7. 银行转账实例-账户A给账户B转账100元

步骤：
1、开始事务
2、检查账户A和账户B是否可用
3、检查账户A是否有100元
4、账户A-100，账户B+100
5、提交事务
6、2-4步骤中任何一步出现异常则回滚事务
先创建一个account表，添加两条数据（1，110）（2，10）

CREATE TABLE `account` (
`accountid` INT(11) DEFAULT NULL COMMENT '账户ID',
`money` INT (11) DEFAULT NULL COMMENT '余额'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

注意：ENGINE 不能设置为MyISAM ，因为不支持事务。
功能实现代码保存文件名 TransMoney.py，执行python TransMoney.py 1 2 100
实现代码如下:

#  -*-coding:utf-8 -*-
import MySQLdb
import sys

class TransferMoney(object):
    def __init__(self,conn):
        self.conn = conn

    def transfer(self,source_acctid,target_acctid,money):
        try:
            self.check_acc_available(source_acctid)
            #self.check_acc_available(target_acctid)
            self.has_enough_money(source_acctid,money)
            self.reduce_money(source_acctid, money)
            self.add_money(target_acctid,money)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e
    def check_acc_available(self,acctid):
        sql = "select * from account where accountid=%s"%acctid
        cursor=self.conn.cursor()
        try:
            cursor.execute(sql)
            print "check_acc_available:" + sql
            rs = cursor.fetchall()
            if len(rs) !=1:
                raise Exception("账号%s不存在"%acctid)
        finally:
            cursor.close()

    def has_enough_money(self,acctid,money):
        sql = "select * from account where accountid=%s and money>%s"%(acctid,money)
        cursor=self.conn.cursor()
        try:
            cursor.execute(sql)
            print "has_enough_money:" + sql
            rs = cursor.fetchall()
            if len(rs) != 1:
                raise Exception("账号金钱%s不足够"%acctid)
        finally:
            cursor.close()

    def reduce_money(self,acctid, money):
        sql = "update account set money=money-%s where accountid=%s"%(money,acctid)
        cursor=self.conn.cursor()
        try:
            cursor.execute(sql)
            print "reduce money:" +sql
            rs = cursor.fetchall()
            if cursor.rowcount != 1:
                raise Exception("账号%s减款失败"%acctid)
        finally:
            cursor.close()

    def add_money(self,acctid, money):
        sql = "update account set money=money+%s where accountid=%s"%(money,acctid)
        cursor=self.conn.cursor()
        try:
            cursor.execute(sql)
            print "add money:" +sql
            rs = cursor.fetchall()
            if cursor.rowcount != 1:
                raise Exception("账号%s加款失败"%acctid)
        finally:
            cursor.close()

if  __name__ == "__main__":
    source_acctid = sys.argv[1]
    target_acctid = sys.argv[2]
    money = sys.argv[3]
    conn = MySQLdb.Connect(host = '127.0.0.1',port=3306 ,user='root',passwd='root',db='test',charset='utf8')
    tr_money = TransferMoney(conn)
    try:
        tr_money.transfer(source_acctid,target_acctid,money)
    except Exception as e:
        print "出错:" + str(e)
    finally:
        conn.close()

最后总结

Python DB API

connection:主要用于建立数据库连接
cursor的作用为执行SQL、获取数据
开发数据库程序流程：
1、创建connection对象，获取cursor
2、使用cursor执行SQL
3、使用cursor获取数据、判断执行状态
4、提交事务或者回滚事务
5、关闭cursor、关闭connection