How to create SQL equivalent for PARTITION BY in sqlalchemy

[Nishant Singh]

Hi, 

I am trying to create partition on sql using sqlalchemy. I am using the declarative system. So as we have the following code in simple sql 

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

I am trying to write an equivalent code for my class :

class Employee(Base):
      __tablename__ = "employee"
      __table_args__ = {'mysql_engine': 'InnoDB'}

      emp_id = Column(Integer, nullable=False)
      fname = Column(String,  nullable=False)
      lname = Column(String,  nullable=False)
      job_code = Column(Integer, nullable=False)
      store_id = Column(Integer, nullable=False)

I referred to https://groups.google.com/forum/#!searchin/sqlalchemy/Partition$20by$20oracle%7Csort:date/sqlalchemy/qCQFD2LNyTQ/5WyRUP9oBwAJ , but sort of wrote something similer for my actual class below: 

class VeryRandom(Base):
    __tablename__ = "lil_cool_data"

    __table_args__ = {
        'info': {
            'mysql_partition': """
                 PARTITION BY RANGE(min)
                 (PARTITION p1 VALUES LESS THAN (6),
                  PARTITION p2 VALUES LESS THAN (10),
                  PARTITION p3 VALUES LESS THAN (20),
                  PARTITION p4 VALUES LESS THAN (MAXVALUE)
                  )
             """
        }
    }
    starttime = Column(DateTime, nullable=False)
    min = Column(COL_INTEGER, nullable=False, primary_key=True)
    random5 = Column(COL_INTEGER, nullable=False)
    random4 = Column(Enum('stale', 'fresh'), nullable=False)
    random3 = Column(COL_INTEGER, nullable=False)
    random2 = Column(COL_INTEGER, nullable=False)
    random1 = Column(Boolean, nullable=False, default=False)

But this does not create partion for me. What am i doing wrong ? 

[Mike Bayer]

On Fri, Feb 28, 2020, at 6:04 AM, Nishant Singh wrote:

Hi, 

I am trying to create partition on sql using sqlalchemy. I am using the declarative system. So as we have the following code in simple sql 

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

I am trying to write an equivalent code for my class :

the MySQL dialect supports all table suffixes natively, so there is no need for a custom recipe.

https://docs.sqlalchemy.org/en/13/dialects/mysql.html#create-table-arguments-including-storage-engines

class A(Base):

    __tablename__ = "a"

    id = Column(Integer, primary_key=True)

    store_id = Column(Integer)

    __table_args__ = {

        "mysql_engine": "InnoDB",

        "mysql_partition_by": """

RANGE (store_id) (

    PARTITION p0 VALUES LESS THAN (6),

    PARTITION p1 VALUES LESS THAN (11),

    PARTITION p2 VALUES LESS THAN (16),

    PARTITION p3 VALUES LESS THAN MAXVALUE );

""",

    }

--

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0f9167bd-b766-48b6-a788-e86b18a729c5%40googlegroups.com.

[Nishant Singh]

Yep. Thank you. It works fine now. 

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.