2

I am new to SqlAlchemy and Firebird DB.

I can directly create a table using DBeaver: 

CREATE TABLE NEWTABLE (
    COLUMN1 FLOAT,
    COLUMN2 FLOAT
);

but 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.
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Alex Poca
  • 2,406
  • 4
  • 25
  • 47
  • Why are you using a dialect 1 database? Is this a new project or a legacy project? Dialect 1 (which doesn't support quoted object names, and where double quotes delimit a string instead) was deprecated **20 years ago** in favor of dialect 3 (which supports quoted object names, and strings are delimited by single quotes). Using dialect 1 with modern tools which expect reasonable standard SQL behavior is hard. If this is a new project, I'd strongly recommend switching to a dialect 3 database. – Mark Rotteveel Feb 24 '20 at 11:42
  • Alas, yes: it is a legacy project, no way to use something less archaic... – Alex Poca Feb 24 '20 at 11:56
  • I'd recommend migrating the database to dialect 3, unless that is really not an option. – Mark Rotteveel Feb 24 '20 at 11:59
  • Alas it isn't an option. A closed app is based on this database, and it is not possible to migrate it. – Alex Poca Feb 24 '20 at 12:02
  • @AlexPoca you might try to override a dialect connection-wise, using parameter: https://firebirdsql.org/file/documentation/reference_manuals/driver_manuals/odbc/html/fbodbc205-conn-params.html // Granted, i do not remember exactly what gets overriden and what not, it is not a mode supported, and Vlad Khorsun usually gets very annoyed at "lame questions" about this. But as a short term solution it erhaps might work. Or maybe not. – Arioch 'The Feb 25 '20 at 08:48

2 Answers2

2

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.

Alex Poca
  • 2,406
  • 4
  • 25
  • 47
0

If you have created models:

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.declarative import declarative_base

base = declarative_base()

class Base(base):
    __abstract__ = True
    __table_args__ = {'quote':False}
    

db = SQLAlchemy(model_class=Base)


class Newtable(Base):
    __tablename__ = 'NEWTABLE '
    column1 = db.Column('COLUMN1', db.Float, quote=False)
    column2 = db.Column('COLUMN2', db.Float, quote=False)
jpfreire
  • 1,268
  • 16
  • 23