SQL Alchemy does not execute queries

February 18, 2024

I am using SQLAlchemy to run a query in SQL Server.
The query is supposed to create a table.
The below example is meant to be easily reproducable for you.
It does not contain the actual queries I use.

If I run the following code no table gets created. No error or message of any kind is returned.
(Please assume the URL_OBJECT is created properly)

from sqlalchemy import create_engine

engine = create_engine(URL_OBJECT, fast_executemany=True)

with engine.connect() as connection:
    connection.execute("Select 1 as an_integer into database.schema.test_table")

–> If the table "test_table" already exists I get an error telling me that the table already exists.

In contrast, the following is working:

from sqlalchemy import create_engine

engine = create_engine(URL_OBJECT, fast_executemany=True)

with engine.connect() as connection:
    print(connection.execute("Select 1").fetchone())

Returns: (1,)

This one also does what it should.

from sqlalchemy import create_engine

engine = create_engine(URL_OBJECT, fast_executemany=True)

with engine.connect() as connection:
    print(connection.execute("Select * datebase.schema.table_existing_in_database").fetchone())

Returns: That table’s first row’s data.

Why is my table creation code not executed while the Select statements are?

>Solution :

You need to commit otherwise every transaction is rolled back by default.

SEE: commit-as-you-go

with engine.connect() as connection:
    connection.execute("Select 1 as an_integer into database.schema.test_table")
    connection.commit()

You can get more logging information by setting echo=True in create_engine(). Maybe post that information from this example if committing doesn’t solve this.

You should also wrap the sql statements in text() from sqlalchemy.sql.