SQLAlchemy基础教程

5星 · 超过95%的资源需积分: 10 189 浏览量更新于2024-07-29 收藏 1.67MB PDF 举报

"essential_sqlalchemy" 《Essential SQLAlchemy》是一本由Rick Copeland编著的关于SQLAlchemy的书籍，主要探讨了Python编程语言中的SQLAlchemy库的使用和实践。SQLAlchemy是Python中最流行的ORM（对象关系映射）工具之一，它允许开发者用Python对象的方式操作数据库，从而减少了对SQL语句的直接依赖。 SQLAlchemy的核心概念包括： 1. **对象关系映射（ORM）**：ORM是将数据库关系模型转换为面向对象编程模型的一种技术。在SQLAlchemy中，ORM提供了一种方式，使得开发者可以使用Python类和对象来操作数据库表，而无需编写大量的SQL语句。 2. **Session**：Session是SQLAlchemy中的一个关键概念，它是数据库操作的会话层，负责管理对象的状态和事务。通过Session，开发者可以方便地进行对象的查询、添加、删除和更新等操作。 3. **Table和Column**：在SQLAlchemy中，可以使用Table和Column类来定义数据库表的结构。Table用于表示数据库中的表，Column则表示表中的列及其数据类型。 4. **Model和映射**：开发者定义的Python类被称为Model，它们与数据库表进行映射。通过`__table__`属性或`metadata`对象，可以将这些类映射到实际的数据库表上。 5. **查询API**：SQLAlchemy提供了强大的查询API，允许开发者以链式方式构造复杂的查询，同时支持SQL表达式的动态构建。 6. **关系和关联**：在ORM中，表之间的关系（如一对一、一对多、多对多）可以通过association_proxy、backref和relationship等特性来实现。 7. **事务处理**：SQLAlchemy支持事务管理，开发者可以通过Session对象进行显式的开始、提交、回滚事务，确保数据的一致性。 8. **引擎和连接池**：SQLAlchemy的Engine对象负责与数据库的连接，而连接池则管理数据库连接的创建和回收，提高性能并减少资源消耗。 9. **事件监听**：SQLAlchemy提供了事件系统，允许开发者在特定操作（如插入、更新、删除）前后注册回调函数，实现自定义的行为。 10. **SQL表达式语言**：除了ORM之外，SQLAlchemy还提供了SQL Expression Language，这是一套构建和执行SQL语句的API，适合那些需要直接操作SQL的场合。《Essential SQLAlchemy》这本书旨在帮助Python开发者深入理解并熟练使用SQLAlchemy库，提升数据库操作的效率和灵活性。无论你是初学者还是有经验的开发者，都能从书中找到对应的知识点和实用技巧。

SQL Injection Attacks

SQL injection is a type of programming error where carefully crafted user input can

cause your application to execute arbitrary SQL code. For instance, suppose that the

DB-API code in the earlier listing had been written as follows:

sql="INSERT INTO user(user_name, password) VALUES ('%s', '%s')"

cursor = conn.cursor()

cursor.execute(sql % (user_name, password))

In most cases, this code will work. For instance, with the user_name and password var-

iables just shown, the SQL that would be executed is INSERT INTO user(user_name,

password) VALUES ('rick', 'parrot'). A user could, however, supply a maliciously

crafted password: parrot'); DELETE FROM user; --. In this case, the SQL executed is

INSERT INTO user(user_name, password) VALUES ('rick', 'parrot'); DELETE FROM

user; --', which would probably delete all users from your database. The use of bind

parameters (as in the first example in the text) is an effective defense against SQL in-

jection, but as long as you are manipulating strings directly, there is always the

possibility of introducting a SQL injection vulnerability into your code.

In the SQLAlchemy SQL expression language, you could write the following instead:

statement = user_table.insert(user_name='rick', password='parrot')

statement.execute()

To migrate this code to Oracle, you would write, well, exactly the same thing.

SQLAlchemy also allows you to write SQL queries using a Pythonic expression-builder.

For instance, to retrieve all the users created in 2007, you would write:

