0

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()
Parfait
  • 104,375
  • 17
  • 94
  • 125
Sergio
  • 43
  • 1
  • 6
  • First MDB file means an Access database. Why are you using Pandas for this? Is there really a table named `MITSF_1.dbo.MeasurementsTest`? – Parfait Dec 23 '20 at 21:27
  • Review https://datatofish.com/sql-to-pandas-dataframe/ – June7 Dec 23 '20 at 22:01
  • Does this answer your question? [How to convert SQL Query result to PANDAS Data Structure?](https://stackoverflow.com/questions/12047193/how-to-convert-sql-query-result-to-pandas-data-structure) – June7 Dec 23 '20 at 22:02
  • Hello @Parfait, yes I understand that MDB is Access database, what I'm trying to do is to consolidate all the daily information generated by our equipment's in Access to my SQL database, yes, there is a table inside my SQL database named 'MITSF_1.dbo.MeasurementsTest' – Sergio Dec 23 '20 at 22:55
  • Access runs SQL. So it is an SQL database. Did you mean SQL Server database? Specifically is that table a linked table in MS Access that points to an SQL Server database? – Parfait Dec 23 '20 at 23:25

1 Answers1

0

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:tiger@192.168.0.199/MITSF_1"
    "?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True,
)
df.to_sql("MeasurementsTest", engine, schema="dbo", 
    index=False, if_exists="append",
)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Hello, its giving me an error: InterfaceError: (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'scott'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'scott'. (18456)") – Sergio Dec 29 '20 at 16:51
  • Umm, `scott:tiger` are placeholder values for `your_username:your_password`. Substitute your own values as needed. – Gord Thompson Dec 29 '20 at 17:07