[sqlalchemy] How to create a Partitioned Oracle Table in SQLAlchemy?

[Matthew Moisen]

Hello,

In Oracle we can create a Partitioned Table like the following:

CREATE TABLE sales_hash
  (s_productid  NUMBER,
   s_saledate   DATE,
   s_custid NUMBER,
   s_totalprice NUMBER)
PARTITION BY HASH(s_productid)
( PARTITION p1 TABLESPACE tbs1
, PARTITION p2 TABLESPACE tbs2
, PARTITION p3 TABLESPACE tbs3
, PARTITION p4 TABLESPACE tbs4
);

Is it possible in SQLAlchemy to define this in the Core or ORM? Note that this 
is different from the horizontal/vertical sharding supported in SQLAlchemy 
.

Checking the docs 

 for `Table`, I did not see any "postfixes" or similar.

Likewise in the Oracle Dialect page 
, it didn't mention 
table partitions.

I took a look at the Customizing DDL 
 page, but it appears that 
this only allows you to ALTER TABLE after it has already been created - but in 
Oracle it is not possible to alter a table to be partitioned.

Thanks and best regards,

Matthew

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] is it possible to create an ORM session with a db cursor?

[mike bayer]

you'd need to patch some kind of fake DBAPI connection that returns this 
cursor from the cursor() method.   it would be ugly but if the cursor 
behaves well it could work.   you'd get session.connection() to get a 
sqlalchemy.engine.Connection, then patch your "fake" DBAPI connection 
into it as the underlying connection object.   (or use a pool with 
custom "creator" + engine).


On 03/22/2017 03:54 PM, Jonathan Vanasco wrote:

longshot, i know.

We've got some legacy twisted code that does raw operations via a
twisted.enterprise.adbapi connection pool with the psycopg2 driver.
 Other services in this deployment are configured to use SqlAlchemy for
all operations - this is the legacy holdout.

There are a few chunks of blocking code that have a performance
bottleneck caused by multiple updates to the same records.

I'm wondering if it's possible to leverage the orm on top of this
existing connection's cursor.  The basic idea would be this:

   s = Session(connection=twisted_dbapi_cursor)
   foo = s.query(Foo).get()
   s.flush()
   txn.commit()
   s.close()

I've seen stuff about using an engine build off an existing connection,
but I only have the cursor - not the connection itself.  The connection
is a private attribute in the cursor, so i could potentially access
it but that could cause issues that scare me at this point.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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 - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Large number of polymorphic subclasses

[mike bayer]

On 03/22/2017 02:17 PM, da...@benchling.com wrote:

Hey all,

We were wondering if you had any advice on having a large (~10) number
of polymorphic subclasses for a single base class. Using
with_polymorphic: '*' causes SQLAlchemy to joinedload all subclasses
like this:

SELECT ...
FROM base_table
LEFT OUTER JOIN sub_table_1 ON base_table.id = sub_table_1.id
LEFT OUTER JOIN sub_table_2 ON base_table.id = sub_table_2.id
...

Postgres buckles under too many joins, and these queries start taking a
really long time.

One other note is that for most of our queries, only a few of these
sub-tables are actually needed, so most of the joins are wasted.
Unfortunately, ahead of time, we don't know which tables will be needed
-- we're relying on the discriminator.

Ideally, we'd be able to specify that the ORM should subqueryload the
subclasses (and only execute subqueries on the types that are present).
This would have to happen both when querying the base table, but also
when accessing relationships. We'd want it to execute a query on the
base table, then execute one query for each present subclass.

Another solution might be to use some kind of hook that

- is executed after a query returns with results (or after a list of
models are added to the session?)
- groups the models by type and runs its own subqueries to load the data

Any help here is greatly appreciated!



The purpose of with_polymorphic is more about being able to filter on 
multiple classes at the same time, which is why it uses joins, but these 
don't scale to many subclasses.Adding a subquery load for the 
related tables would be something that the ORM can someday have as a 
feature, but it would need a lot of tests to ensure it's working as 
advertised.


There's a lot of ways to get those other tables loaded but none of them 
look that great.   Turning off with_polymorphic(), one approach is to 
collect all the distinct types and identifiers from your query result; 
then do a separate query for each subtype:


result = session.query(BaseClass).filter(...).all()

types = sorted([(type(obj), obj.id) for obj in result], 
key=lambda t: t[0])


for type, ids in itertools.groupby(types, key=lambda t: t[0]):
session.query(type).filter(type.id.in_(ids)).all()

That will emit a query with an INNER JOIN for each class and will 
populate the remaining records in the identity map.  The columns that 
are already loaded are not re-accessed, though the DBAPI will still send 
them over the network to the cursor.   You can try limiting the columns 
you query for in each statement as well by using the defer() option.