statement = user_table.select(and_(

user_table.c.created >= date(2007,1,1),

user_table.c.created < date(2008,1,1))

result = statement.execute()

In order to use the SQL expression language, you need to provide SQLAlchemy with

information about your database schema. For instance, if you are using the user table

mentioned previously, your schema definition might be the following:

metadata=MetaData('sqlite://') # use an in-memory SQLite database

user_table = Table(

'tf_user', metadata,

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

Column('user_name', Unicode(16), unique=True, nullable=False),

Column('email_address', Unicode(255), unique=True, nullable=False),

Column('password', Unicode(40), nullable=False),

Column('first_name', Unicode(255), default=''),

Column('last_name', Unicode(255), default=''),

Column('created', DateTime, default=datetime.now))

If you would rather use an existing database schema definition, you still need to tell

SQLAlchemy which tables you have, but SQLAlchemy can reflect the tables using the

2 | Chapter 1: Introduction to SQLAlchemy

database server’s introspection capabilities. In this case, the schema definition reduces

to the following:

users_table = Table('users', metadata, autoload=True)

Although the SQLAlchemy SQL expression language is quite powerful, it can still be

tedious to manually specify the queries and updates necessary to work with your tables.

To help with this problem, SQLAlchemy provides an ORM to automatically populate

your Python objects from the database and to update the database based on changes

to your Python objects. Using the ORM is as simple as writing your classes, defining

your tables, and mapping your tables to your classes. In the case of the user table, you

could perform a simple mapping via the following code:

class User(object): pass

mapper(User, user_table)

Notice that there is nothing particularly special about the User class defined here. It is

used to create “plain old Python objects,” or POPOs. All the magic of SQLAlchemy is

performed by the mapper. Although the class definition just shown is empty, you may

define your own methods and attributes on a mapped class. The mapper will create

attributes corresponding to the column names in the mapped table as well as some

private attributes used by SQLAlchemy internally. Once your table is mapped, you can

use a Session object to populate your objects based on data in the user table and flush

any changes you make to mapped objects to the database:

>>> Session = sessionmaker()

>>> session = Session()

>>>

>>> # Insert a user into the database

... u = User()

>>> u.user_name='rick'

>>> u.email_address='rick@foo.com'

>>> u.password='parrot'

>>> session.save(u)

>>>

>>> # Flush all changes to the session out to the database

... session.flush()

>>>

>>> query = session.query(User)

>>> # List all users

... list(query)

[<__main__.User object at 0x2abb96dae3d0>]

>>>

>>> # Get a particular user by primary key

... query.get(1)

<__main__.User object at 0x2abb96dae3d0>

>>>

>>> # Get a particular user by some other column

... query.get_by(user_name='rick')

<__main__.User object at 0x2abb96dae3d0>

>>>

>>> u = query.get_by(user_name='rick')

>>> u.password = 'foo'

What Is SQLAlch | 3

>>> session.flush()

>>> query.get(1).password

'foo'

As you can see, SQLAlchemy makes persisting your objects simple and concise. You

can also customize and extend the set of properties created by SQLAlchemy, allowing

your objects to model, for instance, a many-to-many relationship with simple Python

lists.

The Object/Relational “Impedance Mismatch”

Although a SQL database is a powerful and flexible modeling tool, it is not always a

good match for the object-oriented programming style. SQL is good for some things,

and object-oriented programming is good for others. This is sometimes referred to as

the object/relational “impedance mismatch,” and it is a problem that SQLAlchemy

tries to address in the ORM. To illustrate the object/relational impedance mismatch,

let’s first look at how we might model a system in SQL, and then how we might model

it in and object-oriented way.

SQL databases provide a powerful means for modeling data and allowing for arbitrary

queries of that data. The model underlying SQL is the relational model. In the relational

model, modeled items (entities) can have various attributes, and are related to other

entities via relationships. These relationships can be one-to-one, one-to-many, many-

to-many, or complex, multientity relationships. The SQL expression of the entity is the

table, and relationships are expressed as foreign key constraints, possibly with the use

of an auxiliary “join” table. For example, suppose we have a user permission system

that has users who may belong to one or more groups. Groups may have one or more

permissions. Our SQL to model such a system might be something like the following:

CREATE TABLE tf_user (

id INTEGER NOT NULL,

user_name VARCHAR(16) NOT NULL,

email_address VARCHAR(255) NOT NULL,

password VARCHAR(40) NOT NULL,

first_name VARCHAR(255),

last_name VARCHAR(255),

created TIMESTAMP,

PRIMARY KEY (id),

UNIQUE (user_name),

UNIQUE (email_address));

CREATE TABLE tf_group (

id INTEGER NOT NULL,

group_name VARCHAR(16) NOT NULL,

PRIMARY KEY (id),

UNIQUE (group_name));

CREATE TABLE tf_permission (

id INTEGER NOT NULL,

permission_name VARCHAR(16) NOT NULL,

PRIMARY KEY (id),

UNIQUE (permission_name));

4 | Chapter 1: Introduction to SQLAlchemy

-- Relate the user and group tables

CREATE TABLE user_group (

user_id INTEGER,

group_id INTEGER,

PRIMARY KEY(user_id, group_id),

FOREIGN KEY(user_id) REFERENCES tf_user (id),

FOREIGN KEY(group_id) REFERENCES tf_group (id));

-- Relate the group and permission tables

CREATE TABLE group_permission (

group_id INTEGER,

permission_id INTEGER,

PRIMARY KEY(group_id, permission_id),

FOREIGN KEY(group_id) REFERENCES tf_group (id),

FOREIGN KEY(permission_id) REFERENCES tf_permission (id));

Notice the two auxilliary tables used to provide many-to-many joins between users and

groups, and between groups and users. Once we have this schema in place, a common

scenario is to check whether a particular user has a particular permission. In SQL, we

might write:

SELECT COUNT(*) FROM tf_user, tf_group, tf_permission WHERE

tf_user.user_name='rick' AND tf_user.id=user_group.user_id

AND user_group.group_id = group_permission.group_id

AND group_permission.permission_id = tf_permission.id

AND permission_name='admin';

In a single statement, we join the three entities—user, group, and permission—together

to determine whether the user “rick” has the “admin” permission.

In the object-oriented world, we would probably model the system quite differently.

We would still have users, groups, and permissions, but they would probably have an

ownership relationship between them:

class User(object):

groups=[]

class Group(object):

users=[]

permissions=[]

class Permission(object):

groups=[]

Suppose we wanted to print out a summary of all of a given user’s groups and permis-

sions, something an object-oriented style would do quite well. We might write some-

thing like the following:

print 'Summary for %s' % user.user_name

for g in user.groups:

print ' Member of group %s' % g.group_name

for p in g.permissions:

print ' ... which has permission %s' % p.permission_name

On the other hand, if we wanted to determine whether a user has a particular permis-

sion, we would need to do something like the following:

The Object/Relational “Impedance Mismatch” | 5

def user_has_permission(user, permission_name):

for g in user.groups:

for p in g.permissions:

if p.permission_name == 'admin':

return True

return False

In this case, we needed to write a nested loop, examining every group the user is a

member of to see if that group had a particular permission. SQLAlchemy lets you use

object-oriented programming where appropriate (such as checking for a user’s per-

mission to do something) and relational programming where appropriate (such as

printing a summary of groups and permissions). In SQLAlchemy, we could print the

summary information exactly as shown, and we could detect membership in a group

with a much simpler query. First, we need to create mappings between our tables and

our objects, telling SQLAlchemy a little bit about the many-to-many joins:

mapper(User, user_table, properties=dict(

groups=relation(Group, secondary=user_group, backref='users')))

mapper(Group, group_table, properties=dict(

permissions=relation(Permission, secondary=group_permission,

backref='groups')))

mapper(Permission, permission_table)

Now, our model plus the magic of the SQLAlchemy ORM allows us to detect whether

the given user is an administrator:

q = session.query(Permission)

rick_is_admin = q.count_by(permission_name='admin',

... user_name='rick')

SQLAlchemy was able to look at our mappers, determine how to join the tables, and

use the relational model to generate a single call to the database. The SQL generated

by SQLAlchemy is actually quite similar to what we would have written ourselves:

SELECT count(tf_permission.id)

FROM tf_permission, tf_user, group_permission, tf_group, user_group

WHERE (tf_user.user_name = ?

AND ((tf_permission.id = group_permission.permission_id

AND tf_group.id = group_permission.group_id)

AND (tf_group.id = user_group.group_id

AND tf_user.id = user_group.user_id)))

AND (tf_permission.permission_name = ?)

SQLAlchemy’s real power comes from its ability to bridge the object/relational divide

and allow you to use whichever model is appropriate to your task at hand. Aggregation

is another example of using SQLAlchemy’s relational model rather than the object-

oriented model. Suppose we wanted a count of how many users had each permission

type. In the traditional object-oriented world, we would probably loop over each per-

mission, then over each group, and finally count the users in the group (without

forgetting to remove duplicates!). This leads to something like this:

for p in permissions:

users = set()

6 | Chapter 1: Introduction to SQLAlchemy

剩余229页未读，继续阅读

alang85

粉丝: 2
资源: 17

SQLAlchemy基础教程

《Essential SQLAlchemy》：通用数据库接口指南

Python Web开发基石：SQLAlchemy ORM实战指南

Python ORM框架SQLAlchemy深度解析

Essential SQLAlchemy

Essential SQLAlchemy 2nd Edition

sqlalchemy 英文解读

Python-awesomesqlalchemy系列SQLAlchemy相关工具集合

Essential ORM Modeler:以平台无关的方式设计您的ORM应用程序。-开源

Python ORM利器：SQLAlchemy深度解析

Python数据库编程：SQLAlchemy实战与进阶

最新资源