[sqlalchemy] SQLAlchemy. Creating tables that share enum
Models FacebookPost and TwitterPost share an enum called types. This enum
is correctly created when creating facebook_posts table, but when trying to
create twitter_posts table, there is an attempt to recreate this type which
results in an error.
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "types"
already exists
[SQL: "CREATE TYPE types AS ENUM ('Video', 'GIF', 'Scratch Reel',
'Card', 'Video Card', 'Text', 'Photo', 'Shared Article', 'Reply', 'Canvas',
'Carousel', 'Video Carousel', 'Link', 'Status')"]
This is the way I'm creating the database. I can't use
Base.metadata.create_all, because I need to be explicit in terms of what
tables are created
Engine = create_engine(db_url, echo=False)
Campaign.__table__.create(Engine)
SubCampaign.__table__.create(Engine)
Creative.__table__.create(Engine)
Hashtag.__table__.create(Engine)
FacebookPost.__table__.create(Engine)
TwitterPost.__table__.create(Engine)
I'm creating the enums this way:
from sqlalchemy import Enum
types = ('Video', 'GIF', 'Scratch Reel', 'Card', 'Video Card',
'Text', 'Photo', 'Shared Article', 'Reply', 'Canvas',
'Carousel', 'Video Carousel', 'Link', 'Status')
goals = ('CTR', 'ER', 'Awareness', 'CPGA')
sources = ('Facebook', 'Twitter', 'Instagram', 'Tumblr')
vars_ = locals().copy()
for k, v in vars_.items():
if isinstance(v, tuple):
locals()[k] = Enum(*v, name=k)
Thanks
--
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.
[sqlalchemy] Using Python functions in query with automap reflected tables?
I'm having some trouble using Python string functions on VARCHAR columns in
MySQL (reflected using automap_base() and base.prepare). I'm getting
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator'
object associated with users.middlename has an attribute 'replace'
when I try
base_match = sess.query(md_users.id, abms_biog.id).\
filter(match().non_conflict_middlename(md_users.middlename,
abms_biog.middlename) == 1).
with my non_conflict_middlename function which just does some basic string
matching:
def non_conflict_middlename(self, a, b):
a = a.replace('.','')
b = b.replace('.','')
if ((len(a.strip()) == 0 or len(b.strip()) == 0)
or (a.replace('-','').replace(' ','') ==
b.replace('-','').replace(' ',''))
or ((len(a) == 1 or len(b) == 1) and a[0] == b[0])
or ((len(a) > 1 and len(b) > 1) and (a.find(b) + b.find(a) >
-2))
or (((' ' in a) and a[0]+a.split(' ',1)[1][0] == b) or ((' ' in
b) and b[0]+b.split(' ',1)[1][0] == a))
or ((('-' in a) and a[0]+a.split(' ',1)[1][0] == b) or (('-' in
b) and b[0]+b.split(' ',1)[1][0] == a))):
return 1
else:
return 0
Am I missing something here? Do I need to explicitly declare the tables and
data types with declarative base or can I somehow treat columns as Python
string objects while using reflected tables via automap? Thanks in advance
for your time!
--
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.
Re: [sqlalchemy] What criteria should I use to determine whether I need a distinct class-level expression for my hybrid attributes?
On 08/22/2016 03:20 AM, Jinghui Niu wrote:
I'm creating a mixin for my timestamp columns throughout my projects.
Internally, the mixin uses UTC strings to store timestamps, externally
it converts back and forth into local time using a second column that
stores timezone information. I'm studying the hybrid attribute section
in the documentation, but not sure if this use case would involve a
different expression between the instance level and class level.
As a general rule, what should I look into to determine whether I need
to use the hybrid_property.expression()?
expression is needed when you A. want to use your attribute in a Query
as part of filter() or similar and B. if the Python inside your hybrid
doesn't work as a SQLAlchemy expression.
Example 1: doesn't need expression - simple addition:
@hybrid_property
def a_plus_b(self):
return self.a + self.b
Example 2: does need expression - Python "if" statement needs to be CAST
on SQL
@hybrid_property
def conditional_a(self):
return "foo" if self.a == 'f' else "bar"
@conditional_a.expression
def conditional_a(cls):
return sql.case(("foo", cls.a == 'f'), else_="bar")
Do I need to first familiarize myself with the SQLalchemy's SQL
functions, such as func() etc., or there is some simpler rules? Thanks.
if you're working with date / timezone conversions in SQL then you
should figure out the SQL you want first, then at that point you
probably would need to use func. to get some SQL functions out of it,
it's pretty easy at that level.
--
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.
--
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.
Re: [sqlalchemy] Having SA generate constructor when using autoload.
On Mon, Aug 22, 2016 at 10:12 AM, Piotr Dobrogost
wrote:
> I'd like to map a class onto table and automatically get __init__() method
> per
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.declarative_base.params.constructor
>
> However when I declare class as
>
> Base = declarative_base(metadata=metadata)
> class MyClass(Base):
> pass
>
> I get
> sqlalchemy.exc.InvalidRequestError: Class does not have a
> __table__ or __tablename__ specified and does not inherit from an existing
> table-mapped class.
>
> Is there a way to hold off this check until after I map this class like this
>
> my_table = sa.Table("my_table", meta.metadata, autoload=True,
> autoload_with=engine)
> orm.mapper(MyClass, my_table)
>
> ?
>
> I tried having MyClass inherit from object instead of Base but then I get
> TypeError: __init__() got an unexpected keyword argument 'col1'
> when calling
> x = MyClass(col1=1, col2=2)
> , although "my_table" has "COL1" column. So it seems in this case __init__()
> method is not automatically synthesized.
>
You'd probably be best off copying the SQLAlchemy code into your own
project - it's not long:
https://bitbucket.org/zzzeek/sqlalchemy/src/5145f671a4b5eb072e996bc450d2946d4be2a343/lib/sqlalchemy/ext/declarative/base.py?at=master&fileviewer=file-view-default#base.py-634
Simon
--
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.
Re: [sqlalchemy] Google Bigquery support
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 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
> > .
> > To post to this group, send email to sqlal...@googlegroups.com
>
> > .
> > 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.
[sqlalchemy] Having SA generate constructor when using autoload.
I'd like to map a class onto table and automatically get __init__() method
per
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.declarative_base.params.constructor
However when I declare class as
Base = declarative_base(metadata=metadata)
class MyClass(Base):
pass
I get
sqlalchemy.exc.InvalidRequestError: Class does not have a
__table__ or __tablename__ specified and does not inherit from an existing
table-mapped class.
Is there a way to hold off this check until after I map this class like this
my_table = sa.Table("my_table", meta.metadata, autoload=True,
autoload_with=engine)
orm.mapper(MyClass, my_table)
?
I tried having MyClass inherit from object instead of Base but then I get
TypeError: __init__() got an unexpected keyword argument 'col1'
when calling
x = MyClass(col1=1, col2=2)
, although "my_table" has "COL1" column. So it seems in this case
__init__() method is not automatically synthesized.
Regards,
Piotr
--
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.
[sqlalchemy] What criteria should I use to determine whether I need a distinct class-level expression for my hybrid attributes?
I'm creating a mixin for my timestamp columns throughout my projects. Internally, the mixin uses UTC strings to store timestamps, externally it converts back and forth into local time using a second column that stores timezone information. I'm studying the hybrid attribute section in the documentation, but not sure if this use case would involve a different expression between the instance level and class level. As a general rule, what should I look into to determine whether I need to use the hybrid_property.expression()? Do I need to first familiarize myself with the SQLalchemy's SQL functions, such as func() etc., or there is some simpler rules? Thanks. -- 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.
