On 08/25/2016 11:26 AM, [email protected] wrote:
Hello all,
I overrided that method as you suggested, and included placeholders for
the other ones being overloaded by calchipan. The reflection functions
now work, and I decided to use the same mechanism as calchipan for DDL
compilation (instantiating a Resolver object that is then passed to the
dbapi implementation). Hoever I've hit another snag that maybe you guys
have seen before.
The current state of the code can be checked out here:
https://github.com/rlanda/sqlalchemy-bigquery/tree/master/sqlalchemy_bigquery
To start filling in the CREATE functionality, I'm using this toy example:
metadata = MetaData(engine)
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('age', Integer),
Column('fullname', String),
)
users.create()
I expected this to trigger *visit_create_table *in the DDL compiler,
which would return a *Resolver* object that would be provided to the
*execute* method in the *Cursor* implementation in *dbapi*. However,
this is not happening - instead, it seems that the default execute
method is being triggered, instead of that in dbapi. Since that method
expects a string instead of the Resover object, it complains. The
exception I am getting is:
Traceback (most recent call last):
File "./test_bq.py", line 39, in <module>
users.create()
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/sql/schema.py", line
747, in create
checkfirst=checkfirst)
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 1920, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 1529, in _run_visitor
**kwargs).traverse_single(element)
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/sql/visitors.py",
line 126, in traverse_single
return meth(obj, **kw)
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/sql/ddl.py",
line 767, in visit_table
include_foreign_key_constraints=include_foreign_key_constraints
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 947, in execute
return meth(self, multiparams, params)
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/sql/ddl.py",
line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 1004, in _execute_ddl
compiled
File
"/Users/rlanda/Workspace/Python/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 1125, in _execute_context
util.text_type(statement), parameters,
TypeError: coercing to Unicode: need string or buffer,
CreateTableResolver found
that text_type() call is inside of the part where it's trying to throw
an exception. you'll want to give your Resolver object a __str__()
method so that unicode() reports something meaningful.
Any ideas on how invoke the correct execute method?
Thanks again!
Raul
On Friday, August 19, 2016 at 3:14:00 PM UTC+1, Mike Bayer wrote:
On 08/19/2016 06:37 AM, [email protected] <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
<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
<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
<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 [email protected] <javascript:>
> <mailto:[email protected] <javascript:>>.
> To post to this group, send email to [email protected]
<javascript:>
> <mailto:[email protected] <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
