sqlalchemy

　　

 

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,Float,Enum,Date
from sqlalchemy.orm import sessionmaker

Base=declarative_base()

class Student(Base):
    __tablename__='Stu'
    id=Column(Integer,primary_key=True,nullable=False,autoincrement=True)
    name=Column(String(64),nullable=False)
    age=Column(Float,unique=True)

    def __repr__(self):
        return '<{} (id:{}, name:{}, age:{})>'.format(self.__class__.__name__,self.id,self.name,self.age)

    __str__=__repr__


engine=sqlalchemy.create_engine("mysql+pymysql://root:cruces@localhost:3306/uranus",echo=True)

# create table,drop table
# Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)

Session=sessionmaker()
session=Session(bind=engine)

try:
    # t1=Student()
    # t1.name='ppp'
    # t1.age=44
    # t2=Student()
    # t2.name='ooo'
    # t2.age=88
    lst=[]
    for b in range(5):
        t=Student()
        t.name='uiop'+str(b)
        t.age=30+b
        lst.append(t)

    session.add_all(lst)
    session.commit()
except Exception as e:
    print(e.args,e.__dict__,e)
    session.rollback()
finally:
    pass

 

try:
    s1=Student()
    s1.name='pp'
    s1.age=33
    session.add(s1)
    print(s1.id)
    session.commit()
    print(s1.id,999999999999999999999)

    s1.age=88
    # session.add(s1)  # not necessary
    session.commit()

except Exception as e:
    print(e.args,e.__dict__,e)
    session.rollback()
finally:
    pass

 

查询

try:
    queryobj=session.query(Student).filter(Student.name=='pp')
    for b in queryobj:
        print(b)
except Exception as e:
    print(e.__cause__)
finally:
    pass

 

get方法用主键查询,返回一条传入类的实例

try:
    s=session.query(Student).get(2)
    print(s)
    s.name='uiop'
    s.age=999
    print(s)
    session.commit()
    
except Exception as e:
    print(e.__traceback__)
finally:
    pass

 

删除

try:
    s=Student()
    s.id=2
    session.delete(s)
    session.commit()
except Exception as e:
    print(e.__cause__,e.__class__,e)
    session.rollback()
finally:
    pass

 

 

 

try:
    # s=session.query(Student).filter(Student.id==2)
    s=session.query(Student).get(2)
    session.delete(s)
    session.commit()
except Exception as e:
    print(e.__cause__)
finally:
    pass

 

 

def show(entity):
    ins=inspect(entity)
    print(ins.transient,ins.pending,ins.persistent,ins.deleted,ins.detached)

try:
    # s=session.query(Student).filter(Student.id<6)
    # print(s,8888888888,type(s))
    # for b in s:
    #     print(b,type(b))
    # s=session.query(Student).get(2)
    # print(s)
    # s.name='bbbbbbbbb'
    # session.commit()
    s=session.query(Student).get(5)
    show(s)
    session.delete(s)
    show(s)
    session.flush()
    show(s)
    # session.rollback()
    # show(s)
    session.commit()
    show(s)
except Exception as e:
    print('@'*80)
    print(e.__cause__,e.args,e)
    session.rollback()
finally:
    pass

 

 

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, Enum, Date, inspect,func
from sqlalchemy.orm import sessionmaker
import enum

Base = declarative_base()


class Student(Base):
    __tablename__ = 'Stu'
    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
    name = Column(String(64), nullable=False)
    age = Column(Float, unique=True)

    def __repr__(self):
        return '<{} (id:{}, name:{}, age:{})>'.format(self.__class__.__name__, self.id, self.name, self.age)

    __str__ = __repr__


engine = sqlalchemy.create_engine("mysql+pymysql://root:cruces@localhost:3306/employees")

# create table,drop table
# Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)

Session = sessionmaker()
session = Session(bind=engine)


def show(entities):
    for entity in entities:
        ins = inspect(entity)
        print(entity, ins.transient, ins.pending, ins.persistent, ins.deleted, ins.detached, end='\n')


# | employees | CREATE TABLE `employees` (
#   `emp_no` int NOT NULL,
#   `birth_date` date NOT NULL,
#   `first_name` varchar(14) NOT NULL,
#   `last_name` varchar(16) NOT NULL,
#   `gender` enum('M','F') NOT NULL,
#   `hire_date` date NOT NULL,
#   PRIMARY KEY (`emp_no`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

class Accede(enum.Enum):
    M = 'M'
    F = 'F'


class Employee(Base):
    __tablename__ = 'vv'
    emp_no = Column(Integer, primary_key=True)
    birth_date = Column(Date, nullable=False)
    first_name = Column(String(14), nullable=False)
    last_name = Column(String(16), nullable=False)
    gender=Column(Enum(Accede),nullable=False)
    hire_date = Column(Date, nullable=False)

    def __repr__(self):
        return '<{} (emp_no: {}, name: {}.{}, birth_date: {},Gender: {})>'.format(self.__class__.__name__, self.emp_no,
        self.first_name, self.last_name,self.birth_date,self.gender)

    __str__ = __repr__


# # and
# employees = session.query(Employee).filter((Employee.emp_no < 10010) & (Employee.emp_no > 10005))
# show(employees)
#
# print('$' * 80)
# # or
# employees = session.query(Employee).filter((Employee.emp_no < 10005) | (Employee.first_name == 'Sumant'))
# show(employees)
#
# print('$' * 80)
#
# # not
# employees = session.query(Employee).filter(~(Employee.emp_no <= 10005))
# show(employees)
#
# print('$' * 80)

