Loop through Microsoft Access queries with Python

Question

I need to loop through some queries of a Microsoft Access database (mdb). Is there a possibility to do so with Python? (I am not familiar with Python.)

I was thinking of creating a list with the query names and then loop through it.

So far I have this:

# Import system modules (ArcGIS, Excel, Microsoft Access)
import arcpy
from arcpy import env
import csv
import pyodbc

# Set workspace

arcpy.env.workspace = r"\\mars\Skript\Connection to ERDE.XYZ.XX.sde"
MDB = r"\\mars\Konzept\auswertung_gdm.mdb"

I am sure that there is plenty of information on how to work with an Access database with Python, so do some research. You don't need to create a table to list queries. Access already does that. Select [Name] FROM MSysObjects WHERE Type = 5. — AVG
– AVG, Commented Sep 28, 2016 at 13:03

Gord Thompson · Accepted Answer · 2016-09-28 13:54:56Z

1

I was thinking of creating a list with the query names and then loop through it.

That's certainly possible. For example.

query_names = ['Query1', 'Query2']
for query_name in query_names:
    sql = "SELECT * FROM [{}]".format(query_name)
    print(sql)

prints

SELECT * FROM [Query1]
SELECT * FROM [Query2]

For your code the loop could execute each SELECT statement using a pyodbc cursor ...

conn = pyodbc.connect(your_connection_string)  # e.g. "DRIVER=...;DBQ=...;"
crsr = conn.cursor()
crsr.execute(sql)
# do stuff with the results
crsr.close()
conn.close()

... retrieving the query results (via crsr.fetchall() or crsr.fetchone()) and processing them as required.

edited Sep 28, 2016 at 13:54

answered Sep 28, 2016 at 13:43

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user20416 Over a year ago

Is pyodbc a better choice than ACE and win32com?

Gord Thompson Over a year ago

I'd say that yes, pyodbc would be "better" most of the time since it would provide a mechanism that is DBAPI compliant and therefore more familiar to Python users. By contrast, using win32com would presumably involve creating Recordset objects and looping through them with .MoveNext, rather than "the DBAPI way" of creating cursor objects and using .fetchone(), .fetchall(), etc..

user7075507 · Accepted Answer · 2016-12-22 16:34:21Z

If you have Access installed, why not just use VBA for this taks?

Public Sub GetMyObjectsList()
Dim db As Database
Dim Qry As QueryDef
Dim QryNames As String
Dim QryCount As Integer
Dim Tbl As TableDef
Dim TblNames As String
Dim TblCount As Integer
Set db = CurrentDb
QryCount = 0
TblCount = 0
For Each Qry In db.QueryDefs
 QryNames = QryNames & Qry.Name & ", "
 QryCount = QryCount + 1
Next
For Each Tbl In db.TableDefs
 If Tbl.Attributes = 0 Then 'Ignores System Tables
 TblNames = TblNames & Tbl.Name & ", "
 TblCount = TblCount + 1
 End If
Next
QryNames = QryNames & "TOTAL Query Count: " & QryCount
TblNames = TblNames & "TOTAL Table Count: " & TblCount
MsgBox QryNames
MsgBox TblNames
db.Close
Set db = Nothing
End Sub

Or, try this.

http://www.consultdmw.com/access-VBA-list-objects.htm

Collectives™ on Stack Overflow

Loop through Microsoft Access queries with Python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related