Unable to select from table starting with "@" in Python fdb

Question

I'm using FDB module to fetch data from Firebird database using Python. I'm trying to fetch data from table named @TABLE_NAME, it was not my idea to include "@" in the table name, and it makes it impossible to execute statements on it. When I try to fetch data from normal table, everything works just fine. This command:

cur.execute("SELECT * FROM @TABLE_NAME")

return this error:

fdb.fbcore.DatabaseError: ('Error while preparing SQL statement:\n- SQLCODE: -104\n- Dynamic SQL Error\n- SQL error code = -104\n- Token unknown - line 1, column 15\n- @', -104, 335544569)

I have no clue what to do and I would apprieciate some help.

Answer 1

"Regular identifiers" in Firebird SQL server can not contain @ symbol, see Identifiers.

With SQL Dialect 3 you can have quoted irregular identifiers, see SQL Dialects.

But you better check you are actually using it. Granted, Dialect 1 is VERY obsolete and any reasonable program today sets Dialect 3 when creating and when connecting to Firebird databases. Still you will be safer to check this.

You can check that database property by select MON$SQL_DIALECT from MON$DATABASE, see MON$DATABASE.

There is no way that I know to query the connection dialect, but it seems enforcing dialect 1 for a connection to dialect 3 database is rather exotic and hard to do endeavor, so you may practically assume it happens next to never. Still you must know about that and be able to check connection opening code in rare cases dialect-depending things go awry.

---

After you made sure you are on modern side of SQL you can start quoting identifiers:

SELECT * FROM "@TABLE_NAME"

Judging by How to write string literals in python without having to escape them? you have a number of ways to do it, for example:

• C-style cur.execute( "SELECT * FROM \"@TABLE_NAME\"" )
• Raw style cur.execute( r'SELECT * FROM "@TABLE_NAME"' )
• and many others, with Unicode numeric codes and string expressions