python - Unable to create data frame from MS Access query results

Question

I'm trying to insert information from an MS Access database MDB file, unfortunately I don't know how to delimitate the columns from the database table with Python.

I'm getting the error

ValueError: Shape of passed values is (109861, 1), indices imply (3,1)

and the code I'm using is:

import os
import shutil
import pyodbc
import pandas as pd
import csv
from datetime import datetime
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\sguerra\Desktop\Python\Measurements-2020-12-15.mdb;')
cursor = conn.cursor()
cursor.execute('select * from Measurements')
new = cursor.fetchall()
columns = ['Prod_Date','Prod_Time','CCE_SKU']
df = pd.DataFrame(new,columns)

for row in df.itertuples():
    cursor.execute('''
                   insert into MITSF_1.dbo.MeasurementsTest ([Prod_Date],[Prod_Time],[CCE_SKU])
                   VALUES (?,?,?)
                   ''',
                   row.Prod_Date,
                   row.Prod_Time,
                   row.CCE_SKU
                   )
conn.commit()

Answer 1

You are using the same cursor to try and execute both the select and the insert, so both of those statements would be operating on the same database. To keep things simple, you should use pandas' read_sql_query() to read the required columns from Access and then use to_sql() to write them to SQL Server:

df = pd.read_sql_query(
    "SELECT [Prod_Date],[Prod_Time],[CCE_SKU] FROM Measurements",
    conn,
)
from sqlalchemy import create_engine
engine = create_engine(
    "mssql+pyodbc://scott:[email protected]/MITSF_1"
    "?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True,
)
df.to_sql("MeasurementsTest", engine, schema="dbo", 
    index=False, if_exists="append",
)