SQLAlchemy 在MySQL创建表示例程序

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from datetime import date
#we are able to create database table and base with python code.
HOST_NAME='localhost'
PORT='3306'
DATABASE='my_database'
USER_NAME='root'
PASSWORD='135246'
DB_URI="mysql+pymysql://{username}:{password}@{host}:{port}/{db}".format(username=USER_NAME,password=PASSWORD,host=HOST_NAME,port=PORT,db=DATABASE)

#mySQL tips:
#login mySQL: mysql -hlocalhost -uroot -p          then type password.
#show all database:   show database
#use certain databse: use database_name
#check out mySQL status:   status
#show all tables:   show tables

if __name__=="__main__":
    engine=create_engine(DB_URI)
    conn=engine.connect()
    Base=declarative_base(engine)

    #create table Person(
    #   id int primary key autoincrement,
    #   name varchar(50),
    #   age int
    #   )
    #one table is mapped by one class. the class below is to execute the SQL statement above.
    class Person(Base):
        __tablename__='Person'
        id=Column(Integer,primary_key=True,autoincrement=True)
        name=Column(String(50))
        age=Column(Integer)
    class MyEnum():
        option1='A'
        option2='B'
        option3='C'

    class Article(Base):
        __tablename__='article'
        id=Column(Integer,primary_key=True,autoincrement=True)
        price=Column(Float)
        precise_price=Column(DECIMAL(20,10))#DECIMAL type saves value precisely up to 20 bits with 10bits behind . symbol.
        is_deleted=Column(Boolean)#often used for cancel delete operation in website.
        option1=Column(Enum('A','B'))#Enum limits the value option.
        option2 = Column(Enum(MyEnum))  # you can also do like this to use your own enum
        create_time=Column(Date)
        text=Column(Text)#no length required.
        # long_text = Column(LONGTEXT)  # long text type is only available in mysql.
    #create table.
    Base.metadata.create_all()
    #statement below is to delete all tables
    #Base.metadata.drop_all()
    #note: you can not alter table with alchemy! if you wrongly edit tables, you have to delete all tables!

    #operate table, we need session
    session=sessionmaker(engine)()
    #one row is mapped by a class object variant, here we get article object.
    article=Article(price=11.1,is_deleted=True,create_time=date(2018,5,30))
    #session.add means SQL insert operation.
    session.add(article)
    #to session.commit is to save changes.
    session.commit()

 

alchemySQL官方数据常用类型

专为MySQL的https://docs.sqlalchemy.org/en/13/dialects/mysql.html#mysql-data-types

通用数据库的数据类型类 https://docs.sqlalchemy.org/en/13/core/types.html

简介首页：https://docs.sqlalchemy.org/en/13/index.html