python数据库编程

连接数据库

创建连接时，就指定:
pymysql.connect(host=’172.25.254.178”, user=’cooffee”,
password=’cooffee”, charset=’utf8’, autocommit=True)
host(要连接的主机ip), user（用户）, passwd（用户密码）, charset（默认编码格式），autocommit（对数据库操作是否自动提交）
eg：

import pymysql
#连接数据库
conn = pymysql.connect(host='172.25.254.178', user='cooffee',
                password='cooffee', charset='utf8', autocommit=True)
 #创建一个游标， 用来给数据库发送sql语句的
cur = conn.cursor()
# 选择需要操作的数据库
conn.select_db('cooffee')
try:
    create_sql = 'create table myuser (name varchar(30), age int );'
    cur.execute(create_sql)   #执行的sql
except Exception as e:
    print('fail:',e)
else:
    print('Success')
# 4. 先关闭游标
cur.close()

# 5. 关闭数据库连接
conn.close()

数据库的增删改查

单条记录：
 实现思路：
 sqli='数据库的增删改查语句'
 cur.execute(isqli)
批量实现用：
cur.executemany(insert_sqli, users) #users为一个列表

记录的增加

import random
import pymysql
conn = pymysql.connect(host='172.25.254.178', user='cooffee',
                password='cooffee', charset='utf8', autocommit=True)
cur = conn.cursor()
conn.select_db('cooffee')
try:
    insert_sqli1 = 'insert into myuser VALUES ("user1", 32);'
    insert_sqli2 = 'insert into myuser VALUES ("user2", 18);'
    cur.execute(insert_sqli1)
    cur.execute(insert_sqli2)
    #批量对数据实现操作
    users=[('user'+str(i),random.choice(range(18,40))) for i in range(10)]
    inser
t_sqli = 'insert into myuser VALUES (%s, %s);'
    cur.executemany(insert_sqli, users)
    select_sqli = 'select * from myuser;'
    res=  cur.execute(select_sqli)
    print("查看语句的返回结果:", res)
except Exception as e:
    print('fail:',e)
else:
    print('Success')
cur.close()
conn.close()

查看表中的数据及更改与删除

import pymysql
conn = pymysql.connect(host='172.25.254.178', user='cooffee',
                password='cooffee', charset='utf8', autocommit=True)
cur = conn.cursor()
conn.select_db('cooffee')
try:
    delete_sqli = 'delete from myuser where name="user3";'
    cur.execute(delete_sqli)
    update_sqli = 'update myuser set age="1" where name="user2";'
    cur.execute(update_sqli)
    select_sqli = 'select * from myuser;'
    res = cur.execute(select_sqli)
    print("查看语句的返回结果:", res)
    # cur.fetchone类似与文件的操作f.readline, 每次只读取一条记录;
    print("查找一条记录:", cur.fetchone())
    print("查找一条记录:", cur.fetchone())
    #cur.fetchmany， 类似于f.readlines, 返回的是一个元组；
    print("查找5条记录:",cur.fetchmany(5))
    #cur.fetchall返回的是一个元组；
    print("1查找所有记录", cur.fetchall())
    #移动游标的位置, 到记录的最开始
    cur.scroll(0, mode='absolute')
    print("查找2所有记录", cur.fetchall())
    cur.scroll(-3, mode='relative')
    print("查找2所有记录", cur.fetchall())
except Exception as e:
    print('fail:',e)
else:
    print('Success')
cur.close()
conn.close()

获取表的字段名和信息

import pymysql
# 安全管理器： with

conn = pymysql.connect(host='172.25.254.178', user='cooffee',
                password='cooffee', charset='utf8', autocommit=True,
                db='cooffee')
with conn:
    print("is_open", conn.open)
    # 2. 创建一个游标， 用来给数据库发送sql语句的；
    cur = conn.cursor()
    # 显示有多少行记录？
    res = cur.execute('select * from myuser;')
    # 显示每列的详细信息
        desc = cur.description
        print("表的描述:",desc )
        # 获取表头
        print("表头", ",".join([item[0] for item in desc]))
        # 4. 先关闭游标
        cur.close()
    conn.close()

银行转账操作

