python处理数据库_python处理数据库工具sqlalchemy 原

安装sqlalchemy

pip install sqlalchemy

数据库基本操作

数据库连接

使用create_engine创建数据库连接；

例子：

from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')

connect = engine.connect()

创建表

方法一：

from sqlalchemy import Table, MetaData, create_engine

engine = create_engine("mysql+mysqlconnector://root:password@localhost:3306/test")

metadata = MetaData(bind=engine)

t1 = Table('users',

metadata,

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

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

Column('fullname', String(50)),

Column('password', String(20))

)

t2 = Table('address',

metadata,

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

Column('email_address',String(50), nullable=False),

Column('user_id', INT, ForeignKey('users.id'))

)

t1.create()

t2.create()

方法二

from sqlalchemy import Table, MetaData, create_engine

engine = create_engine("mysql+mysqlconnector://root:password@localhost:3306/test")

metadata = MetaData()

t1 = Table('users',

metadata,

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

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

Column('fullname', String(50)),

Column('password', String(20))

)

t2 = Table('address',

metadata,

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

Column('email_address',String(50), nullable=False),

Column('user_id', INT, ForeignKey('users.id'))

)

metadata.create_all(engine)

String 对象使用时，++必须声明大小++，对应的是mysql数据库的varchar。

==区别==:方法一是直接在连接的基础上定义了模式【直接将数据库和模式对应】，而方法二先定义模式，侯江模式设置到特定的数据库中。

==重定义表结构==：在表定义的语句末尾添加extend_existing=True。

表和类

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, INT, String, ForeignKey

from sqlalchemy.orm import backref,relationship

Base = declarative_base()

class User(Base):

__tablename__ = "users"

id = Column(INT, primary_key=True)

name = Column(String)

fullname = Column(String)

password = Column(String)

class Address(Base):

__tablename__ = "addresses"

id = Column(INT, primary_key = True)

email_address = Column(String, nullable = False)

user_id = Column(INT, ForeignKey('users.id'))

user = relationship('User', backref=backref('addresses',order_by=id))

Column函数

第一个参数是元组的数据类型

primary_key指定主键，

nullable指定元组是否可以为空(nullable = False表示元组不许为空)

ForeignKey指定外键约束，例如ForeignKey('users.id')，表示addresses表外键约束users表的主键id

relatioship函数[^2x]：将会告知ORM通过Address.user,Address类自身必须链接到User类。relationship()使用两个表的外键约束来判定这种链接的性质。比如说判定Address.user将会是多对一(many-to-one)关系。

backref函数[^2x]：它将提供一种用于反向查询的细节，比如说在对象User上的Address对象集是通过User.addresses属性引用，那么多对一的关系(many-to-one)反向总会是一对多关系(one-to-many)。还有对于Address.user和User.addresses的关系来说总是双向的。（一般与relationship函数结合使用。）

会话

使用sessionmaker来绑定数据库连接，并建立会话。

例子：

from sqlalchemy import Column, String, create_engine

from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')

Session = sessionmaker(bind = engine)

session = Session()

==连接信息格式==：数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名,如果碰到中文乱码问题，可在后面加上?charset=utf8。

==注意==：此处的session相当于java中的statement一样，具有操作数据库的句柄，可以执行sql语句session.execute("sql语句")。

添加数据

方法一：使用session执行sql语句方式

session.execute('insert into users values(2,"Bob","Bob hgf", "hgf")')

session.commit()

方法二：使用映射类成员变量的数据

user = User(id="1", name="alice", fullname="alice hgf", password="hgf")

session.add(user)

session.commit()

查询操作

查询并获取所有结果

users = session.query(User).all()

==说明==：上述语句返回所有的users表中的数据并以User对象的形式储存在列表中。

for x in query:

print x

结果：

<__main__.User object at 0x36aa990>

<__main__.User object at 0x36aaad0>

查询并排序

根据表中的某一个元组排序，使用order_by语句

q = session.query(Address).order_by(desc(Address.user_id))

result = q.all()

for x in result:

print x.email_address

==说明==:执行查询函数后，再使用all()函数,才能将结果映射成类的列表，类中储存从数据库中获取的一行数据。

过滤查询

q = session.query(Address).filter(Address.user_id == 1)

result = q.all()

for x in result:

print x.email_address

连接查询

q = session.query(Address).join(Address.user).group_by(Address.id)

result = q.all()

for x in result:

print x.email_address

常见的内联函数，聚合函数

求平均值：q = session.query(func.avg(Address.id))

Count:session.query(Address).filter(Address.user_id ==1).count()

distinct:session.query(Address).disdinct().count()

删除数据

方法一：使用session执行sql语句方式

方法二：使用映射类成员变量的数据

session.query(Address).filter(Address.id ==4).delete()

session.commit()

级联删除：在relationship关联时要加上passive_deletes=True外键要加上ondelete='CASCADE',否则sqlalchemy不能级联删除。例如：

class MyClass(Base):

__tablename__ = 'mytable'

id = Column(Integer, primary_key=True)

children = relationship("MyOtherClass",

cascade="all, delete-orphan",

passive_deletes=True)

class MyOtherClass(Base):

__tablename__ = 'myothertable'

id = Column(Integer, primary_key=True)

parent_id = Column(Integer,

ForeignKey('mytable.id', ondelete='CASCADE')

)

修改数据

方法一：使用session执行sql语句方式

session.execute('update addresses set user_id = 1 where id = 2')

session.commit()

方法二：使用映射类成员变量的数据

session.query(Address).filter(Address.id == 2).update({"user_id": 1})

==注意==:update函数中的参数必须是字典类型

删除表

t1.drop() #t1是sqlalchemy.Table 对象,为users表的定义

---

**{贺广福}(heguangfu)**(tm) @2015-9-18