AttributeError: 'Engine' object has no attribute 'conn'

Question

So I am trying to create an auto update to SQL from another excel file, by unique value, as to know what is the new data to add to the database..

There's different in columns names between the database and the excel file as in the database and names without spaces...

I tried to do it with pandas it gave me the same error

So here's my simple code tried with xlrd

import xlrd
from sqlalchemy import create_engine


def insert():

    book = xlrd.open_workbook(r"MNM_Rotterdam_5_Daily_Details-20191216081027 - Copy (2).xlsx")
    sheet = book.sheet_by_name("GSM Details")

    database = create_engine(
    'mssql+pyodbc://WWX542337CDCD\SMARTRNO_EXPRESS/myDB?driver=SQL+Server+Native+Client+11.0')  # name of database


    cnxn = database.raw_connection
    cursor = cnxn.cursor()




    query = """Insert INTO [myDB].[dbo].[mnm_rotterdam_5_daily_details-20191216081027] (Date, SiteName, CellCI, CellLAC, CellName, CellIndex) values (?,?,?,?,?,?)"""


    for r in range(1, sheet.nrows):
        date = sheet.cell(r,0).value
        site_name = sheet.cell(r,3).value
        cell_ci = sheet.cell(r,4).value
        cell_lac = sheet.cell(r,5).value
        cell_name = sheet.cell(r,6).value
        cell_index = sheet.cell(r,7).value


        values = (date, site_name, cell_ci, cell_lac, cell_name, cell_index)


        cursor.execute(query, values)
        cnxn.commit()

# Close the cursor
    cursor.close()

# Commit the transaction
    database.commit()

# Close the database connection
    database.close()

# Print results
    print ("")
    print ("")
    columns = str(sheet.ncols)
    rows = str(sheet.nrows)
    print ("Imported", columns,"columns and", rows, "rows. All Done!")
insert()

and this is the error:

I tried to change the range I found another error:

Traceback (most recent call last):
  File "D:/Tooling/20200207/uniquebcon.py", line 48, in <module>
    insert()
  File "D:/Tooling/20200207/uniquebcon.py", line 37, in insert
    database.commit()
AttributeError: 'Engine' object has no attribute 'commit'

I think this is related to SQL-Alchemy in the connection

Answer 1

Instead of creating the cursor directly with

cursor = database.raw_connection().cursor()

you can create a connection object, then create the cursor from that, and then call .commit() on the connection:

cnxn = database.raw_connection()
crsr = cnxn.cursor()
# do stuff with crsr ...
cnxn.commit()