Python - how to make SQL "Create Table" statement based on DF and data in it?

Question

I loaded data (from CSV or Excel) into Python's Panda DF.

import pandas as pd
df = pd.read_csv('table1.csv')

Data looks like this:

c1,c2,c3
1,abc,1.5
2,bcd,2.53
3,agf,3.571

How to make SQL create table statement based on data in a dataframe which in this example would be:

create table table1 (c1 int, c2 varchar(3), c3 float);

Thanks.

Answer 1

You can add loops to this for any additional datatypes you are dealing with. The result should be a create table statement including your column names and datatypes.

#Get rid of invalid characters in the column names
Col_list = []
for i in range(df.shape[1]):   
    Col_Name = df.columns[i].replace(" - ", "_").replace(" ", "_").replace("&", "and") # Some characters were unacceptable
    Col_list.append(Col_Name)
df.columns = Col_list


#This function creates a create table statement out of my dataframe columns and data types
Text_list = []
for i in range(df.shape[1]):   
    Col_Name = df.columns[i]
    Python = df.convert_dtypes().dtypes[i]  # Most of the data types were listed as object so this reassigns them
    if Python == float:
        Oralce = "FLOAT"
    elif Python == 'datetime64[ns]':
        Oracle = 'DATE'
    elif Python == 'Int64':
        Oracle = "NUMBER"
    else:
        Oracle = "VARCHAR2(50)"
    Text_list.append(Col_Name)
    Text_list.append(' ')
    Text_list.append(Oracle)
    if i < (df.shape[1] - 1): 
        Text_list.append(", ")
Text_Block = ''.join(Text_list)
Text_Block

cursor = conn.cursor()

drop = """
drop TABLE tableName
"""
create = """
    CREATE TABLE tableName (
    {}    )
""".format(Text_Block)

cursor.execute(drop)
cursor.execute(create)
cursor.execute("commit")
cursor.close()