This is the 8th day of my participation in the More text Challenge. For details, see more text Challenge

Today I’m going to introduce a backend development artifact that’s great for when you already have these tables in your database and you want their Model classes to CRUD using ORM technology (or if I don’t know how to write a Modle class at all); Hand-write a model class with 100 tables? This is… It’s not gonna happen. Not in my life. Since we have the SQlacodegen artifact, a single command gets the model class for all tables in the database.

Application scenarios

1. In background development, CRUD operation of the database is often needed;

2, in this process, we often use ORM technology to facilitate CURD, such as mature SQLAlchemy;

3. However, before ORM operations, we need to provide the model class corresponding to the table.

4. Many historical tables already exist in the database.

5. What if there are hundreds of tables? Do you want to write them all yourself?

I’m sure there’s an idea in your mind…

The Gospel

Again, Python is good. Here is an artifact that generates a SQLAlchemy model class based on an existing database (table) structure: SQlacodegen

This is a tool that reads the structure of an existing database and generates the appropriate SQLAlchemy model code, using the declarative style if possible.

Installation method:

pip install sqlacodegen
Copy the code

Make haste to use

You can run a command in the terminal (command line window) to obtain the model of the entire database.

sqlacodegen postgresql:///some_local_db
sqlacodegen mysql+oursql://user:password@localhost/dbname
sqlacodegen sqlite:///database.db
Copy the code

To view specific parameters, enter:

sqlacodegen --help
Copy the code

Parameter Meaning:

optional arguments:
  -h, --help         show this help message and exit
  --version          print the version number and exit
  --schema SCHEMA    load tables from an alternate schema
  --tables TABLES    tables to process (comma-separated, default: all)
  --noviews          ignore views
  --noindexes        ignore indexes
  --noconstraints    ignore constraints
  --nojoined         don't autodetect joined table inheritance
  --noinflect        don't try to convert tables names to singular form
  --noclasses        don't generate classes, only tables
  --outfile OUTFILE  file to write output to (default: stdout)
Copy the code

Currently, I have a table that looks like this in postgres, the default postgresQL database:

create table friends
(
  id   varchar(3) primary key ,
  address  varchar(50) not null ,
  name varchar(10) not null
);

create unique index name_address
on friends (name, address);
Copy the code

In order to operate with ORM, I need to get its Modle class. We’ll just use sqlacodegen to generate it automatically

Sqlacodegen postgresql: / / ridingroad: [email protected]:5432 / postgres -- outfile = models. Py - tables friendsCopy the code

Model class effects

View the output to models.py

# coding: utf-8
from sqlalchemy import Column, Index, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Friend(Base) :
    __tablename__ = 'friends'
    __table_args__ = (
        Index('name_address'.'name'.'address', unique=True),id = Column(String(3), primary_key=True)
    address = Column(String(50), nullable=False)
    name = Column(String(10), nullable=False)
Copy the code

If you have many tables, specify the database (which is an ORM model class that will generate the entire database), and don’t specify each table

Matters needing attention

Why does it sometimes generate classes and sometimes Tables?

Unless the –noclasses option is used, sqlacodegen tries to generate declarative model classes from each table. There are two circumstances in which a Table is generated instead: The table has no primary key constraint (which is required by SQLAlchemy for every model class) association table between two other tables

When a table field lacks a primary key or the table has two foreign key constraints, a table class is generated instead of a model class. For example, my table is structured like this:

create table friends
(
  id   varchar(3) ,
  address  varchar(50) not null ,
  name varchar(10) not null
);

create unique index name_address
  on friends (name, address);
Copy the code

Run the same command again:

Sqlacodegen postgresql: / / ridingroad: [email protected]:5432 / postgres -- outfile = models. Py - tables friendsCopy the code

Gettable (Table);

# coding: utf-8
from sqlalchemy import Column, Index, MetaData, String, Table

metadata = MetaData()


t_friends = Table(
    'friends', metadata,
    Column('id', String(3)),
    Column('address', String(50), nullable=False),
    Column('name', String(10), nullable=False),
    Index('name_address'.'name'.'address', unique=True))Copy the code

It’s similar to a model class, but try to include a primary key to avoid manually changing it to a model class