trying to insert pandas with alchemy got error fetchall

Question

I'm trying to insert pandas into SQL Server table with chunksize but I got this error.

The error

Traceback (most recent call last): File "C:\Users eyma\PycharmProjects\pythonProject23\main.py", line 95, in for row in conn.fetchall(): AttributeError: 'Connection' object has no attribute 'fetchall'

and this my code

 connection_url = URL.create(
        "mssql+pyodbc",
        query={"odbc_connect": connection_string}
    )
    engine = create_engine(connection_url)
    engine = create_engine(connection_url)
    conn = engine.connect().execution_options(
        stream_results=True)
    
    
    
    with open("ss.xml") as fp:
        soup = BeautifulSoup(fp, 'xml')
    data = []
    
            'DSName': e.text if (e := Event.select_one(('Data[Name="DSName"]'))) else None,
            'DSType': e.text if (e := Event.select_one(('Data[Name="DSType"]'))) else None,
            'ObjectDN': e.text if (e := Event.select_one(('Data[Name="ObjectDN"]'))) else None,
            'ObjectGUID': e.text if (e := Event.select_one(('Data[Name="ObjectGUID"]'))) else None,
            'ObjectClass': e.text if (e := Event.select_one(('Data[Name="ObjectClass"]'))) else None,
            'AttributeLDAPDisplayName': e.text if (e := Event.select_one(('Data[Name="AttributeLDAPDisplayName"]'))) else None,
            'AttributeSyntaxOID': e.text if (e := Event.select_one(('Data[Name="AttributeSyntaxOID"]'))) else None,
            'AttributeValue': e.text if (e := Event.select_one(('Data[Name="AttributeValue"]'))) else None,
            'OperationType': e.text if (e := Event.select_one(('Data[Name="OperationType"]'))) else None,
    
    
        })
    
    df = pd.DataFrame(data);
    engine.execute('''
    
                                CREATE TABLE try(
                                    DSName nvarchar(max),
                                    DSType nvarchar(max),
                                    ObjectDN nvarchar(max),
                                    ObjectGUID nvarchar (max),
                                    AttributeLDAPDisplayName nvarchar(max),
                                    AttributeSyntaxOID nvarchar(max),
                                    AttributeValue nvarchar(max),
                                    OperationType nvarchar(max),
                                    )
                                       ''')
    df.to_sql('try', conn, if_exists='replace', index = False,chunksize=100)
    engine.execute('''  
    SELECT * FROM test
              ''')
    
    for row in conn.fetchall():
        print (row)

user_1885713 · Answer

Your mistake seems to be in the use of fetchall(). As the error tells you the conn object does not support fetchall() and that's true. It is the query results that contain/support this function as shown here.

Change the last loop to

result = engine.execute('''  
SELECT * FROM test
          ''')

 for row in result.fetchall():
        print (row)

Alternatively you can try:

for row in engine.execute('''SELECT * FROM test''').fetchall()
    print(row)

Edit: A version that will work with versions >2.0 of SQLAlchemy is

with engine.begin() as conn:
    result = conn.exec_driver_sql("SELECT * FROM test").all()

Thanks to Gord Thompson for highlighting this in the comments and some more details for the SQLAlchemy part can be found here.

trying to insert pandas with alchemy got error fetchall

1 Answers