How to execute pure SQL query in Python

Question

I am trying to just create a temporary table in my SQL database, where I then want to insert data (from a Pandas DataFrame), and via this temporary table insert the data into a 'permanent' table within the database.

So far I have something like

""" Database specific... """

import sqlalchemy
from sqlalchemy.sql import text

dsn = 'dsn-sql-acc'
database = "MY_DATABASE"

connection_str =    """
                            Driver={SQL Server Native Client 11.0};
                            Server=%s;
                            Database=%s;
                            Trusted_Connection=yes;
                    """ % (dsn,database)

connection_str_url = urllib.quote_plus(connection_str)
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % connection_str_url, encoding='utf8', echo=True)

# Open connection

db_connection = engine.connect()

sql_create_table = text("""          
                            IF OBJECT_ID('[MY_DATABASE].[SCHEMA_1].[TEMP_TABLE]', 'U') IS NOT NULL
                            DROP TABLE [MY_DATABASE].[SCHEMA_1].[TEMP_TABLE];

                            CREATE TABLE [MY_DATABASE].[SCHEMA_1].[TEMP_TABLE] (
                                [Date] Date,
                                [TYPE_ID] nvarchar(50),
                                [VALUE] nvarchar(50)
                                );


                     """)

db_connection.execute("commit")                  
db_connection.execute(sql_create_table)

db_connection.close()

The "raw" SQL-snippet within sql_create_table works fine when executed in SQL Server, but when running the above in Python, nothing happens in my database...

What seems to be the issue here?

Later on I would of course want to execute

BULK INSERT [MY_DATABASE].[SCHEMA_1].[TEMP_TABLE]
FROM '//temp_files/temp_file_data.csv'
WITH (FIRSTROW = 2,  FIELDTERMINATOR = ',', ROWTERMINATOR='\n');

in Python as well...

Thanks

haha thanks @StevenRumbalski. Sometimes one is too blind to see.. How do I accept your answer when all you needed was a comment!? — gussilago
– gussilago, Commented Feb 8, 2018 at 15:48

Steven Rumbalski · Accepted Answer · 2018-02-08 15:59:20Z

2

These statements are out of order:

db_connection.execute("commit")                  
db_connection.execute(sql_create_table)

Commit after creating your table and your table will persist.

answered Feb 8, 2018 at 15:59

Steven Rumbalski

45.7k10 gold badges96 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute pure SQL query in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related