import pymysql
import pandas as pd
import sys

# pip install csvkit
#csvsql --dialect mysql --snifflimit 100000000 bigdatafile.csv > maketable.sql
#csvsql --db mysql://root:###########99@localhost:3306/SAMYSTOCKS --tables income_full_quarterly --insert /home/samystocks/simfin/all/income-full-quarterly.csv
# csvsql --dialect mysql --snifflimit 100000000 cashflow-full-quarterly.csv > cashflow-full-quarterly.sql

"""

I know that this is a little bit stale as a topic, but there is an easier way -- IF -- you are using phpmyadmin as your mysql front end.

1)Create a database with just default settings.

2)Select the database.

3)Click "Import" at the top of the screen.

4)Select CSV under "Format".

5)Choose the options appropriate to your csv file (open the csv file in a text editor and reference it to get 'appropriate' options).

If you muck it up, no problem, simply drop the database and try again.

"""
"""
def mysql_to_csv(sql, file_path, host, user, password):
    '''
    The function creates a csv file from the result of SQL
    in MySQL database.
    '''
    try:
        con = pymysql.connect(host=host,
                                user=user,
                                password=password)
        print('Connected to DB: {}'.format(host))
        # Read table with pandas and write to csv
        df = pd.read_sql(sql, con)
        df.to_csv(file_path, encoding='utf-8', header = True,\
         doublequote = True, sep=',', index=False)
        print('File, {}, has been created successfully'.format(file_path))
        con.close()

    except Exception as e:
        print('Error: {}'.format(str(e)))
        sys.exit(1)

# Execution Example
sql = 'Select * From world.city'
file_path = '/tmp/city.csv'
host = 'host url'
user = 'username'
password = 'password'
mysql_to_csv(sql, file_path, host, user, password)
"""

def csv_to_mysql(load_sql, host, user, password):
    '''
    This function load a csv file to MySQL table according to
    the load_sql statement.
    '''
    try:
        con = pymysql.connect(host=host,
                                user=user,
                                password=password,
                                autocommit=True,
                                local_infile=1)
        print('Connected to DB: {}'.format(host))
        # Create cursor and execute Load SQL
        cursor = con.cursor()
        cursor.execute(load_sql)
        print('Succuessfully loaded the table from csv.')
        con.close()
       
    except Exception as e:
        print('Error: {}'.format(str(e)))
        sys.exit(1)

host = 'localhost'
user = 'root'
password = '###########99'

# companies.zip Ticker;SimFinId;"Company Name";IndustryId
#load_sql_companies = "LOAD DATA LOCAL INFILE '/home/samystocks/simfin/all/companies.csv' INTO TABLE SAMYSTOCKS.companies FIELDS TERMINATED BY ';' IGNORE 1 LINES;"
#csv_to_mysql(load_sql_companies, host, user, password)

# industries.zip: IndustryId;Sector;Industry
#load_sql_industries = "LOAD DATA LOCAL INFILE '/home/samystocks/simfin/all/industries.csv' INTO TABLE SAMYSTOCKS.industries FIELDS TERMINATED BY ';' IGNORE 1 LINES;"
#csv_to_mysql(load_sql_industries, host, user, password)

#shareprices-daily.csv Ticker;SimFinId;Date;Open;Low;High;Close;"Adj. Close";Dividend;Volume
#load_sql_sharepricesdaily = "LOAD DATA LOCAL INFILE '/home/samystocks/simfin/all/shareprices-daily.csv' INTO TABLE SAMYSTOCKS.sharepricesdaily FIELDS TERMINATED BY ';' IGNORE 1 LINES;"
#csv_to_mysql(load_sql_sharepricesdaily, host, user, password)

# csvsql --db mysql://root:###########99@localhost:3306/SAMYSTOCKS --tables income_full_quarterly --insert /home/samystocks/simfin/all/income-full-quarterly.csv

# csvsql --db mysql://root:###########99@localhost:3306/SAMYSTOCKS --tables balance_full_quarterly --insert /home/samystocks/simfin/all/balance-full-quarterly.csv

# csvsql --db mysql://root:###########99@localhost:3306/SAMYSTOCKS --tables cashflow_full_quarterly --insert /home/samystocks/simfin/all/cashflow-full-quarterly.csv