1

I am using SQLAlchemy 0.7.6. I am aliasing columns with:

column = table.c["name"].label("foo.bar")

and SQLite uses only 'bar' as result field alias. Is there any workaround for that?

Example code:

create_table("sqlite:////tmp/test.sqlite", schema)

engine = create_engine(url)
metadata = MetaData(engine, reflect=True)
table = Table("test_table", metadata, schema=schema, autoload=True)

column = table.c["name"].label("foo.bar")
cursor = sql.expression.select([column])
row = cursor.execute().fetchone()

print "keys are: %s" % (row.keys(), )

Will print:

keys are: [u'bar']

instead of:

keys are: [u'foo.bar']

Works for postgres.

Here is full test code: https://gist.github.com/2506388

I've already reported that to the sqlalchemy lists, however meanwhile I would like to know if anyone else is experiencing similar problem and might have a workaround.

Stiivi
  • 2,077
  • 2
  • 16
  • 27
  • 1
    Simple answer is not to use dots in column names, but i'm sure you already knew that ;). – weenoid Apr 27 '12 at 08:33
  • Strange> I have `SA-0.7.4` and i do get `keys are: [u'foo.bar']` when I run your code. – van Apr 27 '12 at 08:41
  • @van: Yeah, it is possible, as it worked in 0.7 (remember having this exact issue in March 2011 on 0.6.something) – Stiivi Apr 27 '12 at 09:20
  • @Stiivi: cool, then one has to compare two codebases in regards to `sqlite` and voila :) – van Apr 27 '12 at 12:22

1 Answers1

2

Going to be patched in SQLAlchemy with an engine option. See mailing list thread for more information.

Meanwhile, the workaround is:

# select is sqlalchemy.sql.expression.select() 
# each selected column was derived as column = table.c[reference].label(label_with_dot)

labels = [c.name for c in select.columns]
...
record = dict(zip(labels, row))

Solution after patch is :

conn = engine.connect().execution_options(sqlite_raw_colnames=True)
result = conn.execute(stmt)
Stiivi
  • 2,077
  • 2
  • 16
  • 27
  • thanks, I didn't use zip though. Mine looks like this: `var_holding_query_EXECUTED = self.connection.execute(var_holding_query).fetchall() cols = [c.name for c in var_holding_query.columns] df = pd.DataFrame(var_holding_query_EXECUTED, columns=cols)` – hope288 May 07 '21 at 21:13