【数据库】pymysql编程

1.pymysql执行流程及基本操作：

基本操作语句：

代码实现：

import pymysql

#1.创建链接
conn = pymysql.connect(host='localhost', user='root', password="123456",
                 database='blog', port=3306, charset='utf8')

#2.创建游标
cur = conn.cursor()

#3.执行sql语句
insert_sql = 'insert into studentinfo values("03","user3","m",14,"bj","102")';
cur.execute(insert_sql) #执行这一插入语句
conn.commit() #提交到数据库中（也可以在很多据sql语句执行完毕后统一提交）
print('插入语句成功')

#4.关闭游标

cur.close()
#5.关闭连接
conn.close()

效果：
执行插入语句之前的数据库内容：

执行插入语句之后的数据库内容：

其中，conn.commit() 这一语句是我们手动提交内容到数据库，在创建连接时，可设置参数自动提交，默认为False，我们改成True。

import pymysql

#1.创建链接
conn = pymysql.connect(host='localhost', user='root',
                       password="123456",database='blog',
                       port=3306, charset='utf8',autocommit=True)

#2.创建游标
cur = conn.cursor()

#3.执行sql语句
insert_sql = 'insert into studentinfo values("04","user4","m",14,"bj","102")';
cur.execute(insert_sql) #执行这一插入语句
insert_sql = 'insert into studentinfo values("06","user6","f",15,"bj","102")';
cur.execute(insert_sql) #执行这一插入语句

print('插入语句成功')

#4.关闭游标

cur.close()
#5.关闭连接
conn.close()

执行效果：

2.用with语句创建连接

上边的代码实现我们可以看到，实现数据库的连接最后进行了关闭。如果执行过程中出现了异常，可能就无法关闭连接了。这时我们想到了使用with语句，但前提with语句操作的对象必须是上下文管理器。
所谓上下文管理器之前介绍过，就是拥有 __ enter__() 和 __ exit__() 方法的对象。

import pymysql

# 使用with语句是， pymysql.connect返回的是数据库游标。(具体的内容查看源代码)
原理：
首先，该 enter 和 exit 函数是 connect 类中定义的，也就是说其生成对象为上下文管理器，可配合 with 使用
其次，分析  enter 和 exit 函数得知。当“进入”文件管理器时，返回 cursor 游标对象；当“退出”文件管理器时，会自动进行事务提交
最后，利用 with 实现内存的自动管理，精简代码
with pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8') as cur:
    # 3). 执行sql语句(增删改)
    insert_sql = 'insert into users(username) values ("user7");'
    cur.execute(insert_sql)
    print("插入数据成功.......")

"""
*********************************源代码*********************************
class Connection(object):
  def __enter__(self):
        Context manager that returns a Cursor
        warnings.warn(
            "Context manager API of Connection object is deprecated; Use conn.begin()",
            DeprecationWarning)
        return self.cursor()

    def __exit__(self, exc, value, traceback):
        On successful exit, commit. On exception, rollback(回滚)
        if exc:
            self.rollback()
        else:
            self.commit()
"""


import pymysql

#1.创建链接
conn = pymysql.connect(host='localhost', user='root',
                       password="123456",database='blog',
                       port=3306, charset='utf8',autocommit=True)

#2.创建游标
with conn as cur:
    #3.执行sql语句
    insert_sql = 'insert into studentinfo values("07","user7","m",14,"bj","102")';
    cur.execute(insert_sql) #执行这一插入语句
    insert_sql = 'insert into studentinfo values("09","user9","f",15,"bj","102")';
    cur.execute(insert_sql) #执行这一插入语句

print('插入语句成功')

3. 批量操作

• execute: 执行一条sql语句。 ##返回的是符合条件的记录数
• executemany: 执行多条sql语句。

executemany的函数定义如下：
def executemany(self, query, args): ##对一个查询运行多条数据
# type: (str, list) -> int

其中，参数解释如下：
query: query to execute on server
args: Sequence of sequences or mappings. It is used as parameter.
return: Number of rows affected, if any.

import pymysql
users = [(i,'user'+str(i)) for i in range(10,20)]
#1.创建链接
conn = pymysql.connect(host='localhost', user='root',
                       password="123456",database='blog',
                       port=3306, charset='utf8',autocommit=True)

#2.创建游标
cur = conn.cursor()

#3.执行sql语句
insert_sql = 'insert into studentinfo values("%s","%s","m",14,"bj","102")';
cur.executemany(insert_sql,users)
print('插入语句成功')

#4.关闭游标
cur.close()
#5.关闭连接
conn.close()

批量插入：

4. 查询操作

fetchone()：该方法获取下一个查询结果集，结果集是一个对象
fetchall()：接收全部的返回结果行
fetchmany()：传入参数想要获取的行数count
rowcount：这是一个只读属性，并返回执行execute（）方法影响的行数

