python安全工具开发笔记（五）——python数据库编程

一、Python DB API

在没有Python DB API之前：

有Python DB API之后：

Python DB API包含内容

Python DB API访问数据库流程

二、Python Mysql开发环境

三、Python 数据库编程实例

数据库连接对象connection
连接对象：建立Python客户端与数据库的网络连接
创建方法：MySQLdb.Connect(参数)

3.1 connection对象支持的方法

3.2 cursor对象支持的方法

3.3 实例

示例一：

import pymysql#python3 用 pymysql，python2 用MySQLdb

conn = pymysql.connect(
                        host = '127.0.0.1',
                        port = 3306,
                        user = 'root',
                        passwd = 'root',
)
cus = conn.cursor()#连接数据库
sql = 'select version()'
cus.execute(sql)#执行sql语句
print(cus.fetchone())

cus.close()#关闭
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

示例二：
爬取i春秋的课程名并保存在数据库中

# coding=utf-8

import requests
import json
import pymysql#python3 用 pymysql，python2 用MySQLdb

payload_start = 'courseTag=&courseDiffcuty=1&IsExp=&producerId=&orderField=&orderDirection=&pageIndex='

def lesson(payload):
    url='https://www.ichunqiu.com/courses/ajaxCourses'
    #payload = 'courseTag=&courseDiffcuty=1&IsExp=&producerId=&orderField=&orderDirection=&pageIndex='
    #复制浏览器的UA，默认的UA属于爬虫UA被waf拦截
    headers = {
    'Cookie': 'ci_session=ea12fe98d0b99f9cfc7de37d51e34805ec566686; chkphone=acWxNpxhQpDiAchhNuSnEqyiQuDIO0O0O; __jsluid_s=e09df2c83e087903c72b4d33caca7c93; Hm_lvt_2d0601bd28de7d49818249cf35d95943=1662127088; Hm_lpvt_2d0601bd28de7d49818249cf35d95943=1662128935',

    'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Firefox/91.0',

    'Accept': 'application/json, text/javascript, */*; q=0.01',

    'Accept-Language': 'zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2',

    'Accept-Encoding': 'gzip, deflate',

    'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',

    'X-Requested-With': 'XMLHttpRequest',

    'Content-Length': '103',

    'Origin': 'https://www.ichunqiu.com',

    'Referer': 'https://www.ichunqiu.com/courses/nandu-chu',

    'Sec-Fetch-Dest': 'empty',

    'Sec-Fetch-Mode': 'cors',

    'Sec-Fetch-Site': 'same-origin',

    'Te': 'trailers',
    }
    r = requests.post(url,headers=headers,data=payload)
    data = json.loads(r.text)
    name_long = int(data['course']['perPageSize'])#每页有多少个课程名称
    #name_long = len(data['course']['result'])#方法1：json文件中course里面的有多少个result
    #print(name_long)
    #print(data['course']['result'][0]['courseName'])#读取json文件中course里面的result中的第一个courseName
    for i in range(name_long):
        #print(data['course']['result'][i]['courseName'],data['course']['result'][i]['producerName'])
        sql = "insert into lessons (lesson_name,lesson_own) values('%s','%s')"%(data['course']['result'][i]['courseName'],data['course']['result'][i]['producerName'])
        cus.execute(sql)#执行sql语句


conn = pymysql.connect(
                        host = '127.0.0.1',
                        port = 3306,
                        user = 'root',
                        passwd = 'root',
                        db = 'ichunqiu',
)
cus = conn.cursor()#连接数据库        
#使用for循环读取到8页课程名称
for i in range(1,9):#共12页的课程
    payload =payload_start+str(i)+'&tagType=&isOpen='
    lesson(payload)
    
cus.close()#关闭
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68