Transfering Data in MS Access Using Python

Question

I have an ever growing and changing database that reflects a permits passed by the State and EPA. As the database changes and updates I need to transfer the relevant information.

The script does two things; first it checks which fields are the same and creates a list of fields and data that will be inserted into the new database. Second to insert the data into the new database.

Problem is I cannot get it to insert. I have matched everything like it says online in various ways but i get error ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)').

I cannot figure out how to prevent it.

Code:

import pyodbc

importDatabase = r"J:\ENVIRO FIELD\AccessDatabases\MS4\MS4 Town Databases\~Template\MS4_Apocalypse Import DEV 1.accdb"

"Create the Import Database Connection"
connectionImport = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' %(importDatabase))
cursorImport = connectionImport.cursor()


"####---Outfall Section---####"

"Import the outfall names into the new database"

tbl = "tbl_Outfall_1_Profile"

exportList = []
importList = []
for row in cursorImport.columns(table = "tblExportMigration_Outfall_1_Profile"):
    field = row.column_name
    exportList.append(field)

for row in cursorImport.columns(table = "tbl_Outfall_1_Profile"):
    field = row.column_name
    importList.append(field)

matchingList = []
for field in exportList:
    if field != "outfallID":
        if field in importList:
            matchingList.append(field)
    else:
        continue

sqlValue = ""
for field in matchingList:
    sqlValue += "[%s], " %(field)

sqlValue = sqlValue[:-2]
sql = "SELECT %s from %s" %(sqlValue, "tblExportMigration_Outfall_1_Profile")

for rowA in cursorImport.execute(sql):
    tupleList = list(rowA)
    tupleList = ["" if i == None else i for i in tupleList]
    tupleValues = tuple(tupleList)
    sqlUpdate = """INSERT INTO tbl_Outfall_1_Profile (%s) Values %s;""" %(sqlValue, tupleValues)
    cursorImport.execute(sqlUpdate)

cursorImport.close()

This is the sql string I create

"INSERT INTO tbl_Outfall_1_Profile ([profile_OutfallName], [profile_HistoricalName1], [profile_HistoricalName2], [profile_HistoricalName3], [profile_HistoricalName4]) Values ('756', '', '', '', '');"

First off, don't use string substitution ("dynamic SQL") to insert column values. Use a proper parameterized query instead, e.g., sqlUpdate = "INSERT INTO tbl_name (⁦col1, col2) Values (?, ?);" followed by cursorImport.execute(sqlUpdate, (param1, param2)). Do a web search for "Little Bobby Tables" for more information. — Gord Thompson
– Gord Thompson, Commented Jul 27, 2021 at 12:53
@GordThompson I cannot just hard wire the information into the query. I could create a section of the script to generate the ?'s. However the tables field names have a high potential of being updated and changed due to the nature of the permit. I have to account for this in the system or there will be an issue further down the road. I did not create nor write the permit but I have to have the system reflect it. — Jake
– Jake, Commented Jul 27, 2021 at 13:26
So you may still need to use dynamic SQL for the column list since parameter substitution only works for column values, not column names, but you still should use parameters for the values. — Gord Thompson
– Gord Thompson, Commented Jul 27, 2021 at 15:39
@GordThompson Is this what you are referring to to do: sqlUpdate = "INSERT INTO tbl_Outfall_1_Profile ([profile_OutfallName], [profile_HistoricalName1], [profile_HistoricalName2], [profile_HistoricalName3], [profile_HistoricalName4]) Values (?,?,?,?,?);" cursorImport.execute(sqlUpdate, (tupleValues)) — Jake
– Jake, Commented Jul 27, 2021 at 16:36

Jake · Accepted Answer · 2021-07-30 13:46:03Z

1

Taking what @Gord Thompson said I was actually able to create a dynamic parameter flow

First created a module to create the ?

def Defining_Paramters(length):
    parameterString = ""
for x in range(1,length):
    parameterString += "?, "
parameterString += "?"

return parameterString

Then stuck it into the string for the sql update

sqlUpdate = sqlUpdate = "INSERT INTO %s (%s) Values (%s);" %(table, sqlFrameworkSubStr, parameters)

Run the cursor and commit it

cursorTo.execute(sqlUpdate, (dataTuple))
connectionTo.commit()

It would seem that you have to create the query in its entirety then have your data in tuple format for entry

answered Jul 30, 2021 at 13:46

Jake

3138 silver badges22 bronze badges

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

Comments

Gustav · Accepted Answer · 2021-07-27 12:52:07Z

0

This is the sql string [I think] I create

Try this:

sqlUpdate = """INSERT INTO tbl_Outfall_1_Profile (%s) Values (%s);""" %(sqlValue, tupleValues)

or perhaps:

sqlUpdate = "INSERT INTO tbl_Outfall_1_Profile (%s) Values (%s);" %(sqlValue, tupleValues)

answered Jul 27, 2021 at 12:52

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

3 Comments

Jake Over a year ago

I tried it out but still get the 42000 Error

Gustav Over a year ago

Perhaps you can't insert empty strings Values ('756', '', '', '', ''). Most likely, they should be Null.

Gord Thompson Over a year ago

( cc: @Jake ) - That would be a different error: "[HY000] [Microsoft][ODBC Microsoft Access Driver] Field 'Donor.DonorName' cannot be a zero-length string. (-3702) (SQLExecDirectW)"

Collectives™ on Stack Overflow

Transfering Data in MS Access Using Python

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related