程序步骤:
- 1.定义连接数据库的相关参数
- 2.engine与Base
- 3.定义表征数据库的类,继承Base
- 4.创建表与表的相关操作
代码与功能:
1.定义参数:
# 地址
HOSTNAME = '127.0.0.1'
# 数据库
DATABASE = 'class_database'
# 端口
PORT = '3306'
# 用户名和密码
USERNAME = 'root'
PASSWORD = 'root'
# 继承该函数的基类
DB_URL = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
2.engine和Base
engine = create_engine(DB_URL)
# 都要继承这个函数生成的基类
Base = declarative_base(engine)
3.数据表类
class User(Base):
__tablename__ = 'user1'
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(50))
price = Column(DECIMAL(20,5))
is_delete = Column(Boolean)
gender = Column('sex',Enum('男','女'))
create_time = Column(DateTime)
content = Column(LONGTEXT)
update_time = Column(DateTime,onupdate=datetime.now())
4.数据库相关操作
Base.metadata.drop_all()
# 删除metadata中的所有内容(包括数据库中的数据)
Base.metadata.create_all()
# 按照class创建字段,此时并没有数据
添加数据:
Session = sessionmaker(bind=engine)
session = Session()
user = User(name='cheney',price=10.123,is_delete=False,gender='男',create_time=datetime(2020,10,28,21,38),content='xxxxxxxxxxx',update_time=None)
# 添加数据
session.add(user)
# 提交数据
session.commit()
以上为主要的操作,完整代码:
#! C:\Python\Python36
# -*- coding: utf-8 -*-
# @Time : 2020/11/4 11:33
# @Author : liuchengyong
# @File : 数据类型.py
# @Software: PyCharm
from sqlalchemy import create_engine
from sqlalchemy import Column,Integer,String,DECIMAL,Boolean,Enum,DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker
from datetime import datetime
# 连接数据库
# 地址
HOSTNAME = '127.0.0.1'
# 数据库
DATABASE = 'class_database'
# 端口
PORT = '3306'
# 用户名和密码
USERNAME = 'root'
PASSWORD = 'root'
# 继承该函数的基类
DB_URL = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
engine = create_engine(DB_URL)
# 都要继承这个函数生成的基类
Base = declarative_base(engine)
class User(Base):
__tablename__ = 'user1'
id = Column(Integer,primary_key=True,autoincrement=True)
name = Column(String(50))
price = Column(DECIMAL(20,5))
is_delete = Column(Boolean)
gender = Column('sex',Enum('男','女'))
create_time = Column(DateTime)
content = Column(LONGTEXT)
update_time = Column(DateTime,onupdate=datetime.now())
Base.metadata.drop_all()
Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
user = User(name='cheney',price=10.123,is_delete=False,gender='男',create_time=datetime(2020,10,28,21,38),content='xxxxxxxxxxx',update_time=None)
session.add(user)
user2 = User(name='lcy',price=15.5,is_delete=False)
session.add(user2)
session.commit()