Python3-2020-测试开发-25- 操作数据库（MySQL）

1. 导入模块

import pymysql

2. 创建数据库连接

# 创建于数据库的连接
        db = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            db='mybatis',
            port=3306,
            charset='utf8'
        )

3. 创建游标

#创建游标
        cur = db.cursor()

4. sql语句

#sql
        sql = "select * from orders;"

5. 执行sql语句

cur.execute(sql)

5.1 执行插入语句

 sql ="insert into orders(user_id,number,createtime) values (%s,%s,%s);"

        # 插入单条数据
        cur.execute(sql,values)

        # 插入多条数据
        cur.executemany(sql,values)

 

6. 获取结果

res = cur.fetchall()
        print(res)

具体例子：

# coding=utf8


import pymysql

sql2 = ""

def select_db():
    try:

        # 创建于数据库的连接
        db = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            db='mybatis',
            port=3306,
            charset='utf8'
        )
        print("连接成功")
        #创建游标
        cur = db.cursor()

        #sql
        sql = "select * from orders;"

        cur.execute(sql)

        res = cur.fetchall()
        print(res)

        return res
    except Exception as e:

        print(e)

    finally:
        cur.close()
        db.close()




def insert_db(values):

    try:

        # 创建于数据库的连接
        db = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            db='mybatis',
            port=3306,
            charset='utf8'
        )
        print("连接成功")
        #创建游标
        cur = db.cursor()

        sql ="insert into orders(user_id,number,createtime) values (%s,%s,%s);"

        # 插入单条数据
        #cur.execute(sql,values)

        # 插入多条数据
        cur.executemany(sql,values)



        # 提交事务
        db.commit()

    except Exception as e:

        print(e)
        db.rollback()

    finally:
        db.close()
        cur.close()







# insert_db([(10,'11111','2015-02-04 13:22:35'),(10,'11111','2015-02-04 13:22:35'),(10,'11111','2015-02-04 13:22:35'),(10,'11111','2015-02-04 13:22:35'),])
result = select_db()

for res in result:

   id = res[0]
   u_id = res[1]
   num = res[2]
   c_time = res[3]
   note = res[4]
   print("id:",id,"u_id:",u_id,"num:",num,"c_time:",c_time,"note:",note)