我应该使用 pyodbc 还是 win32com 使用 python 填写 Microsoft Access 数据库 (.accdb) 中预先存在的表单? [英] Should I use pyodbc or win32com to fill out pre-existing forms in a Microsoft Access Database (.accdb) using python?

问题描述

我写了一个从word文档中提取字符串的python脚本.目标是然后将这些字符串插入到 Access 数据库中预先存在的表单中，保存它，创建表单的副本，从下一个 word 文档插入不同的字符串，保存，重复.该表单已包含我需要的所有字段，并且具有用于保存"和创建重复项"的按钮.

我在弄清楚如何在 Access 表单中插入字符串时遇到问题.

到目前为止，我了解到至少有两种方法可以做到这一点，使用 pyodbc 或 win32com.

我使用了以下链接中的代码:

pyodbc - https://datatofish.com/how-to-connect-python-to-ms-access-database-using-pyodbc/

win32com - 写入 MS Access 表，python win32com

我解决了将 64 位 python 连接到 32 位访问的问题，并且我能够使用 win32com 和 pyodbc 连接到我的 accdb 文件，但就我所知.

pyodbc:

导入pyodbcconn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\path\folder\my_database_file.accdb;')游标 = conn.cursor()

win32com:

导入 win32com.client# ADODB 常量adVarWChar = 202广告整数 = 3广告参数输入 = 1connection = win32com.client.Dispatch(r'ADODB.Connection')DSN = (r'PROVIDER=Microsoft.ACE.OLEDB.12.0;'r'数据源=C:\path\folder\my_database_file.accdb;')连接.打开(DSN)cmd = win32com.client.Dispatch(r'ADODB.Command')cmd.ActiveConnection = 连接

pyodbc 或 win32com 哪种方法更好/更容易使用?

此外，一旦我选择了一种方法，我该如何继续?我无法找到有关如何使用这些方法在 Access 中填写表单的文档.我发现的大部分内容是关于如何在 Access 中填写表格，但我不确定如何转录该代码以使用表单，或者该代码最初来自何处.

我没有包含从 word 文档中提取字符串的代码，这部分工作正常，并且不是问题的真正部分，只是背景信息.

我们将不胜感激任何正确方向的建议或指示.

解决方案

首先，MS Access 窗体和报表不存储任何数据，而是在运行时应用与表或查询数据的可视交互.

其次，您的问题显示了 MS Access 如何是 多方面的东西，既是 GUI 应用程序又是数据库.因此，您的两种方法(win32com 和 pyodbc)有些明显不同且部分重叠.通常，有两种方式可以与该软件进行交互:

前端

1. 只需使用已安装的 Microsoft Office 软件 MSAccess.exe 和所有启用的用户界面功能来设计和使用其对象:表格、查询、表单、报告、宏和模块.

2. 通过编程代码，连接到 Microsoft通过外部客户端(例如 Excel VBA、Python 或任何其他与 COM 连接的语言或 API)访问对象库.

   在 Python 中，使用 win32com 允许您访问 MS Access 的 GUI 对象，其中包括表单和报告以及任何其他访问方法，例如 DoCmd.TransferSpreadsheet 或 Application.ImportXML.要运行查询，您需要访问 Access 应用程序对象的基础数据库.总体而言，这种方法需要在客户端计算机上安装成熟的 Office 应用程序 MSAccess.exe.

后端

• 连接到 .accdb(或 .mdb)文件的底层数据库，例如使用几乎任何现代通用语言的 ODBC 或 OLEDB带有相应库的 Python.

  在 Python 中，使用 pyodbc(或 adodbapi)只允许您与 Jet/ACE 数据库(Window .dll 文件)交互.您不能与任何 GUI 对象(包括表单和报告)交互，而只能与表和存储的查询交互，并且只能使用应用层调用的 SQL(这里是 Python).总的来说，这种方法不需要在客户端计算机上安装成熟的 Office 应用程序 MSAccess.exe.

---

话虽如此，对于您的特定需求，您可能不需要更长、更广泛的 win32com 前端方法，因为 Access 表单旨在输入/更新/删除基础表中的数据.换句话说，它们是用户友好的数据处理方式.因此，只需绕过用户界面需求，并使用 pyodbc(一种更简单的后端方法)将提取的 Word 数据直接导入表单后面的数据库表中.

具体来说，回答您的问题:

<块引用>

我编写了一个从 Word 文档中提取字符串的 Python 脚本.目标是然后将这些字符串插入到 Access 数据库中预先存在的表单中，保存它，创建表单的副本，从下一个 word 文档插入不同的字符串，保存，重复.

首先，除了数据之外，没有人应该复制实际的表单对象.相反，使用 cursor 对象和参数化将数据插入表单后面的数据源:

# APPEND QUERY WITH PARAMETERSsql = """ INSERT INTO myTableBehindmyForm (Field1, Field2, Field3, ...)值 (?, ?, ?, ...)"# 使用绑定值元组执行查询cursor.execute(sql, (word_string1, wordstring2, wordstring3, ...))

<块引用>

该表单已包含我需要的所有字段，并且具有用于保存"和创建副本"的按钮.我在弄清楚如何在 Access 表单中插入字符串时遇到问题.

要保存，只需提交上述查询并复制，重复cursor.execute 调用:

# EXECUTE QUERY WITH TUPLE OF BINDED VALUEScursor.execute(sql, (word_string1, wordstring2, wordstring3, ...))conn.commit()# 使用绑定值元组执行查询cursor.execute(sql, (word_string1, wordstring2, wordstring3, ...))cursor.execute(sql, (word_string1, wordstring2, wordstring3, ...))conn.commit()

<块引用>

