File size: 2,627 Bytes
3351f47 474c6f0 3351f47 474c6f0 3351f47 474c6f0 3351f47 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | import os
import pandas as pd
import time
from sqlalchemy import create_engine, Column, String, Integer, Float, DateTime, inspect, MetaData
from sqlalchemy.orm import declarative_base
from sqlalchemy.exc import SQLAlchemyError
DATABASE_URL = os.getenv('DATABASE_URL')
engine = create_engine(DATABASE_URL)
def get_df_from_csv(csv_file_path):
df = pd.read_csv(csv_file_path)
return df
def get_schema_from_df(df):
schema = pd.io.json.build_table_schema(df)
return schema
def create_table_from_schema(table_name, schema):
Base = declarative_base()
inspector = inspect(engine)
metadata = MetaData()
metadata.reflect(bind=engine)
# Check if table already exists
if table_name in inspector.get_table_names():
existing_columns = {column['name']: column['type'] for column in inspector.get_columns(table_name)}
new_columns = {field['name']: field['type'] for field in schema['fields']}
if existing_columns == new_columns:
print(f"Table '{table_name}' with the same schema already exists. Skipping creation.")
return
else:
print(f"Table '{table_name}' exists but has a different schema. Creating a new table with a timestamp suffix.")
table_name = f"{table_name}_{int(time.time())}"
class DynamicTable(Base):
__tablename__ = table_name
id = Column(Integer, primary_key=True)
for column in schema['fields']:
if column['name'] != 'id':
if column['type'] == 'integer':
locals()[column['name']] = Column(Integer)
elif column['type'] == 'number':
locals()[column['name']] = Column(Float)
elif column['type'] == 'datetime':
locals()[column['name']] = Column(DateTime)
else:
locals()[column['name']] = Column(String)
try:
Base.metadata.create_all(engine)
print(f"Table '{table_name}' created successfully.")
except SQLAlchemyError as e:
print(f"Error creating table: {str(e)}")
def save_data_to_table(table_name, df):
try:
df.to_sql(table_name, engine, if_exists='append')
except SQLAlchemyError as e:
print(f"Error saving data to table: {str(e)}")
if __name__ == "__main__":
filename = 'funder_dataset_300_rows.csv'
df = get_df_from_csv(filename)
schema = get_schema_from_df(df)
table_name = filename.split('.')[0]
create_table_from_schema(table_name, schema)
save_data_to_table(table_name, df)
|