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
On Thursday, March 23, 2017 at 7:17:13 AM UTC-7, Mike Bayer wrote:
>
> 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 [email protected] <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > To post to this group, send email to [email protected]
> <javascript:>
> > <mailto:[email protected] <javascript:>>.
> > 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
