Python_sqlalchemy模块对数据库的基本操作

1.初始sqlalchemy

# Author : Xuefeng

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, func
from sqlalchemy.orm import sessionmaker


engine = create_engine("mysql+pymysql://Jim:123456@localhost/testdb",
                       encoding="utf-8",echo=True)
Base = declarative_base()   # 生成orm基类

class User(Base):
    __tablename__="user"
    id=Column(Integer,primary_key=True)
    name=Column(String(32),nullable=False)
    password=Column(String(64),nullable=False)

    def __repr__(self):
        return "<%s name %s>" % (self.id,self.name)
class Student(Base):
    __tablename__="Student"
    id=Column(Integer,primary_key=True)
    name=Column(String(32),nullable=False)
    password=Column(String(64),nullable=False)

    def __repr__(self):
        return "<%s name %s>" % (self.id,self.name)


Base.metadata.create_all(engine) # 创建表结构
Session_class = sessionmaker(bind=engine)
Session = Session_class()
# # Append the object to the table
# obj1 = User(name="Jim", password="123456")
# obj2 = User(name="Tom", password="123456")
# Session.add(obj1)
# Session.add(obj2)
# Session.commit()

# # query the object from the table
# fiter by the condition
# data = Session.query(User).filter_by(id=1).all()
# fiter by cpmpare the paramater
# data = Session.query(User).filter(User.id>1).all()
# query by multicondition
data = Session.query(User).filter(User.id>0).filter(User.id<4).all()

# Change the object of the table
# default all object have been fetched,and all can be changed to first for fetching the first object
# data = Session.query(User).filter_by().first()
print(data)
# data.name = "xiaoqiang"
# data.password = "000000"
# # Submit the change for the table
# Session.commit()
# print(data[0].name,data[1].name)

# Count the number of the object that follow the condition
# print(Session.query(User).filter(User.id>0).count())

# Group the table by some attribute
# print(Session.query(User.name,func.count(User.name)).group_by(User.name).all())

# # 进行两个标段连接
# print(Session.query(User,Student).filter(User.id==Student.id).all())
# # 必须要存在外间关联
# print(Session.query(User).join(Student).all())

2.创建表单

# Author : Xuefeng
from sqlalchemy.orm import mapper
from sqlalchemy import  Column,ForeignKey,MetaData,Table,Integer,String

metadata = MetaData()

user = Table("user", metadata,
             Column("id", Integer, primary_key=True),
             Column("name", String(32)),
             Column("password",String(32)))
class User(object):
    def __init__(self, name, password):
        self.name = name
        self.password = password

mapper(User,user)