import pymysql
def create_data():
    # 1. 连接数据库， host, user, passwd, charset
    conn = pymysql.connect(host='172.25.254.178', user='cooffee',
                           password='cooffee', charset='utf8',
                           autocommit=True, db='cooffee')
    # 2. 创建一个游标， 用来给数据库发送sql语句的；
    cur = conn.cursor()

    # 3. 创建一个表
    try:
        create_sqli = 'create table bankData( id int PRIMARY KEY, ' \
                      'name varchar(10), money FLOAT);'
        cur.execute(create_sqli)
    except Exception as e:
        print("Error: 表已经创建", e)
    else:
        print("表创建成功")
    #4. 创建数据
    try:
        users = [(610001, '张三', 1000), (610002, '李四', 1000),(610003, '粉条', 1000)]
        insert_sqli = 'insert into bankData VALUES (%s, %s, %s);'
        cur.executemany(insert_sqli, users)
    except Exception as e:
        print('Error:', e)
    else:
        print("初始化数据成功!")

    # 4. 先关闭游标
    cur.close()

    # 5. 关闭数据库连接
    conn.close()
class TransferMoney(object):
    def __init__(self, conn):
        self.conn = conn
        self.cursor = conn.cursor()
    def transfer(self, source_accid, target_accid, money):
        """
        转账方法:
            # 1. source_accid帐号是否存在；
            # 2. target_accid帐号是否存在；
            # 3. 是否有足够的钱
            # 4. source_accid扣钱
            # 5. target_acci加钱
            # 6. 提交对数据库的操作
        :param source_accid: 源帐号id
        :param target_accid: 目标帐号id
        :param money: 转账金额
        :return: bool
        """
        # 判断帐号是否存在
        self.check_account_avaiable(source_accid)
        self.check_account_avaiable(target_accid)

        # 是否有足够的钱
        self.has_enough_money(source_accid, money)


        try:
            # source_accid扣钱
            self.reduce_money(source_accid, money)
            #  target_acci加钱
            self.add_money(target_accid, money)
            self.conn.commit()
        except Exception as e:
            # ********************撤销对于数据库的更改操作, 回滚******************
            self.conn.rollback()
        else:
            print("%s给%s转账%s成功" %(source_accid, target_accid, money))
    def check_account_avaiable(self, accid):
        """判断帐号是否存在， 传递参数为帐号id"""
        select_sqli = 'select * from bankData where id=%s' %(accid)
        print("execute sql:", select_sqli)
        res = self.cursor.execute(select_sqli)
        # 判断是否能找到帐号为accid的记录;
        if res == 1:
            return  True
        else:
            raise  Exception("帐号%s不存在" %(accid))
    def has_enough_money(self, accid, money):
        """是否有足够的钱"""
        select_sqli = 'select money from bankData where id=%s' %(accid)
        print('execute sql:', select_sqli)
        self.cursor.execute(select_sqli)
        # 获取查询到的金钱数额
        acc_money = self.cursor.fetchone()[0]
        print(acc_money, type(acc_money))
        # 判断
        if acc_money >= money:
            return  True
        else:
            raise  Exception("账户%s没有足够的金额， 当前余额为%s" %(accid, acc_money))
    def reduce_money(self, accid, money):
        # 对于accid减少的金额为money
        try:
            update_sqli = 'update bankData set money=money-%s where id="%s"' %(money, accid)
            print("redcue_money sql:", update_sqli)
            self.cursor.execute(update_sqli)
        except Exception as e:
            print('Error:',e)
    def add_money(self, accid, money):
        # 对于accid减少的金额为money
        try:
            update_sqli = 'update bankData set money=money+%s where id="%s"' %(money, accid)
            print("add_money sql:", update_sqli)
            self.cursor.execute(update_sqli)
        except Exception as e:
            print('Error:',e)
    def __del__(self):
        # 当删除对象时， 自动执行， 关闭游标；
        self.cursor.close()
        conn.close()

if __name__ == '__main__':
    create_data()
    conn = pymysql.connect(host='172.25.254.178', user='cooffee',
                               password='cooffee', charset='utf8',
                               db='cooffee')

    trans = TransferMoney(conn)
    trans.transfer('610003', '610002', 100)