Table definition in SQLAlchemy

Introduction

Here, we will summarize the method for defining a table using SQLAlchemy. ** Hereafter, dialect (DB type) is mysql. ** **

Charset specification

It can be specified by mysql_charset of__table_args__. For example, if you want to specify charset as utf8mb4

class User(Base):
    __table_args__=({"mysql_charset": "utf8mb4"})

And it is sufficient. By the way, if you specify utf8, Warning will be issued as follows.

Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")

ROW_FORMAT=DYNAMIC The maximum key length of the index is 3072 bytes, but if the mysql version is 5.6 or earlier, the default is 767 bytes. Therefore, if you use 5.6 or earlier and write as below, an error will occur.

class User(Base):
    __table_args__=({"mysql_charset": "utf8mb4"})
    a=Column(String(255), unique=True)

This is because utf8mb4 is specified, so 255 * 4 = 1020 (byte), which exceeds 767 bytes. (By the way, in the case of utf8mb3, 255 * 3 = 765 (byte) is okay) Therefore, by setting ROW_FORMAT = DYNAMIC, the maximum key length of the index becomes 3072 bytes. You can write as follows.

class User(Base):
    __table_args__=({"mysql_charset": "utf8mb4", "mysql_row_format": "DYNAMIC"})
    a=Column(String(255), unique=True)

In 5.7 and later, by default, ROW_FORMAT = DYNAMIC.

DB engine specification

It can be specified by mysql_engine of__table_args__. For example, if you want to specify the DB engine for InnoDB

class User(Base):
    __table_args__=({"mysql_engine": "InnoDB"})

And it is sufficient. Data Types As the Generic Types of SQLAlchemy, you can import and use it with from sqlalchemy.types import hogehoge. For example, there are the following.

from sqlalchemy.types import Float
from sqlalchemy.types import Integer
from sqlalchemy.types import String
from sqlalchemy.types import DateTime

For these Generic Types, SQLAlchemy will select the appropriate Data Types according to the type of DB to be used when CREATE TABLE. Official Documents (Generic Types)

However, for example, unsigned int, tiny int, and timestamp type are not included in Generic Types. In such a case, you can use from sqlalchemy.dialects.mysql import fugafuga.

from sqlalchemy.dialects.mysql import INTEGER as Integer
from sqlalchemy.dialects.mysql import TINYINT as Tinyint
from sqlalchemy.dialects.mysql import TIMESTAMP as Timestamp

class User(Base):
    a=Column(Integer(unsigned=True)) #a is unsigned int
    b=Column(Tinyint(1)) #b is tiny int(1)
    c=Column(Timestamp) #c is timestamp

(Official Documentation (MySQL Data Types)) Primary Key You can set primary_key = True in Column.

class User(Base):
    id=Column(Integer, primary_key=True) #id is Primary Key

If you want to make a compound Primary Key, you can set primary_key = True for both.

class User(Base):
    # id_1 and id_2 is a composite Primary Key
    id_1=Column(Integer, primary_key=True)
    id_2=Column(Integer, primary_key=True)

Auto Increment In Column, set ʻautoincrement = True`.

class User(Base):
    id=Column(Integer, autoincrement=True) #id is Auto Increment

Default value

You can set server_default = hogehoge in Column. Note that you must pass string or text () to server_default.

from sqlalchemy.dialects.mysql import TINYINT as Tinyint

class User(Base):
    bool=Column(Tinyint(1), server_default="1") #To pass a string

If the passed one is a string, it changes to single quotes, and if it is text (), it changes without quotes.

x=Column(String, server_default="val")
x STRING DEFAULT 'val' #Change to single quotes
y=Column(DateTime, server_default=text("NOW()")
y DATETIME DEFAULT NOW() #No quote

Official documentation (server_default)

If you want the Default value to be current_timestamp, set from sqlalchemy.sql.functions import current_timestamp andserver_default = current_timestamp ().

from sqlalchemy.dialects.mysql import TIMESTAMP as Timestamp
from sqlalchemy.sql.functions import current_timestamp

class User(Base):
    created_at=Column(Timestamp, server_default=current_timestamp())

Official documentation (current_timestamp) )

Update value

It is not reflected even if server_onupdate = hogehoge is set in Column. Therefore, if you want to set DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, use server_default.

from sqlalchemy.dialects.mysql import TIMESTAMP as Timestamp
from sqlalchemy.sql.expression import text

class User(Base):
    created_at=Column(Timestamp, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

And it is sufficient. How to set DEFAULT ON UPDATE CURRENT_TIMESTAMP in mysql with sqlalchemy?

Null constraint

Set nullable = False in the Column.

class User(Base):
    a=Column(Integer, nullable=False) #a is int and not null

Unique constraint

If you want to put a Unique constraint on a single column, you can set ʻunique = True` in Column.

class User(Base):
    a=Column(Integer, unique=True) #a is int and unique

If you want to put unique constraints on multiple columns, you have to write sqlalchemy.schema.UniqueConstraint in __table_args__.

from sqlalchemy.schema import UniqueConstraint

class User(Base):
    #(a, b)In unique
    __table_args__=(UniqueConstraint("a", "b", name="uq_user_00"))
    a=Column(Integer)
    b=Column(Integer)

