Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

MySQL > alter table test; MySQL > alter table test;

Now create a table of workers using SQLAlchemy as follows:

1. Create a data table

1. Create method 1

from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, DateTime Base = declarative_base() engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test?charset=utf8', echo=True) class Worker(Base): # tablename __tablename__ = 'worker' ID = Column(Integer, primary_key=True) name = Column(String(50), Unique =True) age = Column(Integer) birth = Column(DateTime) part_name = Column(String(50)) # create table Base.metadata.create_all(engine)Copy the code

Let’s see if the database is actually created

We compared the query information of the two databases before and after, and indeed a new table named Worker was created.

This method introduces the declarative_base module, generates its object Base, and creates a class Worker. In general, data table names and class names are the same. Tablename specifies the name of the table. It can be ignored. When ignored, the default class name is the tablename. Then create fields ID, name, age, birth, part_name, and base.metadata.create_all (engine) to create tables in the database

1.2 Creation Method 2

Base.metadata.create_all(engine) from sqlalchemy import create_engine from sqlalchemy.dialects.mysql import INTEGER,CHAR  from sqlalchemy import Column, MetaData, ForeignKey, Table meta = MetaData() engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test? charset=utf8', echo=True) part = Table("part", meta, Column('id', INTEGER, primary_key=True), Column('part_name', CHAR(50), ForeignKey(Worker.name)), Column('part_no', INTEGER) ) part.create(bind=engine)Copy the code

Take a look at databases

When we compare the previous and previous database queries, a table named Part is indeed created. This method is very different from the previous method of creating a table. The code is biased towards the SYNTAX of SQL creating a table, and the modules introduced are different, which leads to the different syntax of creating a table.

Method 1 does not generate an error while method 2 does.

2. Delete the data table

When deleting a data table, the data table with foreign keys must be deleted first, that is, part must be deleted before worker can be deleted. Foreign keys are involved between the two, which is the rule for deleting a data table in the database. Delete statements are also different for tables created in different ways.

Database tables created in the first way

Base.metadata.drop_all(engine)
Copy the code

Database tables created in the second way

part.drop(bind=engine)
Copy the code

Database situation before delete:

Drop a database table:

part.drop(bind=engine)
Base.metadata.drop_all(engine)
Copy the code

Take a look at the results of the database:

Note: Regardless of whether a database table has been created or not, the attributes and fields of the data table must be class-defined when SQLAlchemy is used. That is, no matter how you create a table, when using SQLAlchemy, the first step is to create a database connection, the second step is to define classes to map the table, and the properties of the class map the fields of the table.