import pymysql

#1.创建链接
conn = pymysql.connect(host='localhost', user='root',
                       password="123456",database='blog',
                       port=3306, charset='utf8',autocommit=True)

#2.创建游标
cur = conn.cursor()

#3.执行sql语句
query_sql = "select * from studentinfo where classno=102;"
result = cur.execute(query_sql)
print('execute方法影响的行数：',result)
print(cur.fetchone())
print(cur.fetchmany(3))
print(cur.fetchall())

#4.关闭游标
cur.close()
#5.关闭连接
conn.close()

这里可以结合prettytable模块中的PrettyTable类，友好的展示出查询出的结果

import pymysql

#1.创建链接
conn = pymysql.connect(host='localhost', user='root',
                       password="123456",database='blog',
                       port=3306, charset='utf8',autocommit=True)

#2.创建游标
cur = conn.cursor()

#3.执行sql语句
query_sql = "select * from studentinfo where classno=102;"
result = cur.execute(query_sql)
print('execute方法影响的行数：',result)

# 3-1. 获取查询的数据
#print(cursor.fetchone())
# 将游标移动到记录的最开始位置
#cursor.scroll(0, mode='absolute')
#print(cursor.fetchmany(2))
# 将游标移动到当前记录的下一个记录的位置;
#cursor.scroll(1)
print(cursor.fetchall())
# print(cur.fetchone())
# print(cur.fetchmany(3))
user_info = cur.fetchall()

#4.关闭游标
cur.close()
#5.关闭连接
conn.close()

#以友好的界面展示
from prettytable import PrettyTable
pt = PrettyTable(field_names=['stu','sname','sex','age','address','classno'])
for user in user_info:
    pt.add_row(user)
print(pt)

5. 项目案例：银行转账系统实现

我们通过一个实际的项目案例，对上面的PyMySQL操作进行一个总体应用。
项目描述：实现一个银行转账系统，使其连接数据库，当执行转账操作时，先判断两个账户是否存在，再判断要转账的账户金额是否足够，若满足上述条件，则对转账账户的金额执行减的操作，并且要保证同时对收帐账户的金额执行加操作。
下面是Python代码：

import pymysql

class TransferMoney(object):
    # 构造方法
    def __init__(self, conn):
        self.conn = conn
        self.cur = conn.cursor()

    def transfer(self, source_id, target_id, money):
        # 1). 判断两个银行卡号是否存在?
        # 2). 判断source_id是否有足够的钱？
        # 3). source_id扣钱
        # 4). target_id加钱
        if not self.check_account_avaialbe(source_id):
            raise  Exception("账户不存在")
        if not self.check_account_avaialbe(target_id):
            raise  Exception("账户不存在")

        if self.has_enough_money(source_id, money):
            try:
                self.reduce_money(source_id, money)
                self.add_money(target_id, money)
            except Exception as e:
                print("转账失败:", e)
                self.conn.rollback()
            else:
                self.conn.commit()
                print("%s给%s转账%s金额成功" % (source_id, target_id, money))
        else:
            print("没有足够的金额")

    def check_account_avaialbe(self, acc_id):
        """判断帐号是否存在， 传递的参数是银行卡号的id"""
        select_sqli = "select * from bank where cardid=%s;" % (acc_id)
        print("execute sql:", select_sqli)
        res_count = self.cur.execute(select_sqli)
        if res_count == 1:
            return True
        else:
            # raise  Exception("账户%s不存在" %(acc_id))
            return False

    def has_enough_money(self, acc_id, money):
        """判断acc_id账户上金额> money"""
        # 查找acc_id存储金额?
        select_sqli = "select money from bank where cardid=%s;" % (acc_id)
        print("execute sql:", select_sqli)
        self.cur.execute(select_sqli)  # ((1, 500), )
        # 获取查询到的金额钱数;
        acc_money = self.cur.fetchone()[0]
        # 判断
        if acc_money >= money:
            return True
        else:
            return False

    def add_money(self, acc_id, money):
        update_sqli = "update bank set money=money+%d  where cardid=%s" % (money, acc_id)
        print("add money:", update_sqli)
        self.cur.execute(update_sqli)

    def reduce_money(self, acc_id, money):
        update_sqli = "update bank set money=money-%d  where cardid=%s" % (money, acc_id)
        print("reduce money:", update_sqli)
        self.cur.execute(update_sqli)

    # 析构方法
    def __del__(self):
        self.cur.close()
        self.conn.close()

测试代码如下：

if __name__ == '__main__':
    #  连接数据库，
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        db='pymysql',
        charset='utf8',
        autocommit=True,    # 如果插入数据，， 是否自动提交? 和conn.commit()功能一致。
    )
    
    trans = TransferMoney(conn)
    trans.transfer('130001', '130002', 300)