sqlalchemy mysql教程_sqlalchemy操作Mysql

SQLAlchemy“采用简单的Python语言，为高效和高性能的数据库访问设计，实现了完整的企业级持久模型”。SQLAlchemy的理念是，SQL数据库的量级和性能重要于对象集合；而对象集合的抽象又重要于表和行。因此，SQLAlchmey采用了类似于Java里Hibernate的数据映射模型，而不是其他ORM框架采用的Active Record模型。不过，Elixir和declarative等可选插件可以让用户使用声明语法。

安装

pip3 install SQLAlchemy

pip3 install pymysql  #需要pymysql或者MySQLdb的支持

连接数据库

mysql+pymysql://:@/[?]

下面我们看一个例子：

#导入想关模块

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:123456@192.168.11.12:3306/haha", encoding='utf-8', max_overflow=5)

# DBSession = sessionmaker(bind=engine)

#实例化

Base = declarative_base()

# 主机表

class Host(Base): #声明类

__tablename__ = 'host' #需要创建的表名

nid = Column(Integer, primary_key=True, autoincrement=True)　　#定义主键，自增1 ，其实这里还可以有其他参数

hostname = Column(String(32))

ip = Column(String(32), unique=True)

port = Column(String(32))

host_user = relationship('HostUser',secondary=lambda: HostToHostUser.__table__,backref='h')#映射表的外键

# 主机用户表

class HostUser(Base):

__tablename__ = 'host_user'

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

username = Column(String(32))

passwd = Column(String(32))

# 主机用户与主机关系表

class HostToHostUser(Base):

__tablename__ = 'host_to_hostuser'

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

user_nid = Column(Integer, ForeignKey('host_user.nid'))

host_nid = Column(Integer, ForeignKey('host.nid'))

host = relationship('Host', backref='h')

host_user = relationship('HostUser', backref='u')

# 审计表

class AuditLog(Base):

__tablename__ = 'audit_log'

nid = Column(Integer, primary_key=True)

user_nid = Column(Integer, ForeignKey('host_user.nid'))

host_nid = Column(Integer, ForeignKey('host.nid'))

cmd = Column(String(255))

date = Column(String(255))

def init_db():

Base.metadata.create_all(engine)

def drop_db():

Base.metadata.drop_all(engine)

init_db()

Session = sessionmaker(bind=engine)

session = Session()

#

#

session.add_all((

Host(hostname='web1', ip='192.168.11.12', port='22', ),

Host(hostname='web2', ip='192.168.11.23', port='22', ),

Host(hostname='web3', ip='192.168.11.34', port='22', ),

Host(hostname='web4', ip='192.168.11.45', port='22', ),

Host(hostname='web5', ip='192.168.11.228', port='22', ),

))

session.commit()

session.add_all([

HostUser(username='madking', passwd='123', ),

HostUser(username='oldboy', passwd='123', ),

])

session.commit()

session.add_all((

HostToHostUser(user_nid='1', host_nid='1', ),

HostToHostUser(user_nid='1', host_nid='2', ),

HostToHostUser(user_nid='1', host_nid='5', ),

HostToHostUser(user_nid='2', host_nid='1', ),

))

session.commit()