Python操作数据库

Python操作数据库

一、PythonDB的API介绍

背景：没有Python DB API之前，接口程序混乱，因为不同的数据库有不同的接口，所以一旦我们的程序在维护的过程中出现修改数据库类型的情况，我们有需要修改数据库接口程序，这样就加大了维护成本。

在使用了PythonDB的API以后，我们就可以通过这个统一的接口实现对不同数据库的访问；

PythonDB API包括的内容

1、数据库连接对象：Connection，相当于与数据库服务器交互数据的高速公路；

2、数据库交互对象：Cursor，相当于高速公路上运输数据的货车；

3、数据库异常类：exceptions，用于解决数据运输过程中的异常错误；

PythonDB API访问数据库流程

创建connection连接，获取cursor对象，执行语句操作并访问数据，关闭cursor对象，关闭connection对象；

关闭connection的目的是避免浪费资源；

二、Python操作MySQL数据

Connection对象

建立了Python客户端与数据库的网络连接

创建方法：MySQLdb.Connect（参数）

其中，如果需要访问数据库中的中文数据，那么charset的连接编码设置为UTF-8；

Connection对象支持的方法有：

创建Connection对象代码：

import pymysql

conn = pymysql.connect(
                        host = '127.0.0.1',   #MySQL服务器地址
                        port = 3306,          #MySQL服务器端口号
                        user = 'root',        #用户名
                        passwd = '970718',    #密码
                        db = 'pythondb',      #数据库名称
                        charset = 'utf8'      #连接编码
                      )
cursor = conn.cursor()

print(conn)
print(cursor)
#释放链接资源
cursor.close()
conn.close()

Cursor对象

数据库游标对象cursor：用于执行查询和获取结果；

cursor对象支持的方法：

其中，execute方法用来执行SQL、将结果从数据库获取到客户端：

首先由execute方法将SQL语句传送到MySQL服务器，然后由MySQL服务器执行SQL语句，并将最后的结果传回客户端的本地缓冲区中。

然后由fetch*（）等方法访问存储到缓冲区的数据，访问的规则如下，初始游标rownumber=0，指向返回数据的第一条；

如果使用fetchone（）则rownumber+1=1，同时返回第二行数据；

如果使用fetchmany（size）则rownumbe+size=size，同时返回size行数的数据；

如果使用fetchall（）则rownumber=总行数，同时返回全部的数据；

三、Python增删改查数据库

select数据

查询代码如下：

import pymysql

conn = pymysql.connect(
                        host = '127.0.0.1',   #MySQL服务器地址
                        port = 3306,          #MySQL服务器端口号
                        user = 'root',        #用户名
                        passwd = '970718',    #密码
                        db = 'pythondb',      #数据库名称
                        charset = 'utf8'      #连接编码
                      )
cursor = conn.cursor()

sql = 'select * from user'
cursor.execute(sql)
print(cursor.rowcount)

rs = cursor.fetchone()
print(rs)

rs = cursor.fetchmany(3)
print(rs)

rs = cursor.fetchall()
print(rs)

print(conn)
print(cursor)
#释放链接资源
cursor.close()
conn.close()

运行结果如图：

insert-delet-update数据库

注意，在执行增加删除和修改操作时，需要判断是否出现异常，于是存在事务的提交和事务的回滚。

测试代码如下：

import pymysql

conn = pymysql.connect(
                        host = '127.0.0.1',   #MySQL服务器地址
                        port = 3306,          #MySQL服务器端口号
                        user = 'root',        #用户名
                        passwd = '970718',    #密码
                        db = 'pythondb',      #数据库名称
                        charset = 'utf8'      #连接编码
                      )
cursor = conn.cursor()
#关闭自动提交事务
conn.autocommit(False)

sql_insert = "insert into user(userid,username) values (10,'name10')"
sql_update = "update user set username='name91' where userid='9'"
sql_delete = "delete from user where  userid<3"
#测试执行增加数据
cursor.execute(sql_insert)
print(cursor.rowcount)
#测试执行修改数据
cursor.execute(sql_update)
print(cursor.rowcount)
#测试执行删除数据
cursor.execute(sql_delete)
print(cursor.rowcount)

#提交事务
conn.commit()


#释放链接资源
cursor.close()
conn.close()

执行结果如下：

import pymysql

conn = pymysql.connect(
                        host = '127.0.0.1',   #MySQL服务器地址
                        port = 3306,          #MySQL服务器端口号
                        user = 'root',        #用户名
                        passwd = '970718',    #密码
                        db = 'pythondb',      #数据库名称
                        charset = 'utf8'      #连接编码
                      )
cursor = conn.cursor()
#关闭自动提交事务
conn.autocommit(False)

sql_insert = "insert into user(userid,username) values (10,'name10')"
sql_update = "update user set username='name91' where userid='9'"
sql_delete = "delete from user where  userid<3"
try:
    #测试执行增加数据
    cursor.execute(sql_insert)
    print(cursor.rowcount)
    #测试执行修改数据
    cursor.execute(sql_update)
    print(cursor.rowcount)
    #测试执行删除数据
    cursor.execute(sql_delete)
    print(cursor.rowcount)

    # 提交事务
    conn.commit()
except Exception as e:
    print(e)
    #事务回滚
    conn.rollback()




#释放链接资源
cursor.close()
conn.close()

添加了事务回滚功能的代码如下：