CODEWITHSUNDEEP
sqlalchemy
user1403505
user1403505

Reputation: 1005

SQLAlchemy not converting column with __ character correctly

I am trying to query a bigquery dataset with SQLAlchemy in a web application written with Django. The table name is TestBillingAcctLkp and it has a particular column __of_projects (two underscores in front)

I have defined the model object like this:

class TestBillingAcctLkp(Base):
    __tablename__ = 'TestBillingAcctLkp'
    Billing_account_name = Column(String)
    Billing_account_ID = Column(String,primary_key=True)
    Parent_account_ID=Column(String)
    Status=Column(String)
    __of_projects=Column(Integer)

In the views, I use this:

session.query(TestBillingAcctLkp).order_by(text("Billing_account_name asc")).all()

When I query I get the SQL like this:

 SELECT `TestBillingAcctLkp`.`Billing_account_name` AS `TestBillingAcctLkp_Billing_account_name`, `TestBillingAcctLkp`.`Billing_account_ID` AS `TestBillingAcctLkp_Billing_account_ID`, `TestBillingAcctLkp`.`Parent_account_ID` AS `TestBillingAcctLkp_Parent_account_ID`, `TestBillingAcctLkp`.`Status` AS `TestBillingAcctLkp_Status`, `TestBillingAcctLkp`.`_TestBillingAcctLkp__of_projects` AS `TestBillingAcctLkp__TestBillingAcctLkp__of_projects` FROM `TestBillingAcctLkp` ORDER BY Billing_account_name asc

I observe that _TestBillingAcctLkp is getting appended to the front of this column __of_projects and it becomes _TestBillingAcctLkp__of_projects in the SQL query translation therefore I get this error:

Exception : (google.cloud.bigquery.dbapi.exceptions.DatabaseError) 400 Name _TestBillingAcctLkp__of_projects not found inside TestBillingAcctLkp at

How can I resolve this?

Upvotes: 0

Views: 42

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55629

By default Python will combine class variables with two leading underscores with the class name - this is known as name mangling.

If the column in the database has two leading underscores you can specify it in the column definition, and assign the class variable to a name without leading underscores.

class TestBillingAcctLkp(Base):
    __tablename__ = 'TestBillingAcctLkp'
    ...
    of_projects=Column('__of_projects', Integer)

Upvotes: 1

Related Questions