Python_PyMySQL数据库操作
程序员文章站
2024-01-10 21:46:53
连接数据库: 建立游标: 创建一个名字叫 lj 的数据库: 选则要操作的数据库: 添加7个字段: 提交修改: 向表中插入数据: 整个爬取网站数据然后存入数据库的脚本: #-*- coding:utf-8 -*- import requests,re,pymysql def get_info(url) ......
连接数据库:
conn=pymysql.connect(host='*.*.*.*',port=3306,user='root',password='123456',charset='utf8')
建立游标:
cur = conn.cursor()
创建一个名字叫 lj 的数据库:
cur.execute('create database if not EXISTS lj character set utf8;') #用cur.execute执行SQL语句 注意如果要插入中文数据,建库时必须要加 character set utf8 意思是把字符集设置为utf8,要不然中文不能显示!
选则要操作的数据库:
conn.select_db('lj')
添加7个字段:
cur.execute('create table JiNan(source VARCHAR(50),addr VARCHAR(50),stru VARCHAR(50),dir_1 VARCHAR(50),floor VARCHAR(50),size VARCHAR(50),money VARCHAR(50));')
提交修改:
conn.commit()
向表中插入数据:
cur.execute("insert into JiNan(source,addr,stru,dir_1,floor,size,money) VALUES('%s','%s','%s','%s','%s','%s','%s')"%(so,ad,st,di,fl,si,mn))
整个爬取网站数据然后存入数据库的脚本:
#-*- coding:utf-8 -*- import requests,re,pymysql def get_info(url): html_response = requests.get(url) html_response.encoding = 'utf-8' add_add = r'<span class="region">(.*?) ' stru_add = r'class="zone"><span>(.*?) ' size_add = r'<span class="meters">(.*?) ' dir_add = r'</span><span>(.*?)</span></div><div' floor_add = r'<span>/</span>(.*?)</div></div><div' source_add = r'<div class="con"><a href=".*?">(.*?)</a>' money_add = r'lass="price"><span class="num">(.*?)</span>元/月' add = re.findall(add_add,html_response.text) stru = re.findall(stru_add,html_response.text) size = re.findall(size_add,html_response.text) dir_1 = re.findall(dir_add,html_response.text) floor = re.findall(floor_add,html_response.text) source = re.findall(source_add,html_response.text) money = re.findall(money_add,html_response.text) #print(add[0],stru[0],dir_1[0],floor[0],source[0],size[0],money[0]) return source,add,stru,dir_1,floor,size,money def into_db(source,add,stru,dir_1,floor,size,money,cur,conn): for so,ad,st,di,fl,si,mn in zip(source,add,stru,dir_1,floor,size,money): cur.execute("insert into JiNan(source,addr,stru,dir_1,floor,size,money) VALUES('%s','%s','%s','%s','%s','%s','%s')"%(so,ad,st,di,fl,si,mn)) conn.commit() if __name__ == '__main__': conn = pymysql.connect(host='*.*.*.*',port=3306,user='root',password='1234',charset='utf8') cur = conn.cursor() cur.execute('create database if not EXISTS lj character set utf8;') conn.select_db('lj') cur.execute('create table JiNan(source VARCHAR(50),addr VARCHAR(50),stru VARCHAR(50),dir_1 VARCHAR(50),floor VARCHAR(50),size VARCHAR(50),money VARCHAR(50));') conn.commit() for i in range(1,101): url = 'https://jn.lianjia.com/zufang/pg' + str(i) print(url) source,add,stru,dir_1,floor,size,money = get_info(url) into_db(source,add,stru,dir_1,floor,size,money,cur,conn) print('OK... ...') conn.close()