0

I'm trying to add data frame information to PostgreSQL's table.

But I don't know how to create an engine. It's very likely related to connection to SQL server, but I could not figure out how to create it.

The example I saw on the to_sql document is:

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite://', echo=False)

And what I tried is:

param_dic = {'host': 'localhost', 'database':'databasename', 'user':'username', 'password':'password' }
conn = pgsql.connect(**param_dic)
cursor = conn.cursor()

for filename in work_dir.glob('excel_filename.xlsx'):
    df = pd.read_excel(filename)
    df.to_sql('table name', conn, if_exists='replace')
    conn.commit()
    cursor.close()
Dong-gyun Kim
  • 411
  • 5
  • 23
  • Those look to be two separate modes of connection. It is not clear where ```pgsql``` is coming from. For the ```create_engine``` portion see [Postgres](https://docs.sqlalchemy.org/en/13/core/engines.html#postgresql) – Adrian Klaver Aug 10 '20 at 20:39

1 Answers1

1
engine = create_engine('sqlite://', echo=False)

this seems incomplete. normally you'd put the connection string here, not just "sqlite".

I guess what you're trying to do is

# note, put your actual credentials here
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=False)

you can then create a connection based on this engine, like

with engine.connect() as con:
    # etc...
    df.to_sql('table name', conn, if_exists='replace')
    # etc...

or just con = engine.connect()

c8999c 3f964f64
  • 1,430
  • 1
  • 12
  • 25
  • Actually I already solved my problem myself. But I directly put the engine to df.to_sql like df.to_sql('table name', engine, if_exists = 'replace'). What's the real purpose using engine.connet()? – Dong-gyun Kim Aug 12 '20 at 13:14
  • engine.connect() just creates a connection. apparently df.to_sql() accepts an engine as a parameter, so you really dont need a connection. idk how that works but I wouldnt have suggested using a connection anyway if you didnt have it in your example. – c8999c 3f964f64 Aug 13 '20 at 07:50