03 单表操作

增加数据

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
# localhost
HOSTNAME = '127.0.0.1'
DATABASE = 'demo'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'
DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)
Base.metadata.create_all()
article = Article(name='ecithy')
article1 = Article(name='fullStack')
print(article.name)
# None
print(article.id)
# 保存到数据库中
# 类的实例化   __call__  将类变成方法去调用
Session = sessionmaker(bind=engine)
session = Session()
# 添加
session.add(article1)
session.add_all([article, article1])
# 提交
session.commit()

CURD

# @ Time    : 2021/1/31
# @ Author  : ecithy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
# localhost
HOSTNAME = '127.0.0.1'
DATABASE = 'demo'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'
DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Article(Base):
    __tablename__ = 'art'
    id = Column(Integer, primary_key=True)
    title = Column(String(50), nullable=False)
    content = Column(String(50))
    author = Column(String(50))
    def __str__(self):
        return "Article(title:{},content:{},author:{})".format(self.title, self.content, self.author)
Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
def add_data():
    article = Article(title='Python', content='人生苦短,我用Python', author='龟叔')
    article1 = Article(title='fullStack', content='人生苦短,我玩全栈', author='ecithy')
    # session.add(article)
    session.add_all([article, article1])
    session.commit()
def search_data():
    # all 查询所有
    # data = session.query(Article).all()
    # for item in data:
    #     print(item)
        # print(item.title)
        # print(item.content)
    # print(data)
    # 条件 filter
    data = session.query(Article).filter(Article.title=='Python').all()
    for item in data:
        print(item, item.title)
    # data = session.query(Article).filter_by(title='JAVA').all()
    # for item in data:
    #     print(item)
    # print(data)
    # 查询第一条
    # data = session.query(Article).first()
    # print(data)
    # get 方法 传的ID 不存在 返回None
    # data = session.query(Article).get(1)
    # print(data)
def update_data():
    # 查询出要修改的这条记录
    article = session.query(Article).first()
    print(article)
    article.title = 'Python'
    print(article.title)
    # session.rollback() 用于try异常后
    # print(article.title)
    session.commit()
    # 回滚
    # A 100  B 0
    # A 0  B 100
    # 撤回
def delete_data():
    # 查询出要修改的这条记录
    article = session.query(Article).first()
    # 误操作
    # is_delete 1 未删除  0 删除
    # 修改操作  is_delete 1=>0
    session.delete(article)
    session.commit()
if __name__ == '__main__':
    pass
    # add_data()
    # search_data()
    # update_data()
    # delete_data()