Thanks! Will do. All the best Raul On Friday, August 19, 2016 at 3:14:00 PM UTC+1, Mike Bayer wrote: > > > > On 08/19/2016 06:37 AM, rla...@fastly.com <javascript:> wrote: > > 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: > > > The method that's calling this test and others is in > sqlalchemy/engine/default -> DefaultDialect.initialize(). You should > override that whole method and do away with all the things it's trying > to check there, as calchipan does: > > > https://bitbucket.org/zzzeek/calchipan/src/86ef380c572b9c1b8186278446a9b4952a538f97/calchipan/base.py?at=master&fileviewer=file-view-default#base.py-45 > > > Although I would say that on the SQLAlchemy side, > _check_unicode_returns() should likely be a method that can raise > NotImplementedError() individually like the rest of the tests called > within the base initialize(). > > > > > > > > > > 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+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. >
-- 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.