我发现的大部分内容是关于如何在 Access 中填写表格，但我不确定如何转录该代码以使用表单，或者该代码最初来自何处.

同样，不需要处理表单(无数据的 GUI 对象)，而只需处理表单背后的表格.因此，使用 pyodbc 或任何兼容的 MS Access 和 Python DB-API 来处理您的数据需求.

I wrote a python script that extracts strings from word documents. The goal is to then insert these strings into a pre-existing form in an Access database, save it, create a duplicate of the form, insert different strings from the next word document, save, repeat. The form already has all the fields I need, and it has buttons for 'save' and 'create duplicate.'

I'm having trouble figuring out how to insert strings into the Access form.

So far I've learned that there are at least two ways of doing this, using pyodbc or win32com.

I used code from the following links:

pyodbc - https://datatofish.com/how-to-connect-python-to-ms-access-database-using-pyodbc/

win32com - Write to MS Access table, python win32com

I solved the problem of connecting 64-bit python to 32-bit access, and I was able to connect to my accdb file using win32com and pyodbc, but that's as far as I got.

pyodbc:

import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\path\folder\my_database_file.accdb;')
cursor = conn.cursor()

win32com:

import win32com.client

# ADODB constants
adVarWChar = 202
adInteger = 3
adParamInput = 1

connection = win32com.client.Dispatch(r'ADODB.Connection')

DSN = (
    r'PROVIDER=Microsoft.ACE.OLEDB.12.0;'
    r'DATA SOURCE=C:\path\folder\my_database_file.accdb;'
    )

connection.Open(DSN)

cmd = win32com.client.Dispatch(r'ADODB.Command')
cmd.ActiveConnection = connection

Which approach is better/easier to work with, pyodbc or win32com?

Also, once I choose an approach, how do I continue? I'm having trouble finding documentation on how to fill out forms in Access using these methods. Most of what I find was on how to fill out tables in Access, and I'm not sure how to transcribe that code to work with forms instead, or where that code comes from in the first place.

I did not include the code that extracts strings from word documents, that part works fine, and isn't really part of the question, just background information.

Any advice or pointers in the right direction would be much appreciated.

解决方案

First and foremost, MS Access forms and reports do not store any data but apply visual interactivity with table or query data at runtime.

Secondly, your question shows how MS Access is the multifaceted thing that is both a GUI application and a database. Because of this your two approaches (win32com and pyodbc) are somewhat distinctly different and partially overlapping. Generally, there are two ways to interact with this software:

Front-End

1. Simply use the installed Microsoft Office software, MSAccess.exe, with all enabled user interface features to design and use its objects: tables, queries, forms, reports, macros, and modules.

2. Through programming code, interface to the Microsoft Access object library via an external client such as Excel VBA, Python, or any other COM-connected language or API.

   In Python, using win32com allows you to access the GUI objects of MS Access which includes forms and reports and any other Access methods like DoCmd.TransferSpreadsheet or Application.ImportXML. To run queries you need to access the underlying database of the Access application object. Overall, this approach requires the full-fledged Office application, MSAccess.exe, installed on client machines.

Back-End

• Connect to the underlying database of the .accdb (or .mdb) file such as with ODBC or OLEDB using practically any modern, general purpose language like Python with corresponding libraries.

  In Python, using pyodbc (or adodbapi) only allows you to interact with the Jet/ACE database (Window .dll files). You cannot interact with any GUI objects including forms and reports but only tables and stored queries and only using SQL called by application layer here being Python. Overall, this approach does NOT require the full-fledged Office application, MSAccess.exe, installed on client machines.

---

With that said, for your specific needs, you may not need the longer, more extensive win32com front-end approach since Access forms are designed to enter/update/delete data in underlying tables. In other words, they are user-friendly means to data handling. Therefore, simply circumnavigate the user interface need and directly import your extracted Word data into the database table behind the form with pyodbc, a much simpler, back-end method.

Specifically, to answer your questions:

I wrote a Python script that extracts strings from Word documents. The goal is to then insert these strings into a pre-existing form in an Access database, save it, create a duplicate of the form, insert different strings from the next word document, save, repeat.

First, no one should duplicate the actual form object but the data. Instead, insert data into the data source behind the form using the cursor object and parameterization:

# APPEND QUERY WITH PARAMETERS
sql = """INSERT INTO myTableBehindmyForm (Field1, Field2, Field3, ...)
         VALUES (?, ?, ?, ...)
      """

# EXECUTE QUERY WITH TUPLE OF BINDED VALUES
cursor.execute(sql, (word_string1, wordstring2, wordstring3, ...))

The form already has all the fields I need, and it has buttons for 'save' and 'create duplicate. I'm having trouble figuring out how to insert strings into the Access form.

To save, simply commit above query and to duplicate, repeat the cursor.execute call:

# EXECUTE QUERY WITH TUPLE OF BINDED VALUES
cursor.execute(sql, (word_string1, wordstring2, wordstring3, ...))
conn.commit()


# EXECUTE QUERY WITH TUPLE OF BINDED VALUES
cursor.execute(sql, (word_string1, wordstring2, wordstring3, ...))
cursor.execute(sql, (word_string1, wordstring2, wordstring3, ...))
conn.commit()

Most of what I find was on how to fill out tables in Access, and I'm not sure how to transcribe that code to work with forms instead, or where that code comes from in the first place.

Again, no need to work with forms (data-less GUI objects) but simply the tables behind forms. Therefore, go with pyodbc or any compliant MS Access and Python DB-API to handle your data needs.

这篇关于我应该使用 pyodbc 还是 win32com 使用 python 填写 Microsoft Access 数据库 (.accdb) 中预先存在的表单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！