<jupyter_start><jupyter_text>Where are my files<jupyter_code>data_dir = "/Users/csiu/repo/kick/data/01_data_set"
import pandas as pd
import glob
import os
# Find my file
data_files = glob.glob(os.path.join(data_dir, "Kick*csv"))
# Load file
data_file = data_files[0]
df = pd.read_csv(data_file)

print(df.columns)
df.head()<jupyter_output>Index(['id', 'photo', 'name', 'blurb', 'goal', 'pledged', 'state', 'slug',
       'disable_communication', 'country', 'currency', 'currency_symbol',
       'currency_trailing_code', 'deadline', 'state_changed_at', 'created_at',
       'launched_at', 'staff_pick', 'backers_count', 'static_usd_rate',
       'usd_pledged', 'creator', 'location', 'category', 'profile',
       'spotlight', 'urls', 'source_url', 'friends', 'is_starred',
       'is_backing', 'permissions'],
      dtype='object')<jupyter_text>Load to database Connect<jupyter_code>import psycopg2
from sqlalchemy import create_engine
dbname="kick"
tblname="info"
verbose=True
data_dir = "/Users/csiu/repo/kick/data/01_data_set"

engine = create_engine(
        'postgresql://localhost:5432/{dbname}'.format(dbname=dbname))

# Connect to database
conn = psycopg2.connect(dbname=dbname)
cur = conn.cursor()<jupyter_output><empty_output><jupyter_text>Load<jupyter_code>cur.execute("DROP TABLE IF EXISTS {table};".format(table=tblname))
cur.execute("DROP TABLE IF EXISTS info3;")
cur.execute("DROP TABLE IF EXISTS info7;")
cur.execute("DROP TABLE IF EXISTS info21;")
cur.execute("DROP TABLE IF EXISTS info29;")

#ERROR 3 of 44: Kickstarter002.csv
#ERROR 7 of 44: Kickstarter006.csv
#ERROR 21 of 44: Kickstarter020.csv
#ERROR 29 of 44: Kickstarter028.csv

conn.commit()
# Find my file
data_files = glob.glob(os.path.join(data_dir, "Kick*csv"))
num_files = len(data_files)
pd.read_csv(data_files[0]).to_sql(tblname, engine, if_exists="append")
pd.read_csv(data_files[1]).to_sql(tblname, engine, if_exists="append")
pd.read_csv(data_files[2]).to_sql(tblname, engine, if_exists="append")<jupyter_output><empty_output><jupyter_text>Get an error when I load the 3rd file -- need to figure out why<jupyter_code>df_pass = pd.read_csv(data_files[1])
df_fail = pd.read_csv(data_files[2])

for i,(p,f) in enumerate(zip(df_pass.dtypes, df_fail.dtypes)):
    if p!=f:
        print(df_pass.columns[i], p, f)<jupyter_output>goal int64 float64
friends float64 object
is_starred float64 object
is_backing float64 object
permissions float64 object<jupyter_text>Types are different; try redefining the variables<jupyter_code>cur.execute("DROP TABLE IF EXISTS {table};".format(table=tblname))
cur.execute("DROP TABLE IF EXISTS info3;")
cur.execute("DROP TABLE IF EXISTS info7;")
cur.execute("DROP TABLE IF EXISTS info21;")
cur.execute("DROP TABLE IF EXISTS info29;")

#ERROR 3 of 44: Kickstarter002.csv
#ERROR 7 of 44: Kickstarter006.csv
#ERROR 21 of 44: Kickstarter020.csv
#ERROR 29 of 44: Kickstarter028.csv

conn.commit()<jupyter_output><empty_output><jupyter_text>Try defining table column types first before loading<jupyter_code>sql = """CREATE TABLE {table} (
    index INTEGER,
%s);""".format(table=tblname)