Of course, you can use sqlalchemy.schema.UniqueConstraint even when you want to put a Unique Constraint on a single column.

Foreign Key constraint

How to use

In Column, set sqlalchemy.schema.ForeignKey. Basically, sqlalchemy.schema.ForeignKey ("{table_name}. {Column_name} ", name =" {foreign_key_constraint_name} ")

from sqlalchemy.schema import ForeignKey

class User(Base):
    __tablename__="users"
    id=Column(Integer, nullable=False, autoincrement=True, primary_key=True)
    name=Column(String(255), nullable=False)

class TestResult(Base):
    __tablename__="test_results"
    id=Column(Integer, nullable=False, autoincrement=True, primary_key=True)
    value=Column(Integer, nullable=False)
    user_id=Column(Integer, ForeignKey("users.id", name="fk_test_results_00", nullable=False)) #The id of the users table is ForeignKey

If you want to set OnUpdate or OnDelete, add ʻonupdate =" CASCADE " or ʻondelete =" CASCADE " to sqlalchemy.schema.ForeignKey (). Official documentation (ForeignKey.params.column) As a caveat, among the options to be written in Column, DataType and ForeignKey must be written before, and those passed with "=" must be written after. relation By creating a relation, you can follow the Foreign Key without explicitly joining. The relation can be set to hoge = sqlalchemy.orm.relationship ("{class name}", uselist = {True or False}) as in the column definition. Uselist is True when there are multiple data to be linked to one data, and False when there is only one. In the following, User: TestResult = one: many.

from sqlalchemy.schema import ForeignKey
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__="users"
    id=Column(Integer, nullable=False, autoincrement=True, primary_key=True)
    name=Column(String(255), nullable=False)
    test_result=relationship("TestResult", uselist=True) #Create a relationship from User to TestResult,Multiple data to be linked

class TestResult(Base):
    __tablename__="test_results"
    id=Column(Integer, nullable=False, autoincrement=True, primary_key=True)
    value=Column(Integer, nullable=False)
    user_id=Column(Integer, ForeignKey("users.id", name="fk_test_results_00", nullable=False)) #The id of the users table is ForeignKey
    user=relationship("User") #Create a relationship from TestResult to User,One piece of data to connect

By setting a relation like this, you can call an instance of each class as follows.


test_result_1=session.query(TestResult).filter(TestResult.id==1).one() # test_In the results table, retrieve the one with id 1.
user_of_test_result_1=test_result_1.user #test_result_1 user_You can retrieve an instance of User class that has an id that matches the id.
# user_of_test_result_1=User

user_1=session.query(User).filter(User.id==1).one() #In the users table, retrieve the one with id 1.
test_results_of_user_1=user_1.test_result # user_User matching id of 1_You can retrieve a list of instances of the TestResult class with id
# test_results_of_user_1=[TestResult, TestResult, ...]

One of the options is lazy, and this parameter allows you to set the timing at which the table to which the relation is set is read. By default, it is loaded when called.

lazy = "select" (same for "True")

Same as the default. It is read when the relation destination is called.

test_result_1=session.query(TestResult).filter(TestResult.id==1).one() #User is not loaded here yet.
user_of_test_result_1=test_result_1.user #User is loaded here

lazy="immediate" When the relation source is called, the relation destination is also called. The query is issued separately to call the relation source and relation destination.

test_result_1=session.query(TestResult).filter(TestResult.id==1).one() #User is also loaded here

lazy = "joined" (same for "False")

When the relation source is called, the relation destination is also called. The difference from lazy = "immediate" is the number of times the query is issued. Since it is called by joining, the query can be issued only once. Official documentation (relationship)

References

This article was written with reference to the following information. ・ Official Document -Sqlalchemy table definition tips -Notes on methods and parameters in SQLAlchemy relations -[4 basic patterns of relational mapping using Python's O / R mapper SQLAlchemy](https://momijiame.tumblr.com/post/27327972441/python-%E3%81%AE-or%E3%83%9E%] E3% 83% 83% E3% 83% 91% E3% 83% BC-sqlalchemy-% E3% 82% 92% E4% BD% BF% E3% 81% A3% E3% 81% 9F% E3% 83% AA % E3% 83% AC% E3% 83% BC% E3% 82% B7% E3% 83% A7% E3% 83% 8A% E3% 83% AB% E3% 83% 9E% E3% 83% 83% E3 % 83% 94% E3% 83% B3% E3% 82% B0% E5% 9F% BA% E6% 9C% AC-4) -How to set DEFAULT ON UPDATE CURRENT_TIMESTAMP in mysql with sqlalchemy?

Recommended Posts

Table definition in SQLAlchemy
sqlalchemy table definition tips
OperationalError in SQLAlchemy + SQLite3
Get table dynamically with sqlalchemy
Rename table columns in Django3
Output table structure in Django
Save Time type in SQLAlchemy
Generate SQLAlchemy table definition from existing MySQL server using sqlacodegen
Implement Table Driven Test in Java
How to handle session in SQLAlchemy
Function argument type definition in python
(sqlalchemy) Display text in select field
DB table insertion process using sqlalchemy
sqlalchemy
Write a short property definition in Python
Associate the table set in python models.py
How to display multiplication table in python
Code required for API conversion in sqlalchemy