无法使用python将数据加载到mysql

mwg9r5ms 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(453)

我无法将symbol列加载到mysql中。文件名为bajfinance.ns.csv

Date,Open,High,Low,Close,Adj Close,Volume   
2002-07-01,5.239740,5.540930,5.239740,5.412680,0.063393,21923  
2002-07-02,5.523440,5.537040,5.421420,5.443770,0.063757,61045  
2002-07-03,5.505950,5.545790,5.328150,5.466120,0.064019,34161

在mysql中，我得到了

timestamp,open,high,low,close,adj_close,volume,symbol  
2002-07-01,5.23974,5.54093,5.23974,5.41268,0.063393,21923,NULL  
2002-07-02,5.52344,5.53704,5.42142,5.44377,0.063757,61045,NULL  
2002-07-03,5.50595,5.54579,5.32815,5.46612,0.064019,34161,NULL

如何在mysql中包含符号

import mysql.connector
config = {
'user':'root',
'password':'root',
'host':'127.0.0.1',
'database':'masterfiles'
 }
 conn= mysql.connector.connect(**config)
 c=conn.cursor()

def insertRows(fileName,c):
    delimiter=r','
    file=fileName.split("/")[-1]
    symbol = file[:-7]
    if file.startswith("BAJ"):
        c.execute("""Load data local infile %s into table adjprice fields terminated by %s ignore 1 lines 
                 (timestamp,open,high,low,close,adj_close,volume,@symbol)""",(fileName,delimiter))

localExtractFilePath="/Users/dalal"

import os

for file in os.listdir(localExtractFilePath):
    if file.endswith("csv"):
        insertRows(localExtractFilePath+"/"+file,c)
        print "Loaded file "+file+" into database"
        conn.commit()
c.close()
conn.close()

mysql python stock-data

来源：https://stackoverflow.com/questions/50896935/unable-to-load-data-into-mysql-using-python

2条答案

按热度按时间

roqulrg31#

我想这会满足你的要求：

c.execute("""
Load data local infile %s
    into table adjprice
    fields terminated by %s
    ignore 1 lines 
    (timestamp, open, high, low, close, adj_close, volume)
    set symbol = %s
""", (fileName, delimiter, symbol))

我建议使用查询参数，而不是使用字符串。但是，我不能100%确定文件名和字段组件是否可以作为参数传入。

赞(0）回复(0）举报 2021-06-20

fv2wmkja2#

我不确定您对使用导入模块的承诺，您在您的问题，但我会通过使用Pandas和sqlalchemy来解决这个问题，因为这一切都很容易。
以下是我如何处理您的问题：

import os
import pandas as pd
from sqlalchemy import create_engine

localExtractFilePath="/Users/dalal"

config = {
'user':'root',
'password':'root',
'host':'127.0.0.1',
'database':'masterfiles'
 }

def main():
    engine = create_engine('mysql://{}:{}r@{}/{}'.format(config['user'],config['password'],config['host'],config['database']))

    for file in os.listdir(localExtractFilePath):
        if file.endswith("csv"):
            print('{} file has been found. Loading to Pandas DF'.format(file))
            #Load to a Pandas DataFrame Object (Documentation: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html)
            df = pd.read_csv(os.path.abspath(file))
            df['symbol'] = os.path.splitext(file)[0][:-7]
            print('Loading to DB....')
            #Upload DF object to SQL (Documentation: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html)
            df.to_sql(con=engine, index=False, if_exists='append')
            print('Done, on to the next file!')

if __name__ == '__main__':
    main()

希望你觉得这有帮助！

赞(0）回复(0）举报 2021-06-20

我来回答

无法使用python将数据加载到mysql

2条答案

相关问题

热门标签

最新问答