sqlalchemyfirebird

How to remove quotation marks


I am new to SQLAlchemy and Firebird DB.

I can directly create a table using DBeaver:

CREATE TABLE NEWTABLE (
    COLUMN1 FLOAT,
    COLUMN2 FLOAT
);

If I try to do the same using pyndas+sqlalchemy, I get an error:

import sqlalchemy as sa
import pandas as pd


engine = sa.create_engine(r'firebird+fdb://user:pwd@localhost:3050/c:\XXX.FDB', echo=False)
df = pd.DataFrame({"COLUMN1":[], "COLUMN2":[]})
df.to_sql(name="NEWTABLE", con=engine, if_exists = 'replace', index=False, method=None)
DatabaseError: (fdb.fbcore.DatabaseError) ('Error while preparing SQL statement:\n- SQLCODE: -817\n- Dynamic SQL Error\n- SQL error code = -817\n- Metadata update statement is not allowed by the current database SQL dialect 1', -817, 335544569)
[SQL: 
CREATE TABLE "NEWTABLE" (
    COLUMN1 FLOAT, 
    COLUMN2 FLOAT
)

The problem is due to the quotation marks "..." in to_sql query.

  1. How to tell SQLAlchemy not to use quotation marks to be compatible with Firebird (dialect 1)?

as an alternative:

  1. Instead of executing the query immediately, is it possible to produce the query string only from to_sql and execute it later with result = engine.execute(query)? This would give the possibility to fix these incompatibilities before execution.

Solution

  • I found a possible solution reading Get rid of double quotation marks with SQLalchemy for PostgreSQL: if the table name is uppercase, it is automatically surrounded by quotation marks.

    Experimentally I see that the same apply to Firebird, even if I didn't find any proper reference to this issue but only something apparently unrelated

    A change to

    df.to_sql(name="newtable", ...)
    

    solves the problem.