sql_details = ""
for col_name,col_type in zip(df.columns, df.dtypes):
    if col_name in ["friends", "is_starred", "is_backing", "permissions"] or col_type == object:
        data_type = "VARCHAR"
    elif col_type == float:
        data_type = "FLOAT"
    elif col_type == bool:
        data_type = "BOOLEAN"
    elif col_type == int:
        data_type = "INTEGER"
    sql_details += "    {name} {type},".format(name=col_name, type=data_type) + "\n"
sql_details = sql_details.strip(",\n")

sql = sql % sql_details
print(sql)
cur.execute(sql)
conn.commit()<jupyter_output><empty_output><jupyter_text>Comment out:```pd.read_csv(data_files[0]).to_sql(tblname, engine, if_exists="append")pd.read_csv(data_files[1]).to_sql(tblname, engine, if_exists="append")pd.read_csv(data_files[2]).to_sql(tblname, engine, if_exists="append")cur.execute("DROP TABLE IF EXISTS {table};".format(table=tblname))cur.execute("DROP TABLE IF EXISTS info3;")cur.execute("DROP TABLE IF EXISTS info7;")cur.execute("DROP TABLE IF EXISTS info21;")cur.execute("DROP TABLE IF EXISTS info29;")ERROR 3 of 44: Kickstarter002.csvERROR 7 of 44: Kickstarter006.csvERROR 21 of 44: Kickstarter020.csvERROR 29 of 44: Kickstarter028.csvconn.commit()```<jupyter_code>for i,data_file in enumerate(data_files):
    fname = os.path.basename(data_file)
    df = pd.read_csv(data_file)
    try:
        msg = "Processing {i} of {num_files}: {fname}".format(
                i=i+1, num_files=num_files, fname=fname)
        print(msg)
        df.to_sql(tblname, engine, if_exists="append")
    except:
        msg = "ERROR {i} of {num_files}: {fname}".format(
                i=i+1, num_files=num_files, fname=fname)
        print(msg)        
        df.to_sql(tblname + str(i+1), engine, if_exists="append")<jupyter_output>Processing 1 of 44: Kickstarter.csv
Processing 2 of 44: Kickstarter001.csv
Processing 3 of 44: Kickstarter002.csv
Processing 4 of 44: Kickstarter003.csv
Processing 5 of 44: Kickstarter004.csv
Processing 6 of 44: Kickstarter005.csv
Processing 7 of 44: Kickstarter006.csv
Processing 8 of 44: Kickstarter007.csv
Processing 9 of 44: Kickstarter008.csv
Processing 10 of 44: Kickstarter009.csv
Processing 11 of 44: Kickstarter010.csv
Processing 12 of 44: Kickstarter011.csv
Processing 13 of 44: Kickstarter012.csv
Processing 14 of 44: Kickstarter013.csv
Processing 15 of 44: Kickstarter014.csv
Processing 16 of 44: Kickstarter015.csv
Processing 17 of 44: Kickstarter016.csv
Processing 18 of 44: Kickstarter017.csv
Processing 19 of 44: Kickstarter018.csv
Processing 20 of 44: Kickstarter019.csv
Processing 21 of 44: Kickstarter020.csv
Processing 22 of 44: Kickstarter021.csv
Processing 23 of 44: Kickstarter022.csv
Processing 24 of 44: Kickstarter023.csv
Processing 25 of 44: Kickstarter024.csv
Processing 2[...]<jupyter_text>It works  Close connection<jupyter_code># Close communication with the database
cur.close()
conn.close()
df.id.to_csv("/Users/csiu/Desktop/test.txt", index=False)
pd.read_csv(data_files[-1])
data_files[-1]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12910070181165334, 'fraction_numerical': 0.04847396768402154, 'mean_word_length': 5.589247311827957, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 30, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '288595', 'license': 'MIT', 'n_tokens_mistral': 2457, 'n_tokens_neox': 2129, 'n_words': 513, 'path': 'src/ipynb/day43_loading.ipynb', 'repo_name': 'csiu/kick', 'size': '6127'}