[story] create prefect flow to load gsheet into db

[vperluzzo]

• # make test load .xlsx -> db [JK]
• # create connection gdrive<>db [AS]
• # create prefect flow gdrive>db [AS]

[vperluzzo]

[acivitillo]

@jkrobicki and @AdamSulek any updates?

[jkrobicki]

Created table in postgres:

And trying to put excel into this db:

[jkrobicki]

Trying on this code to work:

import pandas as pd
from sqlalchemy import create_engine
import psycopg2

engine=create_engine("postresql+psycopg2://sqlpad:sqlpad/localhost:3001/Postgres demo")

def sheets(data, file):
if(data=="Sheet1"):
df=pd.read_excel(file, "Sheet1")
df.to_sql(name="timelogs_excel", con=engine,if_exists="append", index=False)

with pd.ExcelFile("extract_timelogs_may_july.xlsx") as xls:
for sheet_name in xls.sheet_names:
sheets(sheet_name,xls)

[jkrobicki]

Changed code to:
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql://sqlpad:sqlpad@localhost:3001/Postgres_sqlpad", pool_pre_ping=True)

def sheets(file):
df=pd.read_excel(file)
print(df)
df.to_sql(name="timelogs_excel", con=engine, if_exists="append", index=False)

[jkrobicki]

Working on a problem with connection to database:

[jkrobicki]

Changing database to SQLite and trying to put two column table first

[jkrobicki]

Managed to insert specific columns into SQLite database

[jkrobicki]

Working code for importing "extract_timelogs_may_july.xlsx" into SQLite database via csv:
import sqlite3
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData

meta = MetaData()
file = "extract_timelogs_may_july.xlsx"
output = "output.xlsx"

df = pd.read_excel(file)
dfc = df.to_csv("dfc.csv")
readcsv = pd.read_csv("dfc.csv")

dfr = readcsv.drop( labels=None, axis=0, index=None, columns="Unnamed: 0", level=None, inplace=False,errors='raise')

engine = create_engine('sqlite:///timelogs.db', echo = True)
meta = MetaData()

timelogs = Table('timelogs', meta,
Column('contractor', String(60)),
Column('date', String(60)),
Column('start', String(60)),
Column('end', String(60)),
Column('work_element', String(60)),
Column('calendar_month', String(60)),
Column('work_area', String(60)),
Column('client', String(60)),
Column('count_hours', String(60)),
Column('count_days', String(60)),
Column('client_rate', String(60)),
Column('client_charges', String(60)),
Column('invoice_month', String(60))
)
meta.create_all(engine)

x = dfr.to_sql(name="timelogs", con=engine, if_exists="append", index=False)

results = engine.execute("select * from timelogs")

final = pd.DataFrame(data=results, columns=dfr.columns)
final

[jkrobicki]

Final code for Prefect Flow:

import sqlite3
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData
import prefect
from prefect import task, Flow
engine = create_engine('sqlite:///timelogs.db', echo = True)

@task
def read_excel(file):
df = pd.read_excel(file)
return df
@task
def to_csv(df):
dfc = df.to_csv("dfc.csv")
return dfc
@task
def read_csv():
readcsv = pd.read_csv("dfc.csv")
dfr = readcsv.drop( labels=None, axis=0, index=None, columns="Unnamed: 0", level=None, inplace=False,errors='raise')
return dfr

@task
def create_table():
meta = MetaData()

timelogs = Table('timelogs', meta,
    Column('contractor', String(60)),
    Column('date', String(60)),
    Column('start', String(60)),
    Column('end', String(60)),             
    Column('work_element', String(60)),
    Column('calendar_month', String(60)),                
    Column('work_area', String(60)),            
    Column('client', String(60)),            
    Column('count_hours', String(60)),            
    Column('count_days', String(60)),
    Column('client_rate', String(60)),        
    Column('client_charges', String(60)),
    Column('invoice_month', String(60))             
            )
table = meta.create_all(engine)

@task
def to_sql(dfr):
tosql = dfr.to_sql(name="timelogs", con=engine, if_exists="append", index=False)
return tosql

@task
def read_table():
table = pd.read_sql_table("timelogs", con=engine)
return table

with Flow("excel_to_database") as flow:
read_excel = read_excel("extract_timelogs_may_july.xlsx")
to_csv = to_csv(read_excel)
read_csv = read_csv()
create_table()
to_sql = to_sql(read_csv)
read_table()
flow.visualize()
flow.run()

print(tabulate(df, headers = 'keys', tablefmt = 'psql'))