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.
as an alternative:
to_sql
and execute it later with result = engine.execute(query)
? This would give the possibility to fix these incompatibilities before execution.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.