I can successfully query and insert data using sqlalchemy and pandas:
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('mssql://myserver/mydb?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes')
Read tempy table:
sql_command = """
select top 100 * from tempy
"""
df = pd.read_sql(sql_command, engine)
print df
tempID tempValue
0 1 2
Append new data:
df_append = pd.DataFrame( [[4,6]] , columns=['tempID','tempValue'])
df_append.to_sql(name='tempy', con=engine, if_exists = 'append', index=False)
df = pd.read_sql(sql_command, engine)
print df
tempID tempValue
0 1 2
1 4 6
Try to truncate data:
connection = engine.connect()
connection.execute( '''TRUNCATE TABLE tempy''' )
connection.close()
Read table again, but truncate failed:
df = pd.read_sql(sql_command, engine)
print df
tempID tempValue
0 1 2
1 4 6
sqlalchemy, a db connection module for Python, uses SQL Authentication (database-defined user accounts) by default. If you want to use your Windows (domain or local) credentials to authenticate to the SQL Server, the connection string must be changed.
Pandas in Python uses a module known as SQLAlchemy to connect to various databases and perform database operations.
Update table elements in SQLAlchemy. Get the books to table from the Metadata object initialized while connecting to the database. Pass the update query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.
I have the same problem with pandas
0.19.2 and sqlalchemy 1.1.5
.
As I see autocommit
is not forced in engine.execute()
when running a TRUNCATE
statement. If I force it manually then TRUNCATE
works perfectly:
from sqlalchemy.sql import text as sa_text
engine.execute(sa_text('''TRUNCATE TABLE tempy''').execution_options(autocommit=True))
It's fancy that DROP
works perfectly without forcing autocommit
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With