python学习之day11

目录

SqlAlchemy

外键

---

SqlAlechemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

MySQL-Python

    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

pymysql

    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector

    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

cx_Oracle

    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

创建表（实例方法）

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey

metadata = MetaData()

user = Table('user', metadata,

    Column('id', Integer, primary_key=True),

    Column('name', String(20)),

)

color = Table('color', metadata,

    Column('id', Integer, primary_key=True),

    Column('name', String(20)),

)   #表结构

engine = create_engine("mysql+pymysql://root:1234@127.0.0.1:3306/mydb?charset=utf8", max_overflow=5)#链接数据库

metadata.create_all(engine)

 #!/usr/bin/env python

 # -*- coding:utf-8 -*-

 from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey

 metadata = MetaData()

 user = Table('user', metadata,

     Column('id', Integer, primary_key=True),

     Column('name', String(20)),

 )

 color = Table('color', metadata,

     Column('id', Integer, primary_key=True),

     Column('name', String(20)),

 )

 engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)

 conn = engine.connect()

 # 创建SQL语句，INSERT INTO "user" (id, name) VALUES (:id, :name)

 conn.execute(user.insert(),{'id':7,'name':'seven'})

 conn.close()

 # sql = user.insert().values(id=123, name='wu')

 # conn.execute(sql)

 # conn.close()

 # sql = user.delete().where(user.c.id > 1)

 # sql = user.update().values(fullname=user.c.name)

 # sql = user.update().where(user.c.name == 'jack').values(name='ed')

 # sql = select([user, ])

 # sql = select([user.c.id, ])

 # sql = select([user.c.name, color.c.name]).where(user.c.id==color.c.id)

 # sql = select([user.c.name]).order_by(user.c.name)

 # sql = select([user]).group_by(user.c.name)

 # result = conn.execute(sql)

 # print result.fetchall()

 # conn.close()

增删改查

创建表（类方法）

#!/usr/bin/env python

# -*- coding:utf-8 -*-

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,Integer,String

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine,and_,or_

Base = declarative_base()       #生成一个SqlORM的基类

engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s12",echo=True)

class Host(Base):

    __tablename__ = 'hosts'

    id = Column(Integer,primary_key=True,autoincrement=True)

    hostname = Column(String(64),unique=True,nullable=False)

    ip_addr = Column(String(128),unique=True,nullable=False)

    port = Column(Integer,default=22)

Base.metadata.create_all(engine)     #创建所有表结构

if __name__ == '__main__':

    SessionCls = sessionmaker(bind=engine)  #创建与数据库的会话

    session = SessionCls()  #链接的实例

    # add（添加数据）

    # h2 = Host(hostname='localhost',ip_addr='127.0.0.1')

    # h3 = Host(hostname='ubuntu5',ip_addr='192.168.1.24',port=20000)

    # session.add(h2)

    # session.add(h3)

    # session.add_all([h2,h3])

    # update（修改数据）

    # obj = session.query(Host).filter(Host.hostname=='localhost').first()#查询数据

    # print("++>",obj)

    # obj.hostname = "test server"

    # delete（删除数据）

    # obj = session.query(Host).filter(Host.hostname=='test server').first()

    # session.delete(obj)

    # session.commit()

 # ########## 增 ##########

 # u = User(id=2, name='sb')

 # session.add(u)

 # session.add_all([

 #     User(id=3, name='sb'),

 #     User(id=4, name='sb')

 # ])

 # session.commit()

 # ########## 删除 ##########

 # session.query(User).filter(User.id > 2).delete()

 # session.commit()

 # ########## 修改 ##########

 # session.query(User).filter(User.id > 2).update({'cluster_id' : 0})

 # session.commit()

 # ########## 查 ##########

 # ret = session.query(User).filter_by(name='sb').first()

 # ret = session.query(User).filter_by(name='sb').all()

 # print ret

 # ret = session.query(User).filter(User.name.in_(['sb','bb'])).all()

 # print ret

 # ret = session.query(User.name.label('name_label')).all()

 # print ret,type(ret)

 # ret = session.query(User).order_by(User.id).all()

 # print ret

 # ret = session.query(User).order_by(User.id)[1:3]

 # print ret

 # session.commit()

增删改查

---

外键——一对多