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

需积分: 10 198 浏览量更新于2024-07-22 收藏 1.76MB PDF 举报

《Essential SQLAlchemy》是一本由Rick Copeland编著的Python数据库接口指南，它旨在提供一个全面而实用的SQLAlchemy教程。作为一款通用的数据库连接工具，本书涵盖了广泛的应用场景，适合那些希望在Python环境中操作关系型数据库（如MySQL、PostgreSQL、Oracle等）的开发者，但值得注意的是，书中并未涉及NoSQL数据库如MongoDB。SQLAlchemy是一款强大的ORM（Object-Relational Mapping）框架，它允许开发者通过面向对象的方式来操作数据库，从而简化了复杂的数据交互和管理。该书在2008年首次出版，是作为Nutshell Handbook系列的一部分，强调了其简洁明快的内容和实用性。版权归属于Richard D. Copeland Jr.，所有权利受到保护。O'Reilly Media Inc.是该书籍的出版商，位于美国加利福尼亚州塞巴斯托波尔。对于教育、商业或销售推广目的，读者可以在线获取电子版，详情可通过O'Reilly的网站查询或联系公司销售部门。作者Mary E. Treseler担任编辑，确保了内容的专业性和连贯性；Genevieve'd'Entremont负责校对，确保文字准确无误；Sumita Mukherji担任生产编辑和校对员，保证了整体品质；Joe Wizda负责目录编制，使读者能够快速定位所需信息；而Karen Montgomery和David Futato分别负责封面设计和内部设计，提升了书籍的视觉效果；Jessamyn Read则负责插图，增强阅读体验。《Essential SQLAlchemy》不仅包含了基础概念的讲解，还深入探讨了高级主题，如模型设计、事务处理、连接池管理以及与数据库的集成等。此外，书中可能会涉及如何使用SQLAlchemy的查询构建器（QueryBuilder）、映射对象到数据库表（Mapping Objects to Tables）、以及处理多对多（Many-to-Many）和一对一（One-to-One）关系等。对于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页未读，继续阅读

wangmarkqi

粉丝: 24

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

SQLAlchemy教程与指南压缩包文档

深入浅出SQLAlchemy教程与代码解析

SQLAlchemy ORM教程：从入门到高级查询

sqlalchemy documentation sqlalchemy 文档

sqlalchemy文档资料翻译

sqlalchemy官方文档

SQLAlchemy 学习文档

sqlalchemy 英文文档

SQLAlchemy帮助文档

sqlalchemy 中文文档 1.1

最新资源