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

[mike bayer]

Here is a recipe using compilation extension
(http://docs.sqlalchemy.org/en/rel_1_1/core/compiler.html):

"""
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
);
"""

from sqlalchemy.schema import CreateTable
from sqlalchemy.ext.compiler import compiles
import textwrap


@compiles(CreateTable, "oracle")
def _add_suffixes(element, compiler, **kw):
text = compiler.visit_create_table(element, **kw)
if "oracle_partition" in element.element.info:
text += textwrap.dedent(
element.element.info["oracle_partition"]).strip()
return text


if __name__ == '__main__':
from sqlalchemy import create_engine, DATE, Table, MetaData, Column
from sqlalchemy.dialects.oracle import NUMBER

# use mock strategy just to illustrate this w/o my getting
# on an oracle box
def execute_sql(stmt):
print stmt.compile(dialect=engine.dialect)
engine = create_engine("oracle://", execute_sql, strategy="mock")

m = MetaData()
t = Table(
'sales_hash', m,
Column('s_productid', NUMBER),
Column('s_saledate', DATE),
Column('s_custid', NUMBER),
Column('s_totalprice', NUMBER),
info={
"oracle_partition": """

PARTITION BY HASH(s_productid)
( PARTITION p1 TABLESPACE tbs1
, PARTITION p2 TABLESPACE tbs2
, PARTITION p3 TABLESPACE tbs3
, PARTITION p4 TABLESPACE tbs4
)
"""
}

)

m.create_all(engine, checkfirst=False)

On 03/22/2017 10:11 PM, Matthew Moisen wrote:
> 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

> <http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html?highlight=partition#partitioning-strategies>.
>
> Checking the docs
> <http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=prefixes#sqlalchemy.schema.Table> for `Table`, I did not see any "postfixes" or similar.

>
> Likewise in the Oracle Dialect page

> <http://docs.sqlalchemy.org/en/latest/dialects/oracle.html>, it didn't mention table partitions.
>
> I took a look at the Customizing DDL <http://docs.sqlalchemy.org/en/latest/core/ddl.html> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Matthew Moisen]

Thanks Mike, I can confirm this works.

For reference, if anyone is using ORM, you can provide the `info` as an attribute to `__table_args__`, like:

class Foo(Base):
    __tablename__ = 'foo'
    name = Column(String(10), primary_key=True)
    __table_args__ = {
        'info': {
            'oracle_partition': """
                 PARTITION BY HASH(name)

                 ( PARTITION p1 TABLESPACE tbs1
                 , PARTITION p2 TABLESPACE tbs2
                 , PARTITION p3 TABLESPACE tbs3
                 , PARTITION p4 TABLESPACE tbs4
                 )
             """
        }
    }

Best regards,

Matthew

> <mailto:sqlalchemy+unsub...@googlegroups.com>.