CODEWITHSUNDEEP
pythonoracle-databasesqlalchemycx-oracle
VinceP
VinceP

Reputation: 2173

Failing to create table using bound parameters in sqlalchemy/cx_oracle

I want to execute a "create table" statement in-database using bound parameters. This works (without bound parameters):

from sqlalchemy.sql import text
from sqlalchemy import create_engine

con = create_engine(\\..)
s = text("""create table test_table as select * from dual where 1 = 1 """)
con.execute(s)

However, if I use bound parameters:

s = text("""create table test_table as select * from dual where 1 = :a """)
con.execute(s, a = 1)

it fails with error DatabaseError: (cx_Oracle.DatabaseError) ORA-01036: illegal variable name/number.

I am not convinced this error has anything to do with the bound parameters because a simple select statement without table creation will work smoothly:

s = text("""select * from dual where 1 = :a """)
con.execute(s, a = 1).fetchall()
#[('X',)]

There seems to be something in the "create table" plus "bound parameters" that breaks the query. Any idea why this is happening and how to fix it?

Upvotes: 0

Views: 246

Answers (1)

Anthony Tuininga
Anthony Tuininga

Reputation: 7096

Bind variables are not permitted in DDL statements. This is the reason why it works as expected with a simple query, but as soon as you have a create table statement, it fails. You will have to write your DDL statement without any bind variables, unfortunately!

Upvotes: 3

Related Questions