请输入以下代码涉及的对应的数据库表创建及存储代码,代码如下: import os import struct import mysql.connector # 数据文件路径 data_path = '/path/to/data/files' # MySQL数据库配置 db_config = { 'host': 'localhost', 'user': 'root', 'password': 'password', 'database': 'stock_data' } # 建立MySQL数据库连接 cnx = mysql.connector.connect(**db_config) cursor = cnx.cursor() # 遍历数据文件目录 for root, dirs, files in os.walk(data_path): for file in files: # 获取文件名和扩展名 filename, ext = os.path.splitext(file) # 判断文件类型 if ext == '.day': # 解析日线数据文件 with open(os.path.join(root, file), 'rb') as f: # 读取文件头 header = f.read(32) # 解析文件头 code, name, _, _, _, _, _, _, _, _, _, _, _, _, _, _ = struct.unpack('6s10sHHHHHHHHHHHHHH', header) # 读取数据 data = f.read() # 解析数据 for i in range(len(data) // 32): record = struct.unpack('IIIIIfII', data[i * 32: (i + 1) * 32]) date = str(record[0]) open_price = record[1] / 100.0 high_price = record[2] / 100.0 low_price = record[3] / 100.0 close_price = record[4] / 100.0 volume = record[5] amount = record[6] / 10000.0 # 将数据存储到MySQL数据库中 cursor.execute('INSERT INTO stock_data_daily (code, name, date, open_price, high_price, low_price, close_price, volume, amount) ' 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)', (code, name, date, open_price, high_price, low_price, close_price, volume, amount)) elif ext == '.lc1': # 解析一分钟和五分钟数据文件 with open(os.path.join(root, file), 'rb') as f: # 读取文件头 header = f.read(32) # 解析文件头 code, name, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _, _ = struct.unpack('6s10sHHHHHHHHHHHHHHHHHHHHHH', header) # 读取数据 data = f.read() # 解析数据 for i in range(len(data) // 32): record = struct.unpack('IIIIIfII', data[i * 32: (i + 1) * 32]) date = str(record[0]) time = str(record[1]) open_price = record[2] / 100.0 high_price = record[3] / 100.0 low_price = record[4] / 100.0 close_price = record[5] / 100.0 volume = record[6] amount = record[7] / 10000.0 # 将数据存储到MySQL数据库中 cursor.execute('INSERT INTO stock_data_minute (code, name, date, time, open_price, high_price, low_price, close_price, volume, amount) ' 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', (code, name, date, time, open_price, high_price, low_price, close_price, volume, amount)) # 提交MySQL数据库事务并关闭连接 cnx.commit() cursor.close() cnx.close()
时间: 2024-02-25 09:54:34 浏览: 98
以下是对应的数据库表创建代码:
```
CREATE TABLE `stock_data_daily` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(6) NOT NULL,
`name` varchar(10) NOT NULL,
`date` date NOT NULL,
`open_price` decimal(10,2) NOT NULL,
`high_price` decimal(10,2) NOT NULL,
`low_price` decimal(10,2) NOT NULL,
`close_price` decimal(10,2) NOT NULL,
`volume` decimal(20,2) NOT NULL,
`amount` decimal(20,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `stock_data_minute` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(6) NOT NULL,
`name` varchar(10) NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`open_price` decimal(10,2) NOT NULL,
`high_price` decimal(10,2) NOT NULL,
`low_price` decimal(10,2) NOT NULL,
`close_price` decimal(10,2) NOT NULL,
`volume` decimal(20,2) NOT NULL,
`amount` decimal(20,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
以下是对应的数据存储代码:
```
# 将数据存储到MySQL数据库中
cursor.execute('INSERT INTO stock_data_daily (code, name, date, open_price, high_price, low_price, close_price, volume, amount) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)',
(code, name, date, open_price, high_price, low_price, close_price, volume, amount))
# 将数据存储到MySQL数据库中
cursor.execute('INSERT INTO stock_data_minute (code, name, date, time, open_price, high_price, low_price, close_price, volume, amount) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(code, name, date, time, open_price, high_price, low_price, close_price, volume, amount))
```
阅读全文