Here, we will summarize the method for defining a table using SQLAlchemy. ** Hereafter, dialect (DB type) is mysql. ** **
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.
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
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) )
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?
Set nullable = False in the Column.
class User(Base):
a=Column(Integer, nullable=False) #a is int and not null
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.
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.
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
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)
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