admin管理员组文章数量:1321070
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
Share Improve this question edited Jan 21 at 14:54 CommunityBot 11 silver badge asked Jan 17 at 20:59 arcee123arcee123 24314 gold badges57 silver badges137 bronze badges 7 | Show 2 more comments1 Answer
Reset to default 3This 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;'
)
本文标签: pythonHow to specify a schema in a DoCmdTransferDatabase commandStack Overflow
版权声明:本文标题:python - How to specify a schema in a DoCmd.TransferDatabase command - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742091542a2420296.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
f"Commercial.{table.lower()}"
? – MatBailie Commented Jan 21 at 15:04