Re: [sqlalchemy] custom queries

[Richard Gerd Kuesters]

yeah, i know, it was just a shot in the dark. for me it's useful to 
prefilter some user queries, but not their relationships and others 
(like an audit system with soft delete). anyway, as you said, this 
already works now and i'll play around a little more with it :)


thanks for all your help, as always!
richard.


On 01/13/2016 06:59 PM, Mike Bayer wrote:

I can't imagine what such a "feature" would look like nor how it
wouldn't require every call everywhere in SQLA internals  well as all
dialects, 3rd party and otherwise, to always remember to mark itself in
some way, annotating the end-user statement as I illustrated is already
available now.

as


On 01/13/2016 01:46 PM, Richard Gerd Kuesters wrote:

yup! i already was going this way. it would be impossible to
"automagically" do this without the performance sacrifice ... lol. well,
if this looks reasonable in some use cases, can this sort of "feature"
be implemented in future sa releases?

best regards,
richard.

On 01/13/2016 04:41 PM, Mike Bayer wrote:

or, place a marker in your own queries:

query(User).execution_options(my_query=True).all()



On 01/13/2016 01:40 PM, Mike Bayer wrote:

similar, you'd need to use sys.exc_info() and walk through the stack
trace to programmatically determine the origin of a Python statement.
You could do this inside the before_execute() event, for example.




On 01/13/2016 01:31 PM, Richard Gerd Kuesters wrote:

Mike, thanks for your attention and i'm sorry, i was not clear enough in
my question.

i would like to know if there's a way to tell if a query was fired by
sqlalchemy internals or by my coded query (programatically).


thanks a lot!
richard.

On 01/13/2016 04:11 PM, Mike Bayer wrote:

sure, use pdb to step through and use the "where" command to show where
each call originates.

alternatively, if this is for profiling, you can use print_callers() as
in the example at
http://docs.sqlalchemy.org/en/rel_1_0/faq/performance.html#code-profiling which
will show the origins of calls, though you'd need to step through things
to see the full chain.  A tool like RunSnakeRun can provide a graphical
display instead.


On 01/13/2016 11:58 AM, Richard Gerd Kuesters wrote:

hi all!

i'm wondering if there's a way to determinate if a query was "launch" by
my code (ex. session.query(Entity)...) or by the internals of sqlalchemy
(backref, relationship, etc). i think it's better to use a example,
based on *adjacency_list.py*:

http://pastebin.com/q3yx36vn

in the code, you'll notice there's only one query *literally* written
(in the main script), but "STEP" is called more than once (by
internals). my question is: can I differ between them?

thanks a lot!
richard.

--
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.

--
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.
<>

[sqlalchemy] Postgres 9.5 UPSERT and SQLAlchemy?

[vmalloc]

Hi,

Now that Postgres 9.5 is officially out and supports UPSERT (ON CONFLICT) 
out of the box - is there any recommended way to use it in SQLA? Is there 
any support planned to be added inside the ORM?

Thanks,

Rotem

-- 
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] Re: custom queries

[Jonathan Vanasco]

Couldn't one also use the "PreFilteredQuery" recipe 
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery 
to create a marker?

I don't think that filters relationships, while the "GlobalFilter" recipe 
does https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter

-- 
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] Postgres 9.5 UPSERT and SQLAlchemy?

[Mike Bayer]

since I don't use UPSERT, I don't have a deep insight on an appropriate
API for this, as all the backends have totally different systems, none
of which behave the same.   Whether a construct would attempt to work
"the same" on different backends, or if there are just totally different
constructs for each backend, is unknown.

Seems like for PG/MySQL we'd stick to postgresql_on_conflict and
mysql_on_duplicate_key.  So the actual MERGE statement is nowhere.
PG's on_conflict and MySQL's on_duplicate_key still seem quite different.





On 01/14/2016 06:27 AM, vmalloc wrote:
> Hi,
> 
> Now that Postgres 9.5 is officially out and supports UPSERT (ON
> CONFLICT) out of the box - is there any recommended way to use it in
> SQLA? Is there any support planned to be added inside the ORM?
> 
> Thanks,
> 
> Rotem
> 
> -- 
> 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.

[sqlalchemy] A different approach to building APIs

[Deepak Singh]

So basically for any software nowadays, best practice is to create REST 
resources which handles all GET,POST,PUT,DELETE.
And it works for all clients be it mobile app or web app.

For these controllers are overwhelmed with lots of code.

Now in this approach, What you basically do is you organize your database 
in the best way possible and then treat data as it comes in.

Consider a use case:
User A has 2 addresses and Institute A has 3 addresses

DB schema is:


User:
id(PK)
contactable_id(FK)
name(String)


Institute:
id(PK)
contactable_id(FK)
name(String)


Contactable:
id


Address:
pincode(String)
contactable_id(FK)




So now with this schema when a user is created, a record in contactable is 
created and is associated with user's record, when the address for same 
user is created, it get associated with this contactable id.

Now what we have done is we have a middleware built using sqlalchemy which 
takes structural json as input and records data.

So for above example if I have to create a user with 2 addresses following 
json should be sent as input.

{
  "contactable": {
"user": {
  "name": "Deepak"
},
"address": [
  {
"pincode": "421202"
  },
  {
"pincode": "421203"
  }
]
  }
}




