pymysql

第一步: 根据项目的功能来设计相应的 数据库 & 表结构（不会经常变动，在项目设计之初就确定好了.
第二步: 操作表结构中的数据，已达到实现业务逻辑的目的。

列名	类型	备注
id	int	不为空 & 自增 & 主键
name	varchar(32)	不为空
password	varchar(64)	不为空
gender	char(1)	不为空，支持：男、女
email	varchar(64)	可以为空
amount	decimal(10,2)	不为空 & 默认值为 0
ctime	datetime	新增时的时间 提示：可基于datetime模块实现

create database day25db default charset utf8 collate utf8_general_ci;

create table admin(
      id int not null auto_increment primary key,
    name varchar(32) not null,
    password varchar(64) not null,
    gender char(1) not null,
    email varchar(64) null,
    amount decimal(10,2) not null default 0,
    ctime datetime
)default charset=utf8;

---

数据库|数据表的操作

import pymysql

# 连接MySQL（底层是socket连接）
# 因为是对数据库操作 所以这里没有指定是哪个数据库 后面会创建数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
cursor = conn.cursor() # 游标

# 1.[增]
cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
conn.commit()

# [删]
cursor.execute("drop database db3")
conn.commit()

# [查] 
cursor.execute("show databases")
result = cursor.fetchall()
print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

# 2.进入数据库查看表
cursor.execute("use db4")
cursor.execute("show tables")
result = cursor.fetchall()
print(result) # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',),....


# 3.进入数据库创建表
cursor.execute("use db4")
sql = """
create table L4(
    id int not null primary key auto_increment,
    title varchar(128),
    content text,
    ctime datetime
)default charset=utf8;
"""
cursor.execute(sql)
conn.commit()

# 4.查看数据库中的表
"""
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
"""

# 5.其他 drop table... 略过
cursor.close()
conn.close()

---

表记录的操作 增删改查

pymysql.cursors.DictCursor

conn.commit()

cursor.fetchall()
cursor.fetchonel()
cursor.fetchmany()

cursor.scroll()

import datetime
import pymysql

# 连接指定数据
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="day25db")
cursor = conn.cursor(pymysql.cursors.DictCursor)
'''
((1,'xxx'),(2,'yyy')) 
[{'id':1,'name':'xxx'},{'id':2,'name':'yyy'}]
'''

# [增]
# 单独增加数据 NOW()
'''
insert into admin(name,password,gender,email,amount,ctime) values("武沛齐","123123","男","xxx@live.com",19991.12,NOW());
'''
sql = 'insert into admin(name,password,gender,email,amount,ctime) values(%s,%s,%s,%s,%s,NOW());'
cursor.execute(sql, ["tony", "123123", "男", "xxx@live.com", 19991.12])
conn.commit()

# 单独增加数据 datetime.datetime.now()
sql = 'insert into admin(name,password,gender,email,amount,ctime) values(%s,%s,%s,%s,%s,%s);'
cursor.execute(sql, ["tony", "123123", "男", "xxx@live.com", 19991.12, datetime.datetime.now()])
print(cursor.lastrowid) # #在插入语句后查看 获取插入的最后一条数据的自增ID
conn.commit()

# 多条批量增加
'''
insert into admin(name,password,gender,email,amount,ctime) values("tony","123123123","女","xxxxxxxx@live.com",200.12,NOW()), ("kelly","8888","女","kelly@live.com",991.12,NOW());
'''
sql = 'insert into admin(name,password,gender,email,amount,ctime) values(%s,%s,%s,%s,%s,%s);'
cursor.executemany(sql, [
    ["tony", "123123123", "女", "xxxxxxxx@live.com", 200.12, datetime.datetime.now()],
    ["kelly", "8888", "女", "kelly@live.com", 991.12, datetime.datetime.now()],
])
conn.commit()

# [删]
# 删除性别为男的所有数据。
sql = 'delete from admin where gender="男";'
cursor.execute(sql)
conn.commit()

# [改]
# 将 id>3 的所有人的性别改为 男。
sql = 'update admin set gender="男" where id >3;'
cursor.execute(sql)
conn.commit()

# 让每个人的余额在自己原有的基础上 +1000 
sql = 'update admin set amount=amount+1000;'
cursor.execute(sql)
conn.commit()

# [查]
# 查询余额amount>1000的所有用户。
sql = 'select * from admin where amount >1000;'
# 打印这条语句 返回的是执行结果的条数.
# 执行完这条语句 底层只是生成了一个迭代器 要数据的时候才会生成的 惰性计算减缓数据库压力
# 查完就没啦！！
cursor.execute(sql)  
result = cursor.fetchall()
# fetchone() 取一条
# fetchmany(3) 从当前位置往后取3条
print(result)

'''
# 可以控制游标的移动
# cursor.scroll(3,mode='absolute') # 相对绝对位置移动 参照物:开头位置
# cursor.scroll(3,mode='relative') # 相对当前位置移动
'''

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

---

注意sql注入的问题！

# 原来是我们对sql进行字符串拼接
# sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)
# print(sql)
# res=cursor.execute(sql)

xxx' or 1 = 1 -- hahahah
where name = 'xxx' or 1 = 1 -- hahahah' and password = ''

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
#！注意%s需要去掉引号，因为pymysql会自动为我们加上
sql="select * from userinfo where name=%s and password=%s" 
res=cursor.execute(sql,[user,pwd]) 

---

案例

编写脚本实现将 csv文件的内容录入到 MySQL 数据库中。
要求: 自己创建一个自增列作为主键（不要用csv文件中的第一列作为主键）。

1715046,河北大学取消考试学生紧急离校,老师:回不了家的到老师家过年,https://video.pearvideo.com/mp4/adshort/20210105/cont-1715046-15562045_adpkg-ad_hd.mp4
1715020,重庆两口子因琐事吵架，男子怒将自家车推进涪江,https://video.pearvideo.com/mp4/adshort/20210105/cont-1715020-15561817_adpkg-ad_hd.mp4

create table news(
 id int not null auto_increment primary key,
  nid int not null,
  title varchar(128) not null,
  url char(128) not null
)default charset=utf8;

import datetime
import pymysql

def insert_db(*args):
    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="day25db")
    cursor = conn.cursor()

    # 单独增加数据
    sql = 'insert into news(nid,title,url) values(%s,%s,%s);'
    cursor.execute(sql, args)
    conn.commit()

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

def run():
    with open('data.csv', mode='rt', encoding='utf-8') as file_object:
        for line in file_object:
            nid, others = line.strip().split(",", maxsplit=1)
            title, url = others.rsplit(',', maxsplit=1)
            insert_db(nid, title, url)


if __name__ == '__main__':
    run()

import datetime
import pymysql

def insert_db(multi_row_list):
    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="day25db")
    cursor = conn.cursor()

    # 批量增加数据
    sql = 'insert into news(nid,title,url) values(%s,%s,%s);'
    cursor.executemany(sql, multi_row_list)
    conn.commit()

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

def run():
    with open('data.csv', mode='rt', encoding='utf-8') as file_object:
        # 批量在数据库中插入，每次最多插入10条
        part_list = []
        for line in file_object:
            nid, others = line.strip().split(",", maxsplit=1)
            title, url = others.rsplit(',', maxsplit=1)
            part_list.append([nid, title, url])
            if len(part_list) == 10:
                insert_db(part_list)
                part_list.clear()
        if part_list:
            insert_db(part_list)

if __name__ == '__main__':
    run()