"""
需求:
读取text.csv文件中的内容,写入数据库中
""""
建表:
mysql> create table message(
-> id int not null primary key auto_increment, -- 将id设置为主键+自增+不为空
-> video_id int not null,
-> title varchar(128) not null,
-> url varchar(128) not null
-> )default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
import csv
import pymysql
## 打开csv文件获取数据
f = csv.reader(open('text.csv', mode='r'))
## 链接数据库
conn = pymysql.connect(user='root', passwd='123', database='db', port=3306, charset='utf8', host='127.0.0.1')
curso = conn.cursor()
## 循环写入数据
for i in f:
video, title, url = i
sql = "insert into message(video_id,title,url) values({},'{}','{}');".format(video, title, url)
curso.execute(sql)
conn.commit()
## 查询数据库内容
curso.execute("select * from message;")
res1 = curso.fetchone()
res2 = curso.fetchall()
print(res1) ## 读取一条
print(res2) ## 读取所有
## 断开数据库链接
curso.close()
conn.close()