A note I was addicted to when creating a table with SQLAlchemy

I thought about creating a table with SQLAlchemy, but I was addicted to it more than I expected, so I made a note.

table definition

The table is defined by a class that inherits Base. (There seems to be multiple methods, but unified to this)

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class User(Base):
	__tablename__ = 'users'

	id = Column(Integer, primary_key=True)
	name = Column(String)
	fullname = Column(String(255))
	password = Column(String(255))

Execute declarative_base to create a Base class. The class definition inherits this.

It is possible to determine the table name with __tablename__. Rails-like things such as "singular class names and multiple table names" are also possible.

Creating a table (basic)

Tables are basically created in a batch with metadata.create_all

engine = create_engine("mysql+pymysql://<user>:<pass>@<host>/<dbname>?charset=utf8")
Base.metadata.create_all(bind=engine)

Pass the database engine to metadata.create_all of the Base class created above. As a result, the tables that inherit Base are collectively CREATE TABLE.

For details on creating an engine, refer to the Official Document. Separately, a database client like PyMySQL is required.

Recreate table

Once create_all is executed, it will not be executed the second time or later. Therefore, even if the table is deleted by SQL etc. and then executed again, the table cannot be created.

In this case, set checkfirst = False as an argument.

Base.metadata.create_all(bind=engine, checkfirst=False)

I want to specify a table and create it

create_all creates a table all at once, but in some cases it is created by specifying a table. In this case, it is possible by passing the table information in the tables argument. The table information is in the __table__ property of the table definition class.

Base.metadata.create_all(bind=engine, tables=[User.__table__])

The point to be careful is to pass the table as an array and the __table__ property. Specifying a table in create_all doesn't seem like a name ... but it can't be helped.

I don't want to serialize primary_key

I don't want to use primary_key as a serial number when I want to bring data from a database in another place and make a mess.

If you do not want the primary_key to be a serial number, specify ʻautoincrement with False`.

class User(Base):
	__tablename__ = 'users'

	id = Column(Integer, primary_key=True, autoincrement=False)
	name = Column(String)
	fullname = Column(String(255))
	password = Column(String(255))