Another way is to use with_polymorphic() but to provide a different kind 
of SQL statement, like a polymorphic_union().   This would be a UNION of 
statements that each have an inner join.   the resulting SQL is a beast 
but it at least isn't using those left outer joins.   I think you can 
probably use sqlalchemy.orm.util.polymorphic_union() directly to get 
this UNION statement built up automatically.


Still another way is to reorganize the mappings to use single-table 
inheritance and relationship() to link out to the related table, then 
the normal "subqueryload" feature to load them as relationships.   Even 
though this way is ugly, I might use this (short of implementing the 
related table subqueryload feature) just to make things simple.



Definitely a feature that should be added but that's not an immediate 
solution.





Thanks,
Damon

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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 - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Performing filter by using string

[mike bayer]

keeping in mind it's absolutely not safe to do this with *untrusted* 
user input, you'd use exec/eval:



globals_ = globals()
locals_ = {}
exec("%s = map_model" % model, globals_, locals_)
q = session.query(model).filter(eval(condition, globals_, locals_))




On 03/22/2017 10:00 AM, Vijaya Sekar wrote:

Hi everyone,

I have an string which has the condition to be performed while
retrieving from database. I automap my model and make reference to it by
using another variable name.
How can achieve retrieve data by filter which is in string?

here is my code

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import MetaData


engine = create_engine("sqlite:///chinook.db", echo = False)

metadata = MetaData()
model = 'employees'
condition = 'employees.City == "Chennai"'
metadata.reflect(engine, only=[model])
base = automap_base(metadata=metadata)
base.prepare()
map_model = base.classes[model]
session = Session(engine)
retrieve = session.query(map_model).filter(condition)
for i in retrieve:
print(i.EmployeeId)



--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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 - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] is it possible to create an ORM session with a db cursor?

[Jonathan Vanasco]

longshot, i know.

We've got some legacy twisted code that does raw operations via a 
twisted.enterprise.adbapi connection pool with the psycopg2 driver.  Other 
services in this deployment are configured to use SqlAlchemy for all 
operations - this is the legacy holdout.

There are a few chunks of blocking code that have a performance bottleneck 
caused by multiple updates to the same records.  

I'm wondering if it's possible to leverage the orm on top of this existing 
connection's cursor.  The basic idea would be this:

   s = Session(connection=twisted_dbapi_cursor)
   foo = s.query(Foo).get()
   s.flush()
   txn.commit()
   s.close()
   
I've seen stuff about using an engine build off an existing connection, but 
I only have the cursor - not the connection itself.  The connection is a 
private attribute in the cursor, so i could potentially access it but 
that could cause issues that scare me at this point.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Large number of polymorphic subclasses

[damon]

Hey all,

We were wondering if you had any advice on having a large (~10) number of 
polymorphic subclasses for a single base class. Using with_polymorphic: '*' 
causes SQLAlchemy to joinedload all subclasses like this:

SELECT ...
FROM base_table
LEFT OUTER JOIN sub_table_1 ON base_table.id = sub_table_1.id
LEFT OUTER JOIN sub_table_2 ON base_table.id = sub_table_2.id
...

Postgres buckles under too many joins, and these queries start taking a 
really long time.

One other note is that for most of our queries, only a few of these 
sub-tables are actually needed, so most of the joins are wasted. 
Unfortunately, ahead of time, we don't know which tables will be needed -- 
we're relying on the discriminator.

Ideally, we'd be able to specify that the ORM should subqueryload the 
subclasses (and only execute subqueries on the types that are present). 
This would have to happen both when querying the base table, but also when 
accessing relationships. We'd want it to execute a query on the base table, 
then execute one query for each present subclass.

Another solution might be to use some kind of hook that

- is executed after a query returns with results (or after a list of models 
are added to the session?)
- groups the models by type and runs its own subqueries to load the data

Any help here is greatly appreciated!


Thanks,
Damon

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Performing filter by using string

[Vijaya Sekar]

Hi everyone,

I have an string which has the condition to be performed while retrieving 
from database. I automap my model and make reference to it by using another 
variable name.
How can achieve retrieve data by filter which is in string?

here is my code

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import MetaData


engine = create_engine("sqlite:///chinook.db",  echo = False)

metadata = MetaData()
model = 'employees'
condition = 'employees.City == "Chennai"'
metadata.reflect(engine, only=[model])
base = automap_base(metadata=metadata)
base.prepare()
map_model = base.classes[model]
session = Session(engine)
retrieve = session.query(map_model).filter(condition)
for i in retrieve:
print(i.EmployeeId)



-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.