1.python使用数据库
db = MySQLdb.connect(“localhost”, “root”, “123456”, “testdb”, charset=‘utf8’ ) 即
db = MySQLdb.connect(“localhost”, “用户名”, “密码”, “数据库名”, charset=‘utf8’ )
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost", "root", "123456", "testdb", charset='utf8' )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
#创造一张表
# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# 关闭数据库连接
db.close()
2.效果
3.参考
4.插入带主键的数据:
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost", "root", "123456", "testdb", charset='utf8' )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
#创造一张表
# 如果数据表已经存在使用 execute() 方法删除表。
# cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
sql = """create table test2(
tutorial_id INT NOT NULL AUTO_INCREMENT,
tutorial_title VARCHAR(100) NOT NULL,
tutorial_author VARCHAR(40) NOT NULL,
PRIMARY KEY ( tutorial_id )
)"""
cursor.execute(sql)
# SQL 插入语句
sql = """INSERT INTO test2(
tutorial_title, tutorial_author)
VALUES ( 'chinese', '小明')"""
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# 关闭数据库连接
db.close()
5.对4 的封装
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
class Db():
def __init__(self):
# 打开数据库连接
self.db = MySQLdb.connect("localhost", "root", "123456", "testdb", charset='utf8' )
# 使用cursor()方法获取操作游标
self.cursor = self.db.cursor()
#创造一张表
# 如果数据表已经存在使用 execute() 方法删除表。
# cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
###sql = """create table test2(
#tutorial_id INT NOT NULL AUTO_INCREMENT,
#tutorial_title VARCHAR(100) NOT NULL,
#tutorial_author VARCHAR(40) NOT NULL,
#PRIMARY KEY ( tutorial_id )
#)"""
def insert(self):
#cursor.execute(sql)
# SQL 插入语句
sql = """INSERT INTO test2(
tutorial_title, tutorial_author)
VALUES ("math","王大力")"""
try:
# 执行sql语句
self.cursor.execute(sql)
# 提交到数据库执行
self.db.commit()
except:
# Rollback in case there is any error
self.db.rollback()
# 关闭数据库连接
self.db.close()
db = Db()
db.insert()
6.改进 把insert 能穿sql需要的参数了
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
class Db():
def __init__(self):
# 打开数据库连接
self.db = MySQLdb.connect("localhost", "root", "123456", "testdb", charset='utf8' )
# 使用cursor()方法获取操作游标
self.cursor = self.db.cursor()
#创造一张表
# 如果数据表已经存在使用 execute() 方法删除表。
# cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
###sql = """create table test2(
#tutorial_id INT NOT NULL AUTO_INCREMENT,
#tutorial_title VARCHAR(100) NOT NULL,
#tutorial_author VARCHAR(40) NOT NULL,
#PRIMARY KEY ( tutorial_id )
#)"""
# v1 是sql的第一个参数
def insert(self,v1,v2):
#cursor.execute(sql)
# SQL 插入语句
sql = "INSERT INTO test2(tutorial_title, tutorial_author)VALUES ('{}','{}')".format(v1,v2)
print("sql{}".format(sql))
try:
# 执行sql语句
self.cursor.execute(sql)
# 提交到数据库执行
self.db.commit()
except:
# Rollback in case there is any error
self.db.rollback()
# 关闭数据库连接
self.db.close()
db = Db()
db.insert("python","wangdali")
7.把抓取的弹幕,存到数据库中
"""docstring for Bilibili"""
import MySQLdb
import time
import requests
from lxml import etree
from 弹幕.oid_.Oid import Oid
class Bilibili():
def __init__(self, av_id):
self.headers = {
'Host': 'api.bilibili.com',
'Connection': 'keep-alive',
'Cache-Control': 'max-age=0',
'Upgrade-Insecure-Requests': '1',
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.92 Safari/537.36',
'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8',
'Accept-Encoding': 'gzip, deflate, br',
'Accept-Language': 'zh-CN,zh;q=0.9',
'Cookie': 'finger=edc6ecda; LIVE_BUVID=AUTO1415378023816310; stardustvideo=1; CURRENT_FNVAL=8; buvid3=0D8F3D74-987D-442D-99CF-42BC9A967709149017infoc; rpdid=olwimklsiidoskmqwipww; fts=1537803390'
}
self.oid = Oid(av_id).get_oid()
# print(self.oid)
self.url = 'https://api.bilibili.com/x/v1/dm/list.so?oid=' + self.oid
self.barrage_reault = self.get_page()
# 获取信息
def get_page(self):
try:
# 延时操作,防止太快爬取
time.sleep(0.5)
response = requests.get(self.url, headers=self.headers)
except Exception as e:
print('获取xml内容失败,%s' % e)
return False
else:
if response.status_code == 200:
# 下载xml文件
with open('bilibili.xml', 'wb') as f:
f.write(response.content)
return True
else:
return False
# 关闭数据库连接
# self.db.close()
# 解析网页
def param_page(self):
time.sleep(1)
if self.barrage_reault:
# 文件路径,html解析器
html = etree.parse('bilibili.xml', etree.HTMLParser())
# xpath解析,获取当前所有的d标签下的所有文本内容
results = html.xpath('//d//text()')
return results
# b=Bilibili("BV1cy4y1k7A2")
id = input("请输入b栈的视频id号:")
b=Bilibili(id)
danmu_result = b.param_page()
print(danmu_result)
def db(v1,v2):
db = MySQLdb.connect("localhost", "root", "123456", "testdb", charset='utf8' )
## test3 ,自增的id ,a_id,danmu
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO test3(a_id, danmu)VALUES ('{}','{}')".format(v1, v2)
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭数据库连接
db.close()
# db("shadiao6","world")
# db("hello3","world")
for d in danmu_result:
print(d)
db(id,d)
# print(danmu_result)