sqlalchemy

创建表和数据库连接

from sqlalchemy.ext.declarative import declarative_base

Base=declarative_base()


from sqlalchemy import  Column,INT,VARCHAR

class User(Base):
    __tablename__='user'
    id=Column(INT,primary_key=True,autoincrement=True)
    name=Column(VARCHAR(64),index=True)

#2.创建数据引擎

from sqlalchemy import  create_engine
engine=create_engine("mysql+pymysql://root:密码@192.168.50.67:3306/sqlarchm?charset=utf8")


#3.将所有的继承base的class序列化成数据表
Base.metadata.create_all(engine)

 增

from sqlalchemy.orm import sessionmaker
from create_table import engine
#创建查询窗口
Session_window=sessionmaker(engine)
#打开查询窗口
db_session = Session_window()

#1.增加数据
#insert into table(name) value()
from create_table import User

# user_obj=User(name='lucifer') #sql语句
# db_session.add(user_obj) #将sql语句加入查询窗口
# db_session.commit()  #执行全部sql语句
# db_session.close()  #

#2.增加多条数据

# user_obj_list=[User(name='666'),User(name='999')]
#
# db_session.add_all(user_obj_list)
# db_session.commit()
# db_session.close()

删

#原生sql
#delete   from table
#drop table
from sqlalchemy.orm import sessionmaker
from create_table import engine,User

Session=sessionmaker(engine)
db_session=Session()

#1.删除数据
res=db_session.query(User).filter(User.id>=2).delete()
db_session.commit()

改

from create_table import engine,User
from sqlalchemy.orm import sessionmaker

Session=sessionmaker(engine)
db_session=Session()

#原生sql update table set name=123

#修改一条数据
# user_obj=db_session.query(User).filter(User.id==1).update({"name":'xxy'})
#
# print(user_obj) #受影响的行数
#
# db_session.commit()
# db_session.close()

#修改多条数据

user_obj=db_session.query(User).filter(User.id>=1).update({"name":'xxy'})

print(user_obj) #受影响的行数

db_session.commit()

查

from create_table import engine,User
from sqlalchemy.orm import sessionmaker
Session=sessionmaker(engine)
db_session=Session()

#1.查询数据
# user=db_session.query(User).first()
# print(user.id,user.name)

#查询所有数据
# user_obj_list=db_session.query(User).all()
#
# for row in user_obj_list:
#      print(row.id,row.name)

#带条件的查询
# user_obj_list=db_session.query(User).filter(User.id<=2).all()
# for row in user_obj_list:
#      print(row.id,row.name)

user_obj_list=db_session.query(User).filter_by(id=1,name='123').all()

for row in user_obj_list:
     print(row.id,row.name)