# in
# lst=[10001,10004,10008]
# employees=session.query(Employee).filter(Employee.emp_no.in_(lst))
# show(employees)
#
# print('$' * 80)
#
# # not in
# employees=session.query(Employee).filter(~Employee.emp_no.in_(lst))
# show(employees)
#
# print('$' * 80)
#
# # like ilike(case insensitive)
# employees=session.query(Employee).filter(Employee.last_name.like('p%'))
# show(employees)
#
# print('$' * 80)
#
# # ascending order
# employees=session.query(Employee).filter(Employee.emp_no < 10006).order_by(Employee.birth_date)
# employees=session.query(Employee).filter(Employee.emp_no < 10006).order_by(Employee.birth_date.asc())
# show(employees)
#
# print('$' * 80)
#
# # descending order
# employees=session.query(Employee).filter(Employee.emp_no < 10008).order_by(Employee.emp_no.desc())
# show(employees)
#
# print('$' * 80)

# multi columns order
# employees=session.query(Employee).filter(Employee.emp_no<100010).order_by(Employee.first_name.asc()).order_by(
#     Employee.emp_no.desc()
# )
# show(employees)
#
# print('$' * 80)

# # pagination
# employees=session.query(Employee).limit(4)
# show(employees)
#
# print('$' * 80)
#
# employees=session.query(Employee).limit(4).offset(8)
# show(employees)

e=session.query(Employee).limit(15)
print(len(list(e)))
# select count(*) from table
print(e.limit(8).count())
# 取所有数据,返回列表
# print(e.all())

# fetch one line,直接返回对象,多行抛异常
print(e.limit(1).one())
print(e.limit(1).scalar())

# delete by query
# session.query(Employee).filter(Employee.emp_no>10018).delete()
# session.commit()

# query=session.query(func.count(Employee.emp_no))
# print(query.one())  # tuple
# print(query.scalar())  # one()[0]
# print(query.all())  # [(18,)]
#
# # max/min/avg
# print(session.query(func.max(Employee.emp_no)).scalar())
# print(session.query(func.min(Employee.emp_no)).scalar())
# print(session.query(func.avg(Employee.emp_no)).scalar())
#
# # group
# print(session.query(func.count(Employee.emp_no)).group_by(Employee.emp_no).all())

 

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, Enum, Date, inspect, func, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
import enum

Base = declarative_base()

engine = sqlalchemy.create_engine("mysql+pymysql://root:cruces@localhost:3306/employees", echo=True)

# create table,drop table
# Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)

Session = sessionmaker()
session = Session(bind=engine)


def show(entities):
    for entity in entities:
        ins = inspect(entity)
        print(entity, ins.transient, ins.pending, ins.persistent, ins.deleted, ins.detached, end='\n')


class Accede(enum.Enum):
    M = 'M'
    F = 'F'


class Employee(Base):
    __tablename__ = 'employees'
    emp_no = Column(Integer, primary_key=True)
    birth_date = Column(Date, nullable=False)
    first_name = Column(String(14), nullable=False)
    last_name = Column(String(16), nullable=False)
    gender = Column(Enum(Accede), nullable=False)
    hire_date = Column(Date, nullable=False)

    def __repr__(self):
        return '<{} (emp_no: {}, name: {}.{}, birth_date: {},Gender: {},depts: {})>'.format(self.__class__.__name__,
                                                                                            self.emp_no,
                                                                                            self.first_name,
                                                                                            self.last_name,
                                                                                            self.birth_date,
                                                                                            self.gender, self.depts)

    depts = relationship('Dept_emp')

    __str__ = __repr__


# | departments | CREATE TABLE `departments` (
#   `dept_no` char(4) NOT NULL,
#   `dept_name` varchar(40) NOT NULL,
#   PRIMARY KEY (`dept_no`),
#   UNIQUE KEY `dept_name` (`dept_name`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

class Department(Base):
    __tablename__ = 'departments'
    dept_no = Column(String(4), primary_key=True)
    dept_name = Column(String(40), unique=True, nullable=False)

    def __repr__(self):
        return '<{} (dept_no: {}, dept_name: {})>'.format(type(self).__name__, self.dept_no, self.dept_name)

    __str__ = __repr__


# | dept_emp | CREATE TABLE `dept_emp` (
#   `emp_no` int NOT NULL,
#   `dept_no` char(4) NOT NULL,
#   `from_date` date NOT NULL,
#   `to_date` date NOT NULL,
#   PRIMARY KEY (`emp_no`,`dept_no`),
#   KEY `dept_no` (`dept_no`),
#   CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
#   CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
class Dept_emp(Base):
    __tablename__ = 'dept_emp'
    emp_no = Column(Integer, ForeignKey('employees.emp_no', ondelete='CASCADE'), primary_key=True)
    dept_no = Column(String(4), ForeignKey('departments.dept_no', ondelete='CASCADE'), primary_key=True)
    from_date = Column(Date, nullable=False)
    to_date = Column(Date, nullable=False)

    def __repr__(self):
        return '<{} (emp_no: {}, dept_no: {}, from_date: {},to_date: {})>'.format(type(self).__name__, self.emp_no
                                                                                  , self.dept_no, self.from_date,
                                                                                  self.to_date)

    __str__ = __repr__


def pagination(entities):
    for b in entities:
        print(b)


# 没有使用join
# results = session.query(Employee, Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(
#     Employee.emp_no == 10010).all()

results = session.query(Employee).join(Dept_emp, Employee.emp_no == Dept_emp.emp_no).filter(
    Employee.emp_no == 10010).all()
# results=session.query(Employee,Dept_emp).join(Dept_emp,Employee.emp_no==Dept_emp.emp_no).filter(Employee.emp_no==10010).all()
results = session.query(Employee).join(Dept_emp, (Employee.emp_no == Dept_emp.emp_no) & (Employee.emp_no == 10010))
pagination(results)