This approach has so far worked for all the relational structures we have. 
 And we have around 140 tables. :). Only condition is make sure the json 
structure is as per schema.


For updates, 


{
  "contactable": {
"id": 1,
"user": {
  "id": 1254,
  "name": "Deepak"
},
"address": [
  {
"id": 8,
"pincode": "421202"
  },
  {
"id": 9,
"pincode": "421203"
  }
]
  }
}


So fields will get updated if the json has ID's

Now when you have to GET records,
We have built a mechanism to query it over HTTP.
The URLs are the queries. Attempt is to make everything that you do in SQL 
from browser.
This is the reference http://htsql.org/
Have made our own thing with a different design using sqlalchemy.

We are able to perform, queries with params, joins, conditional joins, 
filters, paging, sorting, combining results from multiple tables, range, 
date.

Still working on other stuff.

Authorization and Authentication are handled separately.

For all of these we have only 2 endpoints in the API.
One for GET and one for POST.

Hardly 700 lines of code and lot of Regex.
If it works fine, plan is to make it an open source.

So far we have solved all the issues we have faced.
I have received mixed reviews on this from different experts.
Would want a review from you guys.

What do you guys think of this approach?
What are the limitations?
Is it a stupid idea?

-- 
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] PostgreSQL domains

[Jonathan Rogers]

On Wednesday, January 13, 2016 at 10:54:04 AM UTC-5, Michael Bayer wrote:
>
>
>
> On 01/13/2016 12:37 AM, Jonathan Rogers wrote: 
> > I have several domains based on type TEXT to constrain values in 
> > specific ways, such as to disallow empty values or only allow valid 
> > email addresses. When I reflect tables with columns of such a domain, 
> > SQLAlchemy simply considers their types to be TEXT. Is there any way to 
> > make the domains known to SQLAlchemy so that it can treat them as 
> > distinct types? My goal is to define a new table with nearly identical 
> > structure to an existing one, adding some columns in the process. I 
> > looked over 
> > 
> > 
> >   "Custom Types" in the manual but I wasn't sure where to start. Would I 
> >   be creating an entirely new type or augmenting an existing one? 
>
> This is a Text subtype so I'd have TEXT in the superclass, the @compiles 
> approach can be used to give it its DDL: 
>
> from sqlalchemy.types import TEXT 
> from sqlalchemy.ext.compiler import compiles 
>
> class MyType(TEXT): 
> pass 
>
> @compiles(MyType) 
> def _mytype(elem, compiler, **kw): 
> return "MYTYPE" 
>
>
Thanks. That was just the hint I needed. Initially, I was hoping that I 
could get SQLAlchemy to reflect the domains but I ended up using explicit 
declarations anyway. 

-- 
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] Write to Class association_table

[Mary Le Jane]

*How do you insert a row into an **association_table through the Parent (class) 
child(variable) relationship.*


This is pretty much my code (simplified)..

association_table = Table('association', Base.metadata,
Column('left_id', Integer, ForeignKey('user.id')),
Column('right_id', Integer, ForeignKey('group.id'))
) 
class User(Base): 
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
group = relationship("Child",
secondary=association_table)
# user permissions

class Group(Base):
__tablename__ = 'group'
id = Column(Integer, primary_key=True)
admin = db.Column(db.Boolean, default=False, nullable=False) mod = 
db.Column(db.Boolean, default=False, nullable=False)

banned = db.Column(db.Boolean, default=False, nullable=False)

Now in User I can *get *the group the user is in by doing User.group.admin 
which goes in the association_table and searches for queries:

return self.group.query.filter(
group_users.c.group_id == group.id).count() > 0


*But I have no idea how to insert a row into **association_table through the 
group variable in Users. I am stumped.*

-- 
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] Re: Write to Class association_table

[Mary Le Jane]

Hate to be "that guy" who prematurely asks for help, then 10 mins later has 
found it. 

u1 = current_user
a1 = current_user.get_group_from_name('admin')
u1.group = a1
u1.save()

Hope this helps someone in the future, and sorry for this post.

On Friday, January 15, 2016 at 4:38:07 PM UTC+11, Mary Le Jane wrote:
>
> *How do you insert a row into an **association_table through the Parent 
> (class) child(variable) relationship.*
>
>
> This is pretty much my code (simplified)..
>
> association_table = Table('association', Base.metadata,
> Column('left_id', Integer, ForeignKey('user.id')),
> Column('right_id', Integer, ForeignKey('group.id'))
> ) 
> class User(Base): 
> __tablename__ = 'user'
> id = Column(Integer, primary_key=True)
> group = relationship("Child",
> secondary=association_table)
> # user permissions
>
> class Group(Base):
> __tablename__ = 'group'
> id = Column(Integer, primary_key=True)
> admin = db.Column(db.Boolean, default=False, nullable=False) mod = 
> db.Column(db.Boolean, default=False, nullable=False)
>
> banned = db.Column(db.Boolean, default=False, nullable=False)
>
> Now in User I can *get *the group the user is in by doing 
> User.group.admin which goes in the association_table and searches for 
> queries:
>
> return self.group.query.filter(
> group_users.c.group_id == group.id).count() > 0
>
>
> *But I have no idea how to insert a row into **association_table through the 
> group variable in Users. I am stumped.*
>
>

-- 
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.