2 primary keys issue

[hevzy86]

Hello, please help me to figure out what's doing on, my original table consists of:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

But when i'm creating model, view and handler, my model looks like this

>>> from employees.models.sql.employees import Employees
setup_schema:employees
>>> t = Employees()
>>> t
id                  : None (primary key) 
created_at          : None
last_updated        : None
emp_no              : None (primary key)
birth_date          : None
first_name          : None
last_name           : None
hire_date           : None

Why i have 3 extra fields and 2 primary keys even if i'm using

_use_pow_schema_attrs = False

? And why i don't have

gender     | enum('M','F')

field? Could someone help me to make it right, thank you.

here is my model employees.py

#
# Model Employees
#
from enum import Enum
from sqlalchemy import Column, Integer, String, Boolean, Sequence
from sqlalchemy import BigInteger, Date, DateTime, Float, Numeric, Unicode, Text
from employees.lib.powlib import relation
from employees.database.sqldblib import Base
from employees.lib.powlib import PowBaseMeta
from sqlalchemy import schema
from sqlalchemy.sql.sqltypes import NULLTYPE
import enum
from sqlalchemy import Integer

# @relation.has_many("<plural_other_models>")
@relation.setup_sql_schema()
class Employees(Base, metaclass=PowBaseMeta):

    #
    # cerberus style schema
    #
    schema = {
        'emp_no':   {'type': 'integer',
                     "sql":
                     {
                         "primary_key": True,
                         "default": NULLTYPE,
                         "unique": True,
                         "nullable": True
                     }, },
        'birth_date':   {'type': 'datetime', "sqltype": "date"},
        'first_name':   {'type': 'string', 'maxlength': 14},
        'last_name':   {'type': 'string', 'maxlength': 16},
        'gender':   {'type': 'string', "allowed": ["M", "F"],  "sqltype": "enum"},
        'hire_date':   {'type': 'datetime', "sqltype": "date"}

    }

    # if you want to define a custom tablename for this model:
    __tablename__ = "employees"

# if you dont want to use the pow schema extension
_use_pow_schema_attrs = False

# define class attributes/variables here that should be included in to_dict()
# conversion and also handed to the encoders but that are NOT part of the schema.
include_attributes = []

# Add sqlalchemy table_args here. Add "autoload" : True for database reflection
__table_args__ = {"extend_existing": True}

#
# init
#

def __init__(self, **kwargs):
    self.setup_instance_values()
    self.init_on_load(**kwargs)
    #
    # your model's methods down here
    #

[pythononwheels]

Hey hevzy86 , currently checking this ... Can you make a

pip freeze | grep pythononwheels

to make sure which version you are using?

[pythononwheels]

Creating the table without the pow default columns (id, created_at, last_updated) and making the emp_no the only primary key works fine ...

To focus on the primary_key / pow attributes issue I changed the gender to

'gender': {'type': 'string', "allowed": ["M", "F"] },

looking at the ENUM problem now..

[hevzy86]

Creating the table without the pow default columns (id, created_at, last_updated) and making the emp_no the only primary key works fine ...

To focus on the primary_key / pow attributes issue I changed the gender to

'gender': {'type': 'string', "allowed": ["M", "F"] },

looking at the ENUM problem now..

Hello, i was using pythononwheels==0.922, i solved my issue by using a different package(sqlAlchemy), thank you

[pythononwheels]

Completely works now.

• emp_no is Primary_key
• pow attributes are not added
• Enum is correctly handled (auto created and handed to the DB through the alembic migration)

I will release a patch 0.923 @hevzy86: Thanks for mentioning this ..

Model: (relevant parts)

...
@relation.setup_sql_schema()
class Test1(Base, metaclass=PowBaseMeta):

    #
    # cerberus style schema
    #
    schema = {        
        'emp_no':   {'type': 'integer',
                     "sql":
                        {
                            "primary_key": True,
                            "default": NULLTYPE,
                            "unique": True,
                            "nullable": True
                        }, 
        },
        'birth_date':   {'type': 'datetime', "sqltype": "date"},
        'first_name':   {'type': 'string', 'maxlength': 14},
        'last_name' :   {'type': 'string', 'maxlength': 16},
        'gender'    :   {'type': 'string', "allowed": ["M", "F"],  "sqltype": "enum"},
        'hire_date' :   {'type': 'datetime', "sqltype": "date"}

    }

    # if you want to define a custom tablename for this model:
    __tablename__ = "employees"

    # if you dont want to use the pow schema extension
    _use_pow_schema_attrs= False
...

The automatically generated alembic migration:

