20230305

sudo mysql -u root -p

CREATE DATABASE mystock;
quit;

pip3 install efinance
pip3 install pymysql
pip3 install SQLAlchemy
python3

import efinance as ef
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


host = 'localhost'
port = 3306
username = 'root'
password = ' '
database = 'mystock'
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}')

# 显示创建会话，最后手动关闭保证连接mysql不会出错
Session = sessionmaker(bind=engine)
session = Session()

# 连接服务器期间的操作
df = ef.stock.get_quote_history("603876")
df.to_sql(name='603876_quote_history', con=engine, if_exists='replace', index=False)


# 关闭连接
session.close() # 或者 engine.dispose()

# 连接到 MySQL 数据库
# install.packages("RMySQL")
library(RMySQL)
library(DBI)
connection <- dbConnect(MySQL(), 
                        dbname = "mystock",
                        host = "localhost", 
                        port = 3306, 
                        user = "root", 
                        password = " ",
                        charset = "utf8")
# 设置一下字符编码问题
dbGetQuery(connection, 'SET NAMES gbk')
# 执行查询并将结果存储在变量中
query <- "SELECT * FROM 603876_quote_history;"
result <- dbGetQuery(connection, query)
head(result)

# 关闭数据库连接
dbDisconnect(connection)

import efinance as ef
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


host = 'localhost'
port = 3306
username = 'root'
password = ' '
database = 'mystock'
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}')

# 显示创建会话，最后手动关闭保证连接mysql不会出错
Session = sessionmaker(bind=engine)
session = Session()

# 连接服务器期间的操作
with open("/mnt/d/stock/test.txt","r") as f:
	ff = f.readlines()

for i in ff:
	stock = i.strip("\n")
	df = ef.stock.get_quote_history(stock)
	df.to_sql(name= stock+'_quote_history', con=engine, if_exists='replace', index=False)

# 关闭连接
session.close() # 或者 engine.dispose()

import efinance as ef
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from datetime import date, timedelta

# mysql服务器连接设置部分
host = 'localhost'
port = 3306
username = 'root'
password = ' '
database = 'mystock'
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}')

# 后续获取数据的开始日期和截止日期
today = date.today()
five_days_ago = today - timedelta(days=5)

today = today.strftime("%Y%m%d")
five_days_ago = five_days_ago.strftime("%Y%m%d")

# 显示创建会话，最后手动关闭保证连接mysql不会出错
Session = sessionmaker(bind=engine)
session = Session()

# 连接服务器期间的操作
with open("/mnt/d/unix_dir/stock/truncate.list","r") as f:
	ff = f.readlines()

for i in ff:
	stock = i.strip("\n")
	df = ef.stock.get_quote_history(stock,start = five_days_ago,end = today)
	df.to_sql(name= stock+'_quote_history', con=engine, if_exists='append', index=False)

# 关闭连接
session.close() # 或者 engine.dispose()