Search code examples
pythonsql-serversqlalchemy

Updating tables using SQLAlchemy failing


I have a connection to SQL Server with:

import pyodbc
import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine

server = 'server'
database = 'db'
driver = 'driver'
database_con = f'mssql://@{server}/{database}?driver={driver}'

engine = create_engine(database_con)
con = engine.connect()

I want to test inserting a data record, so create a df and run it with:

df = pd.DataFrame({'column1':['test'], 'column2':[234],  'column3':[234.56]})
df.to_sql(
            name='A_table',
            con=con,
            if_exists="append",
            index=False
        )

This runs ok, verified by reading and viewing the table wtih:

query = 'select * from A_table'
data = pd.read_sql_query(query, con)
data

enter image description here

My issue arises when my colleague tries to view the data in SQL Server.

Running select * from A_table produces no result, after computing for >10min (it is only 3 rows and 3 columns)

Is there something I am missing. Does the update need to be committed to the server, or is there another way to ensure that when my colleague views the table they can see it without waiting for a long time.


Solution

  • The [Connection] object features “autobegin” behavior, such that any call to Connection.execute() will unconditionally start a transaction which can be controlled using the above mentioned Connection.commit() and Connection.rollback() methods.

    https://docs.sqlalchemy.org/en/14/core/future.html#sqlalchemy.future.Connection

    So run con.commit() to commit the changes.