"""employees added

Revision ID: 7c5631899933
Revises: 
Create Date: 2020-07-25 22:55:26.580612

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '7c5631899933'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('employees',
    sa.Column('emp_no', sa.Integer(), nullable=False),
    sa.Column('birth_date', sa.DateTime(), nullable=True),
    sa.Column('first_name', sa.String(length=14), nullable=True),
    sa.Column('last_name', sa.String(length=16), nullable=True),
    sa.Column('gender', sa.Enum('M,F'), nullable=True),
    sa.Column('hire_date', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('emp_no'),
    sa.UniqueConstraint('emp_no')
    )
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('employees')
    # ### end Alembic commands ###

Database: (Sqlite in this case)

Enum is Varchar but you can see that it added the Enum checks

CHECK(gender IN('M','F')

Full view from DB Browser for SQLite:

[hevzy86]

All right, perfect, thank you👍

On Sat, Jul 25, 2020, 5:11 PM khz notifications@github.com wrote:

Completely works now.

• emp_no is Primary_key
• pow attributes are not added
• Enum is correctly handled (auto created and handed to the DB through the alembic migration)

I will release a patch 0.923 @hevzy86 https://github.com/hevzy86: Thanks for mentioning this ..

Model: (relevant parts)

... @relation.setup_sql_schema() class Test1(Base, metaclass=PowBaseMeta):

#
# cerberus style schema
#
schema = {
    'emp_no':   {'type': 'integer',
                 "sql":
                    {
                        "primary_key": True,
                        "default": NULLTYPE,
                        "unique": True,
                        "nullable": True
                    },
    },
    'birth_date':   {'type': 'datetime', "sqltype": "date"},
    'first_name':   {'type': 'string', 'maxlength': 14},
    'last_name' :   {'type': 'string', 'maxlength': 16},
    'gender'    :   {'type': 'string', "allowed": ["M", "F"],  "sqltype": "enum"},
    'hire_date' :   {'type': 'datetime', "sqltype": "date"}

}

# if you want to define a custom tablename for this model:
__tablename__ = "employees"

# if you dont want to use the pow schema extension
_use_pow_schema_attrs= False

...

The automatically generated alembic migration:

"""employees added

Revision ID: 7c5631899933 Revises: Create Date: 2020-07-25 22:55:26.580612

""" from alembic import op import sqlalchemy as sa

revision identifiers, used by Alembic.

revision = '7c5631899933' down_revision = None branch_labels = None depends_on = None

def upgrade():

commands auto generated by Alembic - please adjust!

op.create_table('employees',
sa.Column('emp_no', sa.Integer(), nullable=False),
sa.Column('birth_date', sa.DateTime(), nullable=True),
sa.Column('first_name', sa.String(length=14), nullable=True),
sa.Column('last_name', sa.String(length=16), nullable=True),
sa.Column('gender', sa.Enum('M,F'), nullable=True),
sa.Column('hire_date', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('emp_no'),
sa.UniqueConstraint('emp_no')
)
# ### end Alembic commands ###

def downgrade():

commands auto generated by Alembic - please adjust!

op.drop_table('employees')
# ### end Alembic commands ###

Database: (Sqlite in this case)

[image: grafik] https://user-images.githubusercontent.com/1859655/88466225-5f8ccc80-ceca-11ea-948d-4c3be8af73a3.png

Enum is Varchar but you can see that it added the Enum checks

CHECK(gender IN('M','F')

[image: grafik] https://user-images.githubusercontent.com/1859655/88466334-88fa2800-cecb-11ea-9a4d-9bce7a9819a8.png

[image: grafik] https://user-images.githubusercontent.com/1859655/88466269-e5a91300-ceca-11ea-97df-630bf1b7ea21.png

Full view from DB Browser for SQLite:

[image: grafik] https://user-images.githubusercontent.com/1859655/88466368-e9896500-cecb-11ea-9c85-563e91c4acbe.png

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/pythononwheels/pow_devel/issues/41#issuecomment-663906533, or unsubscribe https://github.com/notifications/unsubscribe-auth/AFRQX4FPFO7S6OWC2VSJWDDR5NC6XANCNFSM4O5LSMCQ .

[pythononwheels]

Tests run:

Patch and new release 0.923 is out.

[hevzy86]

Very good, Thanks 👍

On Wed, Jul 29, 2020, 7:14 PM khz notifications@github.com wrote:

Tests run

[image: Bildschirmfoto 2020-07-29 um 23 34 58] https://user-images.githubusercontent.com/1859655/88862953-dd6e1200-d201-11ea-97ec-53bd6aaf4e21.png

Patch and new release 0.923 is out.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/pythononwheels/pow_devel/issues/41#issuecomment-665977466, or unsubscribe https://github.com/notifications/unsubscribe-auth/AFRQX4A5IXLWSQPZ5VCC623R6CUNHANCNFSM4O5LSMCQ .

[pythononwheels]

fixed.