4.事务+利用pymysql更删改查+初始化

事务
要么全执行 要么不执行
事务的特性 — ACID
原子性 Atomicity
一致性 Consistency
隔离性 Isolation 不相互影响
持久性 Durability
事物的隔离级别
读未提交：允许读到未提交的数据
读已提交：只能读到已经提交的内容
可重复读（默认）：同一事物在相同的查询条件下两次查询得到的数据结果一样
可串行化：事务进行串行化，但是牺牲了并发性能

mysql隐式提交
set autocommit =0;//可以进行关闭
进行提交BEGIN COMMIT
回滚 ROLLBACK
回滚到保存点 ROLLBACK

mysql的增删改查

import pymysql
# 打开数据库连接
# mysql> create database testdb;
# mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'%' IDENTIFIED BY 'testpass';
#db = pymysql.connect("server1", "testuser", "testpass", "testdb")
#上面一行有问题，connect 只接收一个参数
config = {
          'host':'127.0.0.1',
          'port':3306,
          'user':'root',
          'password':'testpass',
          'database':'trade_ms',
          'charset':'utf8mb4',
          'cursorclass':pymysql.cursors.Cursor,
          }
db = pymysql.connect(**config)
################ 插入############################
try:
    # 使用 cursor() 方法创建一个游标对象 cursor
    with db.cursor() as cursor:#用with方法，不需要关闭了
        sql = '''INSERT INTO book(id,name) VALUES (%s,%s)'''#这里的s是占位符，和类型没关系
        value = (1002,"sssss")
        #{(1004,""),(dd , dd),}多个值
        # 使用 execute()  方法执行 SQL 查询
        cursor.execute(sql,value)#cursor.executemany(sql,values)
        result = cursor.fetchone()
    db.commit()
except Exception as e:
    print(f"fetch error {e}")
##########查询#################################
try:
    # %s是占位符
    with db.cursor() as cursor:
        sql = '''SELECT name FROM book'''
        cursor.execute(sql)
        books = cursor.fetchall() # fetchone()
        for book in books:
            print(book)
    db.commit()
except Exception as e:
    print(f"insert error {e}")
 ################更新########################
try:
    # %s是占位符
    with db.cursor() as cursor:
        sql = 'UPDATE book SET id = %s WHERE name = %s'
        value = (1003, "活着")
        cursor.execute(sql, value)
    db.commit()
except Exception as e:
    print(f"insert error {e}")
#######################删除########################
try:
    # %s是占位符
    with db.cursor() as cursor:
        sql = 'DELETE FROM book WHERE name = %s'
        value = ("活着")
        cursor.execute(sql, value)
    db.commit()
except Exception as e:
    print(f"insert error {e}")
finally:
    # 关闭数据库连接
    db.close()
print(f"Database version : {result} ")

配置文件

##如何自己写数据库配置文件
from configparser import ConfigParser
def read_db_config(filename='config.ini', section='mysql'):
    """ Read database configuration file and return a dictionary object
    :param filename: name of the configuration file
    :param section: section of database configuration
    :return: a dictionary of database parameters
    """
    # create parser and read ini configuration file
    parser = ConfigParser()
    parser.read(filename)
    # get section, default to mysql
    if parser.has_section(section):
        items = parser.items(section)
    else:
        raise Exception('{0} not found in the {1} file'.format(section, filename))
    # print(items)
    return dict(items)

if __name__ == "__main__":
    print(read_db_config())

调用方式

from dbconfig import read_db_config
dbserver = read_db_config()
db = pymysql.connect(**dbserver)

ini文件
[mysql]
host = server1
database = testdb
user = testuser
password = testpass