Create table using select statement in Python [duplicate]

Question

I have connected my SQL Server to Python using sqlalchemy. Now I'm trying to use select statements to create 3 separate tables and ultimately join them. But none of my queries can ran through. Here's my code:

query2 = """
select AML_CUST_ID as AML_CUST_ID0,DATA_DT as DAY0,sum(CAST(AMT as float))as AMT0 into AML_Testing_DAY0 from dbo.trial2 
group by AML_CUST_ID,DATA_DT
"""
connection = engine.connect()
connection.execute(query2); 
connection.execute("SELECT * FROM AML_Testing_DAY0")

Even though this code can run with no error, there's no table created in SQL Server. I have read on stackoverflow that this only creates a temp table, not a permanent table. Is there a way to create a permanent table? Thanks in advance for any inputs

It looks like you're relying on autocommit, but SQLA does not recognize your statement as a data changing operation. — Ilja Everilä
– Ilja Everilä, Commented Apr 22, 2020 at 7:42

Dale K · Accepted Answer · 2020-04-22 07:31:55Z

0

It will create a permanent table. Are you sure you're in the right database in the context of your session? Try adding three part naming, with the database before your schema/tables. If that's not the issue, try starting a trace with SQL Server Profiler to see what is happening.

edited Apr 22, 2020 at 7:31

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Apr 22, 2020 at 7:30

Cedersved

1,0551 gold badge8 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Create table using select statement in Python [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related