Skip to content

Commit cb00ef3

Browse files
coder-pigcoder-pig
committed
Push Code
1 parent f8e2f84 commit cb00ef3

File tree

2 files changed

+207
-0
lines changed

2 files changed

+207
-0
lines changed

‎Chapter 15/15_5.py

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,83 @@
1+
"""
2+
pymysql库使用代码示例
3+
"""
4+
5+
import pymysql
6+
7+
8+
# 连接数据库
9+
def db_connect():
10+
conn = pymysql.connect(host='localhost', user='root', password='Zpj12345', port=3306, db='test')
11+
return conn
12+
13+
14+
# 创建一个数据库表
15+
def create_table(c):
16+
c.execute(
17+
"CREATE TABLE IF Not Exists person(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL DEFAULT '',age INT,sex CHAR(2))")
18+
19+
20+
# 插入数据
21+
def insert_data(c, table, data_dict):
22+
try:
23+
keys = ', '.join(data_dict.keys())
24+
values = ', '.join(['%s'] * len(data_dict))
25+
sql = 'INSERT INTO {table} ({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
26+
print(sql)
27+
c.execute(sql, tuple(data_dict.values()))
28+
db.commit()
29+
except Exception as e:
30+
print(e)
31+
db.rollback()
32+
33+
34+
# 删除数据
35+
def delete_data(c, table, condition):
36+
try:
37+
sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
38+
c.execute(sql)
39+
db.commit()
40+
except Exception as e:
41+
print(e)
42+
db.rollback()
43+
44+
45+
# 修改数据
46+
def update_data(c, table, old, new):
47+
try:
48+
sql = 'UPDATE {table} SET {old} WHERE {new}'.format(table=table, old=old, new=new)
49+
c.execute(sql)
50+
db.commit()
51+
except Exception as e:
52+
print(e)
53+
db.rollback()
54+
55+
56+
# 查看数据
57+
def inquire_data(c, table, condition):
58+
try:
59+
sql = 'SELECT * FROM {table} WHERE {condition}'.format(table=table, condition=condition)
60+
c.execute(sql)
61+
print('共有 %d 行数据' % c.rowcount)
62+
row = c.fetchone()
63+
while row:
64+
print(row)
65+
row = c.fetchone()
66+
except Exception as e:
67+
print(e)
68+
69+
70+
if __name__ == '__main__':
71+
db = db_connect()
72+
cursor = db.cursor()
73+
create_table(cursor)
74+
# data = {
75+
# 'name': '大黄',
76+
# 'age': '17',
77+
# 'sex': '男',
78+
# }
79+
# insert_data(cursor, 'person', data)
80+
# delete_data(cursor, 'person', 'age < 10')
81+
# update_data(cursor, 'person', 'age = 10', "name = '小红'")
82+
inquire_data(cursor, 'person', 'age > 15')
83+
db.close()

‎Chapter 15/15_6.py

Lines changed: 124 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,124 @@
1+
"""
2+
爬取Gank.io API接口的数据到MySQL
3+
"""
4+
import requests as r
5+
from bs4 import BeautifulSoup
6+
import pymysql
7+
8+
# 接口地址
9+
search_api_base_url = 'https://gank.io/api/data/'
10+
11+
# 各种分类的表名：Android，iOS，休息视频，福利，拓展资源，前端，瞎推荐，App
12+
category_list = ["android", "ios", "video", "meizi", "other", "fed", "random", "app"]
13+
14+
# 图片表名
15+
pic_table_name = 'pics'
16+
17+
# 请求分类字段列表
18+
type_list = ["Android", "iOS", "休息视频", "福利", "拓展资源", "前端", "瞎推荐", "App"]
19+
20+
# 表字段名
21+
column_list = ('_id', 'createdAt', 'dsec', 'publishedAt', 'source', 'type', 'url', 'used', 'who')
22+
23+
# 图片表字段名
24+
pic_column_list = ('_id', 'url')
25+
26+
27+
# 创建数据库
28+
def create_db():
29+
conn = pymysql.connect(host='localhost', user='root', password='Zpj12345', port=3306)
30+
cursor = conn.cursor()
31+
cursor.execute("Create Database If Not Exists gank Character Set UTF8MB4")
32+
conn.close()
33+
conn = pymysql.connect(host='localhost', user='root', password='Zpj12345', port=3306, db='gank')
34+
return conn
35+
36+
37+
# 创建数据库表
38+
def init_tables(c, table):
39+
c.execute(
40+
("CREATE TABLE IF Not Exists {table}"
41+
"(_id CHAR(24) PRIMARY KEY,"
42+
"createdAt TEXT NOT NULL,"
43+
"dsec TEXT NOT NULL,"
44+
"publishedAt TEXT NOT NULL,"
45+
"source TEXT NOT NULL,"
46+
"type TEXT NOT NULL,"
47+
"url TEXT NOT NULL,"
48+
"used TEXT NOT NULL,"
49+
"who TEXT NOT NULL)").format(table=table))
50+
51+
52+
# 创建图表
53+
def init_pic_table(c, table):
54+
c.execute(
55+
("CREATE TABLE IF Not Exists {table} "
56+
"(id INT AUTO_INCREMENT PRIMARY KEY,"
57+
"_id CHAR(24),"
58+
"url TEXT NOT NULL)").format(table=table))
59+
60+
61+
# 把数据插入到数据库中
62+
def insert_data(c, table, column, data):
63+
try:
64+
keys = ', '.join(column)
65+
values = ', '.join(['%s'] * len(data))
66+
sql = 'INSERT INTO {table} ({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
67+
c.execute(sql, tuple(data))
68+
db.commit()
69+
except Exception as e:
70+
print(e)
71+
db.rollback()
72+
73+
74+
# 查询数据库表的方法
75+
def query_data(c, table):
76+
try:
77+
sql = 'SELECT * FROM {table}'.format(table=table)
78+
c.execute(sql)
79+
print('共有 %d 行数据' % c.rowcount)
80+
row = c.fetchone()
81+
while row:
82+
print(row)
83+
row = c.fetchone()
84+
except Exception as e:
85+
print(e)
86+
87+
88+
# 爬取接口数据的方法
89+
def fetch_data(c, pos):
90+
page_count = 1
91+
while True:
92+
resp = r.get(search_api_base_url + type_list[pos] + '/50/' + str(page_count))
93+
result_json = resp.json()
94+
print("抓取：", resp.url)
95+
if len(result_json['results']) > 0:
96+
for result in result_json['results']:
97+
data_list = [result['_id'],
98+
result['createdAt'],
99+
result['desc'],
100+
result['publishedAt'],
101+
result.get('source', ''),
102+
result['type'],
103+
result['url'],
104+
1 if result['used'] else 0,
105+
result.get('who', '') if result.get('who', '') is not None else '']
106+
insert_data(c, category_list[pos], column_list, data_list)
107+
if 'images' in result:
108+
for image in result['images']:
109+
insert_data(c, pic_table_name, pic_column_list, [result['_id'], image])
110+
page_count += 1
111+
else:
112+
break
113+
114+
115+
if __name__ == '__main__':
116+
db = create_db()
117+
cursor = db.cursor()
118+
# for category in category_list:
119+
# init_tables(cursor, category)
120+
# init_pic_table(cursor, pic_table_name)
121+
# for i in range(0, len(category_list)):
122+
# fetch_data(cursor, i)
123+
query_data(cursor, 'Android')
124+
cursor.close()

0 commit comments

Comments
 (0)