Import data directly from google sheets

excel_import.py

from os.path import join, dirname, abspath
import psycopg2
import psycopg2.extras
import xlrd

xlrd.xlsx.ensure_elementtree_imported(False, None)
xlrd.xlsx.Element_has_iter = True

fname = join(dirname(dirname(abspath(__file__))), 'dir_name', 'file_name.xlsx')

xl_workbook = xlrd.open_workbook(fname)

sheet_names = xl_workbook.sheet_names()
print('Sheet Names', sheet_names)

tables_names = {'schema_name.short_table_name': 'schema_name.short_table_name', 
                'schema_name.very_long_table_name_max': 'schema_name.very_long_table_name_max_24_char'}

conn_string = "postgresql://vinkrish:password@localhost/cal"
conn = psycopg2.connect(conn_string)
# conn = psycopg2.connect("host=host_name dbname=db_name user=postgres@some_db password=some_password")
cur = conn.cursor()

for name in sheet_names:
    xl_sheet = xl_workbook.sheet_by_name(name)
    table_name = tables_names.get(xl_sheet.name)
    if not table_name:
        continue

    num_cols = xl_sheet.ncols

    cols_names = [xl_sheet.cell(0, i).value for i in range(num_cols)]
    cols_names_tuple = tuple(cols_names)
    cols_names_str = ",".join([str(s) for s in list(cols_names_tuple)])
    
    for row_idx in range(1, xl_sheet.nrows):
        cols_values = [xl_sheet.cell(row_idx, i).value for i in range(num_cols)]
        cols_value_tuple = tuple(cols_values)
        print(cols_value_tuple)
        query = f"insert into {table_name}({cols_names_str}) values{cols_value_tuple}"
        print(query)
        cur.execute(query)
        
    conn.commit()

cur.close()
conn.close()