php程序员学习python3学习第二十一天---mysql

1,pymysql 模块操作数据库

# -*- coding: utf-8 -*-
import pymysql

#创建连接
conn = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = 'root', db = 'ad')

#创建游标
cursor = conn.cursor()
#默认情况取出的数据为元组形式的，若改为字典形式的可以进行设置,方便取出的数据进行操作
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

#执行sql，并返回影响行数 1
# effect_row = cursor.execute("update ad_manger set name = 'test'")
# effect_row = cursor.execute("update ad_manger set name = 'haha' where id > %s", (2,))

#批量插入数据
# effect_row = cursor.executemany("insert into test (name) values (%s)",[(2),(3)])

#查找不执行commit
# cursor.commit()

# 执行查询操作 fetchall 获取所有数据 fetchone 获取一条数据
# effect_row = cursor.execute("select * from test")
# result = cursor.fetchall()
# cursor.execute("select * from test where id > %s", (2,))
# res2 = cursor.fetchall()
effect_one = cursor.execute("select * from test where id > %s", (2,))
res5 = cursor.fetchmany(1)
print(res5)
res3 = cursor.fetchone()
print(res3)
res4 = cursor.fetchone()
# print(result)
# print(res2)
print(res4)

#移动指针，负数为向上移动 relative 相对当前位置， absolite 绝对位置
cursor.scroll(-2, mode = 'relative')

effect_row = cursor.executemany("insert into test (name) values (%s)",[(2),(3)])

# 获取新增数据的自增id
print(cursor.lastrowid)

cursor.close()
# print(effect_row)

2，mysql 存储过程

# 生成存储过程
# drop procedure if EXISTS proc_p1;
# create procedure proc_p1()
# begin
#   select * from test;
# end
# 调用存储过程
# call proc_p1()
# 删除存储过程
# drop procedure proc_p1;


'''
定义以$$结束
delimiter $$
drop procedure if EXISTS proc_p1 $$
create procedure proc_p1(
    in i1 int
)
begin
    declare d1 int;
    declare d2 int default 3;
    set d1 = i1 + d2
end$$
delimiter ;
'''

3，mysql 开启计时  set profiling = 1   show profiles

4,