python连接mysql

安装

pip install pymysql

基本使用

#数据的增删改
import pymysql
#1.创建一个链接对象:决定了要去访问链接哪一个数据库服务器下的哪一个数据仓库
conn=pymysql.Connection(
    user="root",  # 数据库用户名
    password="123456", # 密码
    host="127.0.0.1",  # 数据库地址
    port=3306, # 端口号
    db="demo",  # 数据库名
    charset="utf8",
    cursorclass=pymysql.cursors.DictCursor #字典输出
)
#2.创建一个游标对象：可以让python执行sql语句
cursor = conn.cursor()
#3.使用游标对象执行sql语句(增删改)
id_value = 66  
# sql = 'delete from new_dep where id = 5'
# sql = 'insert into new_dep values (66,"haha","xxx")'
sql = 'update new_dep set dep_name="销售" where id = ?'
cursor.execute(sql,(id_value,))
#4.提交事务:让游标执行的sql语句完全的映射到数据库中
conn.commit()
#5.关闭资源
cursor.close()
conn.close()

示例

import requests
import pymysql
#创建一个mysql的链接对象
conn=pymysql.Connection(
    user="root",  # 数据库用户名
    password="123456", # 密码
    host="127.0.0.1",  # 数据库地址
    port=3306, # 端口号
    db="demo",  # 数据库名
    charset="utf8",
    cursorclass=pymysql.cursors.DictCursor #字典输出
)
#创建游标对象
cursor = conn.cursor()

headers = {
    'User-Agent':'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36'
}
url = 'https://movie.douban.com/j/chart/top_list'
params = {
    "type": "24",
    "interval_id": "100:90",
    "action": "",
    "start": "20",
    "limit": "20"
}
movie_data = requests.get(url=url,headers=headers,params=params).json()
#电影名字，评分，导演，上映时间
for dic in movie_data:
    title = dic['title']
    score = dic['score']
    actors = dic['actors']
    actors = ','.join(actors)
    year = dic['release_date']
    
    sql = 'insert into movie_tb(title,score,actors,year) values ("%s","%s","%s","%s")'%(title,score,actors,year)
    cursor.execute(sql)
    conn.commit()
    print(title,'：电影数据爬取存储成功！')

cursor.close()
conn.close()