I am writing a python script to copy MSaccess tables to Postgres. In this particular case, I'm trying to specify the schema that is being loaded in the Postgres. Most code I found here on SO just loads in generic public. I need to load specific schemas.

a = win32com.client.Dispatch("Access.Application")
a.OpenCurrentDatabase(db_path)

table_list = []

for table_info in cursor.tables(tableType='TABLE'):
    table_list.append(table_info.table_name)

print (table_list)

for table in table_list:
    logging.info(f"Exporting: {table}")

    acExport = 1
    acTable = 0

    a.DoCmd.TransferDatabase(
        acExport, 
        "ODBC Database", 
        "ODBC;DSN=PostgreSQL30;"
        f"DATABASE={db_name};"
        f"UID={pg_user};"
        f"PWD={pg_pwd};"
        f"Schema=Commercial;", 
        acTable, 
        f"{table}", 
        f"{table.lower()}"
        )

   
    logging.info(f"Finished Export of Table: {table}")
    logging.info("Creating empty table in EGDB based off of this")

My issue with this is that while I have tried Schema=Commercial and f"Commercial.{table.lower()}", the tables always land in the public schema. how do I tell the command to export to the correct schema?

Thanks

I am writing a python script to copy MSaccess tables to Postgres. In this particular case, I'm trying to specify the schema that is being loaded in the Postgres. Most code I found here on SO just loads in generic public. I need to load specific schemas.

a = win32com.client.Dispatch("Access.Application")
a.OpenCurrentDatabase(db_path)

table_list = []

for table_info in cursor.tables(tableType='TABLE'):
    table_list.append(table_info.table_name)

print (table_list)

for table in table_list:
    logging.info(f"Exporting: {table}")

    acExport = 1
    acTable = 0

    a.DoCmd.TransferDatabase(
        acExport, 
        "ODBC Database", 
        "ODBC;DSN=PostgreSQL30;"
        f"DATABASE={db_name};"
        f"UID={pg_user};"
        f"PWD={pg_pwd};"
        f"Schema=Commercial;", 
        acTable, 
        f"{table}", 
        f"{table.lower()}"
        )

   
    logging.info(f"Finished Export of Table: {table}")
    logging.info("Creating empty table in EGDB based off of this")

My issue with this is that while I have tried Schema=Commercial and f"Commercial.{table.lower()}", the tables always land in the public schema. how do I tell the command to export to the correct schema?

Thanks

• python
• postgresql
• ms-access

• This question is similar to: How to use pyodbc to migrate tables from MS Access to Postgres?. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – June7 Commented Jan 18 at 19:33
• I tried to edit the question.....but it was rejected. I need to specify which schema the drop goes to when loading into Postgres. Any ideas? Thanks! – arcee123 Commented Jan 21 at 3:20
• Why was your edit rejected? It's your question, you should be able to do edit. – June7 Commented Jan 21 at 3:38
• said it deviated from original post. I overridden the recommendation. still need the help. Thanks – arcee123 Commented Jan 21 at 14:55
• Try specifying the target table as f"Commercial.{table.lower()}"? – MatBailie Commented Jan 21 at 15:04

1 Answer 1

This works for me in Access VBA:

Sub pg_export()
    Dim connect As String
    connect = _
        "ODBC;" & _
        "DRIVER=PostgreSQL Unicode(x64);" & _
        "SERVER=192.168.0.199;" & _
        "DATABASE=test;" & _
        "ConnSettings=SET search_path = ""Commercial"";" & _
        "UID=scott;" & _
        "PWD=tiger;"
    DoCmd.TransferDatabase acExport, "ODBC Database", connect, acTable, "my_table", "my_table"
End Sub

The Python equivalent for assigning the connect variable would be

    connect = (
        'ODBC;'
        'DRIVER=PostgreSQL Unicode(x64);'
        'SERVER=192.168.0.199;'
        'DATABASE=test;'
        'ConnSettings=SET search_path = "Commercial";'
        'UID=scott;'
        'PWD=tiger;'
    )