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))

Recommended Posts

A note I was addicted to when creating a table with SQLAlchemy
A note I was addicted to when running Python with Visual Studio Code
A note I was addicted to when making a beep on Linux
A story I was addicted to when inserting from Python to a PostgreSQL table
I was addicted to creating a Python venv environment with VS Code
I was addicted to trying Cython with PyCharm, so make a note
A story that I was addicted to when I made SFTP communication with python
What I was addicted to when combining class inheritance and Joint Table Inheritance in SQLAlchemy
What I was addicted to when dealing with huge files in a Linux 32bit environment
A story I was addicted to trying to get a video url with tweepy
I get a UnicodeDecodeError when trying to connect to oracle with python sqlalchemy
[Go language] Be careful when creating a server with mux + cors + alice. Especially about what I was addicted to around CORS.
I tried to create a table only with Django
[Python] When I tried to make a decompression tool with a zip file I just knew, I was addicted to sys.exit ()
Three things I was addicted to when using Python and MySQL with Docker
I was addicted to scraping with Selenium (+ Python) in 2020
A story that I was addicted to at np.where
What I was addicted to when using Python tornado
I set up TensowFlow and was addicted to it, so make a note
[IOS] GIF animation with Pythonista3. I was addicted to it.
What I was addicted to when migrating Processing users to Python
I was addicted to multiprocessing + psycopg2
When I tried to install PIL and matplotlib in a virtualenv environment, I was addicted to it.
When creating a pipenv environment, I got addicted to "Value Error: Not a valid python path"
The story I was addicted to when I specified nil as a function argument in Go
A story that I was addicted to calling Lambda from AWS Lambda.
A memorandum when I tried to get it automatically with selenium
What I was addicted to when introducing ALE to Vim for Python
When creating .gitignore with gibo, I didn't have to write .python-version.
What I was addicted to with json.dumps in Python base64 encoding
Note that I was addicted to sklearn's missing value interpolation (Imputer)
I was addicted to pip install mysqlclient
What I was addicted to Python autorun
When I put Django in my home directory, I was addicted to static files with permission errors
I was addicted to running tensorflow on GPU with NVIDIA driver 440 + CUDA 10.2
A story I was addicted to trying to install LightFM on Amazon Linux
Use Python from Java with Jython. I was also addicted to it.
[Fabric] I was addicted to using boolean as an argument, so make a note of the countermeasures.
[Introduction to json] No, I was addicted to it. .. .. ♬
I want to make a game with Python
I get a UnicodeDecodeError when running with mod_wsgi
Python Note: When assigning a value to a string
A memo when creating a python environment with miniconda
I want to write to a file with Python
It is more convenient to use csv-table when writing a table with python-sphinx
When I tried to make a VPC with AWS CDK but couldn't make it
When I tried to create a virtual environment with Python, it didn't work
When I connect to a remote Jupyter Server with VScode, it's remote but local
In IPython, when I tried to see the value, it was a generator, so I came up with it when I was frustrated.
I was soberly addicted to calling awscli from a Python 2.7 script registered in crontab
Note that I was addicted to npm script not passing in the verification environment
[Python] I want to use only index when looping a list with a for statement
I want to transition with a button in flask
Points to note when performing logistic regression with Statsmodels
I want to climb a mountain with reinforcement learning
I tried to draw a route map with Python
How to convert a class object to a dictionary with SQLAlchemy
When I tried to do socket communication with Raspberry Pi, the protocol was different
Problems when creating a csv-json conversion tool with python
I got stuck when trying to specify a relative path with relative_to () in python
Things to note when initializing a list in Python