数据库创建和数据表创建

步骤1：登录后 创建数据库

  1. 登录 MySQL 控制台：

    sudo mysql -u xd -p

  2. 在 MySQL 控制台中，执行以下命令创建数据库my_db：

    CREATE DATABASE my_db;
USE my_db;

步骤2：创建数据表

CREATE TABLE IF NOT EXISTS xfd_data (
            id INT AUTO_INCREMENT PRIMARY KEY,
            prodName VARCHAR(255),
            prodCat VARCHAR(255),
            avgPrice DECIMAL(10, 2),
            specInfo VARCHAR(255),
            unitInfo VARCHAR(255),
            pubDate DATE
        );
# 查看表结构
desc xfd_data;

Python操作数据库

PyMySQL 是 Python 中用于连接 MySQL 数据库的一个库，它提供了对 MySQL 数据库进行操作的功能。

安装

# pip install -i https://pypi.tuna.tsinghua.edu.cn/simple  XXXXXXXXXXX
pip install pymysql

# 连接需要用到这个加密库(可能)
pip install cryptography

典型：数据库连接

import pymysql

# 连接到 MySQL 数据库
connection = pymysql.connect(host='localhost',
                             user='username',
                             password='password',
                             db='dbname',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 创建一个 cursor 对象
cursor = connection.cursor()

#以下是具体操作内容
#xxxxx
#xxxxx
#xxxxx




# 关闭
connection.close()

典型：写入数据

import pymysql

# 连接到 MySQL 数据库
connection = pymysql.connect(host='localhost',
                             user='username',
                             password='password',
                             db='dbname',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 创建一个 cursor 对象
cursor = connection.cursor()



#表结构为prodName, prodCat, avgPrice, specInfo, unitInfo, pubDate
test_item=['V1','V2',1.1,'V3','V4','2023-01-01']
sql = "INSERT INTO table_name (prodName, prodCat, avgPrice, specInfo, unitInfo, pubDate) VALUES (%s, %s, %s, %s, %s, %s)"

cursor.execute(sql, test_item)
connection.commit()




connection.close()

典型：读取数据

import  pymysql
# 打开数据库连接
connection = pymysql.connect(host='localhost',
                             user='username',
                             password='password',
                             db='dbname',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 创建一个 cursor 对象
cursor = connection.cursor()

#以下是具体操作内容

cursor.execute('select * from table_name')

print(cursor.fetchall())


# 关闭
connection.close()

项目: 北京菜场数据存储到mysql代码

完整版（不建议）

import json
import  pymysql
connection = pymysql.connect(host='192.168.40.131',
                             user='xd',
                             password='123',
                             db='my_db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 使用 cursor() 方法创建一个游标对象
cursor = connection.cursor()




for i in range(1,1153):
    file_name="./项目：新发地完整/数据/"+str(i)+".数据.json"
    with open(file_name, 'r',encoding='utf-8') as json_file:
        data = json.load(json_file)
        for i in range(0,len(data['list'])):
            #菜名
            prodName=data['list'][i]['prodName']
            #类别
            prodCat=data['list'][i]['prodCat']
            #均价
            price=data['list'][i]['avgPrice']
            #规格信息
            specInfo=data['list'][i]['specInfo']
            #单位信息
            unitInfo=data['list'][i]['unitInfo']
            #更新时间
            pubDate=data['list'][i]['pubDate']
          
            prod_item=[prodName,prodCat,price,specInfo,unitInfo,pubDate]
            print(prod_item)
            sql = "INSERT INTO xfd_data (prodName, prodCat, avgPrice, specInfo, unitInfo, pubDate) VALUES (%s, %s, %s, %s, %s, %s)"
            cursor.execute(sql, prod_item)
            connection.commit()
connection.close()

爬虫直接到mysql版

import pymysql
import requests  
import time
connection = pymysql.connect(host='192.168.120.128',
                             user='xd',
                             password='123',
                             db='my_db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 创建一个 cursor 对象
cursor = connection.cursor()
url = "http://www.xinfadi.com.cn/getPriceData.html"  
  
headers = {  
    "Accept": "*/*",  
    "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",  
    "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",  
    "X-Requested-With": "XMLHttpRequest",  
}  
  
for i in range(1,10):
    data = {  
    "limit": "500",  
    "current": str(i),  
    "pubDateStartTime": "",  
    "pubDateEndTime": "",  
    "prodPcatid": "",  
    "prodCatid": "",  
    "prodName": "",  
    }  
    response = requests.post(url, headers=headers, data=data)  
 
    if response.status_code == 200:
        data=response.json()
      
        for i in range(0,len(data['list'])):
            #菜名
            prodName=data['list'][i]['prodName']
            #类别
            prodCat=data['list'][i]['prodCat']
            #均价
            price=data['list'][i]['avgPrice']
            #规格信息
            specInfo=data['list'][i]['specInfo']
            #单位信息
            unitInfo=data['list'][i]['unitInfo']
            #更新时间
            pubDate=data['list'][i]['pubDate']
          
            prod_item=[prodName,prodCat,price,specInfo,unitInfo,pubDate]
            sql = "INSERT INTO xfd_data (prodName, prodCat, avgPrice, specInfo, unitInfo, pubDate) VALUES (%s, %s, %s, %s, %s, %s)"
            cursor.execute(sql, prod_item)
            connection.commit()
            print(prod_item)
        time.sleep(1)
    else:
        print(response.status_code)
        # 关闭 
        connection.close()
connection.close()