PYTHON连接数据库等操作

1、访问MySQL数据库，建表、写入数据、读取数据

import pymysql
import pandas as pd
from sqlalchemy import create_engine

# 连接到数据库
conn = pymysql.connect(host='xxx.xxx.x.xx', port=xxxx, user='xxxx', password='xxxxxxxx', database='SE_DATUM')

c = conn.cursor()
# 删除如果表已经存在
c.execute("DROP TABLE IF EXISTS predict_weather_data_test")
c.execute('''CREATE TABLE IF NOT EXISTS predict_weather_data_test
             (id INTEGER PRIMARY KEY AUTO_INCREMENT,
             clo REAL, dhi REAL, dni REAL, ghi REAL, gust REAL, phs REAL, pre REAL,
             prs_qfe REAL, rhu REAL, tem REAL, time TEXT, uvb REAL, uvi REAL, vis REAL,
             wep REAL, wnd REAL, wnd_100m REAL, wns REAL, wns_100m REAL, wns_grd REAL,
             wns_grd_100m REAL)''')
conn.commit()

# 创建SQLAlchemy引擎
engine = create_engine(f'mysql+pymysql://user:passward@host:port/SE_DATUM')

# # 读取excel文件
df = pd.read_excel('test/forecast_weather.xlsx')

# 将数据添加到数据库中
df.to_sql(name='predict_weather_data_test', con=engine, if_exists='replace', index=False)

# 读取表数据
aa = pd.read_sql('SELECT * FROM predict_weather_data_test', conn)

# 关闭数据库连接
conn.close()

2、sqlite 

import sqlite3
import pandas as pd
import requests


# 创建数据库和表
conn = sqlite3.connect('example.db')
# c = conn.cursor()
# 删除如果表已经存在
c.execute("DROP TABLE IF EXISTS predict_data")
c.execute('''CREATE TABLE IF NOT EXISTS predict_data
             (id INTEGER PRIMARY KEY AUTOINCREMENT,
             clo REAL, dhi REAL, dni REAL, ghi REAL, gust REAL, phs REAL, pre REAL,
             prs_qfe REAL, rhu REAL, tem REAL, time TEXT, uvb REAL, uvi REAL, vis REAL,
             wep REAL, wnd REAL, wnd_100m REAL, wns REAL, wns_100m REAL, wns_grd REAL,
             wns_grd_100m REAL)''')
conn.commit()
# 请求获取预报气象数据
# 发送 HTTP 请求
response = requests.get(
    "https://api.seniverse.com/v4?fields=weather_1h&key=XXXXXXXXXXXX&locations=31.453109:121.1828730")
# 解析 JSON 响应
aa = response.json()
forecast_weather = response.json()['weather_1h'][0]['data']
#
dfs = []
for i in range(24, 48):
    data_15min = forecast_weather[i]
    globals()[f'df_{i-24}'] = pd.DataFrame([data_15min])
    dfs.append(globals()[f'df_{i-24}'])
# 使用concat函数合并所有的DataFrame
df_merged = pd.concat(dfs, ignore_index=True)
#
# 将数据添加到数据库中
df_merged.to_sql('predict_data', conn, if_exists='replace', index=False)
# 读取数据库中的数据
df_from_db_1 = pd.read_sql_query('select * from predict_data', conn)
df_from_db = pd.read_sql_query('select * from predict_data ORDER BY id ASC LIMIT 10', conn)
print(df_from_db)
#
# 关闭数据库连接
conn.close()

长段SQL的写法

query = (f"SELECT F0302_Date, sum(F0302_Val_1) as val_1, sum(F0302_Val_4) as val_2, sum(F0302_Val_7) as val_3, "
         f"sum(F0302_Val_10) as val_4, sum(F0302_Val_13) as val_5, sum(F0302_Val_16) as val_6, "
           f"FROM TB0302_DAY_POWER "
           f"WHERE F0202_Data_Type = 409 AND F0204_Dtu_Id IN {dtu_ids} "
           f"GROUP BY F0302_Date")