Hello all, I have implemented the first stab at a PEP 249 adaptor layer for BigQuery and it seems to work well. It is possible to create an engine/connection/cursor, submit an SQL query and get results back (only SELECT statements for now, API commands will come later). I have moved on to changing the DDL and statement compilers to conform to BigQuery's standard SQL dialect:
https://cloud.google.com/bigquery/sql-reference/query-syntax I hit an immediate hurdle and I am not sure if it is because the Dialect object I created is incorrect or because the PEP 249 adapter is behaving in an unexpected way. Basically, upon connection SQL alchemy will fire some test queries (afaiu to detect whether column names support unicode), one of which is being rendered as SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 The problem is that BigQuery does not support VARCHAR. I have already added a colspecs dictionary to my new dialect object, with many common data types mapped to their BgQuery equivalents: colspecs = { types.Unicode: BQString, types.Integer: BQInteger, types.SmallInteger: BQInteger, types.Numeric: BQFloat, types.Float: BQFloat, types.DateTime: BQTimestamp, types.Date: BQTimestamp, types.String: BQString, types.LargeBinary: BQBytes, types.Boolean: BQBoolean, types.Text: BQString, types.CHAR: BQString, types.TIMESTAMP: BQTimestamp, types.VARCHAR: BQString } I was under the impression that this would be enough to define a behaviour where sqlalchemy queries using e.g. VARCHAR would be compiled using the BQString class, which should render as 'STRING' as defined in its get_col_spec method. This is in accordance to BigQuery's basic type system: https://cloud.google.com/bigquery/sql-reference/data-types However, the query renders as above and the underlying PEP 249 throws an exception. I could however envisage a type of operation where 2 queries a fired to the DB, one using VARCHAR and another using unicode, in order to detect which one succeeds. If this is the case, maybe the query rendering is fine and the problem is the underlying library returning an exception instead of some standard failure signal. Does anybody know if 1) defining a colspecs object as above will be enough for objects of e.g. types.VARCHAR to be rendered as e.g. 'STRING' in generated SQL? 2) Does the underlying PEP 249 implementation need to signal failure in a particular way, or is throwing exceptions the expected behaviour? Thanks, Raul -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
