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
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.
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.