[sqlalchemy] hybrid_property vs. hybrid_method, other than the latter can take arguments?
Hi, I wonder if there is any recommendation or best practice on choosing between hybrid_property and hybrid_method , other than they hybrid_method can take arguments? If I use the hybrid_method only throughout, without giving it a argument more than self, doesn't it equal to using hybrid_property? 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] Custom Relationship property for Column with csv of integer keys
Hi *,
this post is a bit short on data because I just wrote a long posting and it
seems to have vanished when I hit the "POST" button. *sigh*
I am trying to do something (admittedly crazy) like this, but without the
after_flush hook - I'd rather like to tell the ORM that member_ids is
computed from the members list.
Problem is that entries to members are new at the time of flush, so ids are
not available.
Any hints how to create a new csv_relationship property to track the ids of
referred objects in a column of comma separated values?!
from sqlalchemy import create_engine, Column, Integer, String, event
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
class Person(Base):
__tablename__ = "persons"
id = Column(Integer, primary_key=True)
name = Column(String)
class Bundle(Base):
__tablename__ = "bundle"
id = Column(Integer, primary_key=True)
member_ids = Column(String, unique=True)
def __init__(self, members):
self.members = members
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
@event.listens_for(Session, "after_flush")
def receive_after_flush(session, flush_context):
items = set(session.dirty) | set(session.new)
for item in items:
if isinstance(item, Bundle):
session.execute(Bundle.__table__
.update()
.values(member_ids=",".join(str(x.id) for x in item.members
))
.where(Bundle.id == item.id))
session = Session()
persons = [Person(name=name) for name in ["Martin", "Michael", "Fabian"]]
for person in persons:
session.add(person)
session.commit()
bundle = Bundle(persons[1:])
session.add(bundle)
session.commit()
Thanks and Greetings, Torsten
--
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] Abusing a string column as list of foreign keys (own relationship class, based on DependencyProcessor?)
Hello everybody,
I am currently pulling my hair out because I have a solution that I think
should work on the database side (albeit I don't like it), but I can't
figure out how to do this with sqlalchemy.
My current goal is to manage (long-lived) locks on copy-on-write
hierarchical data that is stored in Oracle RDBMS.
Of course the code must guaranteee uniqueness. Funny thing is that I can
have multiple locks on the same object as it can be shared due to the
shallow copy nature of the application.
The weird schema is something like this:
CREATE TABLE item_locks (
lock_id VARCHAR(36) NOT NULL,
context_id INTEGER NOT NULL,
target_path VARCHAR(2048) NOT NULL,
target_id INTEGER,
expiration_time DATETIME,
owner_id INTEGER NOT NULL,
PRIMARY KEY (lock_id),
CONSTRAINT item_locks_unique UNIQUE (target_path),
FOREIGN KEY(id) REFERENCES taggable (id) ON DELETE CASCADE,
FOREIGN KEY(target_id) REFERENCES taggable (id) ON DELETE CASCADE,
FOREIGN KEY(owner_id) REFERENCES users (id) DEFERRABLE INITIALLY
deferred
);
CREATE INDEX ix_taggable_locks_target_id ON taggable_locks (target_id);
Yes I know.
My code is basically working fine but I ran into one limitation:
target_path is actually a comma separated list of integers like "5,7,8"
which identify the locked object (id 8, same as target_id) as seen from the
context object (id 5, same as context_id).
Given the copy on write nature I have to propagate the locks to the actual
writable object though. context_id is always writable but the children are
reused across different contexts. A COW operation will create a new
instance that is also writable, so the lock may move from "5,7,8" to "9, 8"
with id 9 being the new copy of the object with id 7. (For completeness:
when the object is actually written by the lock holder, the lock would
transform to something like "10", with context_id = target_id = 10 as well).
What I forgot though: Usually the copy on write created objects are created
within the ORM session which means that I actually got target_path =
"None,8" when actually using the code in the intended setting. That's one
thing I did not have in mind... :-(
I can hack around this by code like this:
from sqlalchemy import create_engine, Column, Integer, String, event
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
class Person(Base):
__tablename__ = "persons"
id = Column(Integer, primary_key=True)
name = Column(String)
class Bundle(Base):
__tablename__ = "bundle"
id = Column(Integer, primary_key=True)
member_ids = Column(String, unique=True)
def __init__(self, members):
self.members = members
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
@event.listens_for(Session, "after_flush")
def receive_after_flush(session, flush_context):
items = set(session.dirty) | set(session.new)
for item in items:
if isinstance(item, Bundle):
session.execute(Bundle.__table__
.update()
.values(member_ids=",".join(str(x.id) for x in item.members
))
.where(Bundle.id == item.id))
session = Session()
persons = [Person(name=name) for name in ["Martin", "Michael", "Fabian"]]
for person in persons:
session.add(person)
session.commit()
bundle = Bundle(persons[1:])
session.add(bundle)
session.commit()
This comes with its share of new problems (like updating bundle.members
after the initial commit does not yield an update).
This frustrates me because SQLAlchemy internally has all the knowledge for
topological sorting of operations, but I can not figure out how to use the
goodness to implement the schema that I would like to have.
I have a faint hope that Mike will come up with a recipe how I can build my
own relationship like property so that the code can just be changed to
class Bundle(Base):
# ...
members = csv_relationship(Person, "id")
BTW: For extra fun the lock propagation work on the target_path column in
before_flush.
Any hints how to mount this with SQLAlchemy greatly appreciated!
Greetings, Torsten
--
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/23/2016 11:31 AM, Jinghui Niu wrote:
Thanks Mike. In future is it likely to have the instance level
expression and class level expression automatically translated by ORM?
It will be so much easier!
well a very small subset of them already are, but beyond that it's an
issue of increasingly diminished returns.
On Mon, Aug 22, 2016 at 7:20 AM, Mike Bayer mailto:mike...@zzzcomputing.com>> wrote:
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 a topic in
the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/AHNauPgot3c/unsubscribe
.
To unsubscribe from this group and all its topics, 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.
--
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?
Thanks Mike. In future is it likely to have the instance level expression
and class level expression automatically translated by ORM? It will be so
much easier!
On Mon, Aug 22, 2016 at 7:20 AM, Mike Bayer
wrote:
>
>
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/to
> pic/sqlalchemy/AHNauPgot3c/unsubscribe.
> To unsubscribe from this group and all its topics, 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] Using Python functions in query with automap reflected tables?
On 08/22/2016 02:50 PM, Rahul Ahuja wrote:
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'objectnor
'Comparator'objectassociated withusers.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!
when you deal with md_users.middlename and abms_blog.middlename, based
on the context shown above these are not Python strings. They are
SQLAlchemy attributes that ultimately resolve to Column objects that
represent a lexical token within a SQL string that is to be passed to
the database. Since you are working with SQL lexical tokens, evaluation
of the data they represent happens on the database side, not the Python
side. The SQLAlchemy Column object has support for converting a tiny
subset of Python operations to their SQL counterparts, but not string
functions split, strip, and replace.
You're looking here to have all of these translations occur within the
database as it selects rows and tests each one, so in this case the
functionality of non_conflict_middlename() would have to be implemented
in SQL.
Ideally for a function this elaborate I'd probably write a custom stored
procedure. Short of that, you'd have to pull the full set of data you
want to filter into Python first, then run your Python level criteria on
that dataset in memory. This might be all you need but of course it
will not scale past a few thousand source rows at the most. If the
table is from a large dataset, you still may be able to pre-filter for a
smaller subset of rows before loading into memory, then applying extra
filtering.
--
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] SQLAlchemy. Creating tables that share enum
if you have an Enum with a certain name that is to be used in multiple
tables, you can create it separately. Build the ENUM using the
create_type=False flag to keep it from generating the type
automatically, then use the .create() method to create it individually,
docs at
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=enum#sqlalchemy.dialects.postgresql.ENUM.
Alternatively, if you don't mind the extra overhead, run all your
table.create() calls with checkfirst=True. This should run a
"checkfirst" query not just for the table itself but also for the ENUM
types contained within.
On 08/22/2016 04:03 PM, Fran Goitia wrote:
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:
|
fromsqlalchemy importEnum
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()
fork,v invars_.items():
ifisinstance(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.
--
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 Monday, August 22, 2016 at 12:54:27 PM UTC+2, Simon King wrote: > > > 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 > > Thank you for pointing me to the right place in source. -- 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.
