SQLAlchemy ORM教程：从安装到高级查询

需积分: 14 22 浏览量更新于2024-07-31 收藏 2.94MB PDF 举报

SQLAlchemy Documentation 是一个强大的Python ORM（对象关系映射）库，用于处理SQL数据库操作。本文档详细介绍了0.7.2版的使用指南，包括安装、配置和核心功能。以下是部分内容概要： 1. **概述** (Section 1.1): 提供了SQLAlchemy的总体介绍，它旨在帮助开发者在Python应用中无缝集成关系型数据库。 2. **文档概述** (Section 1.2): 本部分简述了文档结构，包括章节划分和主要主题，如对象关系教程、安装指南等。 3. **代码示例** (Section 1.3): 提供了大量的代码示例，让读者通过实践学习如何使用SQLAlchemy进行数据库操作和对象映射。 4. **安装指南** (Section 1.4): - **支持平台**: 详述了SQLAlchemy兼容的操作系统。 - **安装方法**: 包括easy_install或pip安装、使用setup.py安装，以及针对Python 3的安装说明。 - **C扩展安装**: 如何安装和配置可能包含的C语言扩展。 - **数据库API**: 指出如何安装必要的数据库驱动程序。 5. **版本迁移** (Section 1.5): 对于从0.6到0.7版本的迁移提供了指导，确保用户能顺利过渡。 6. **SQLAlchemy ORM教程** (Section 2.1): 阐述了ORM的核心概念，包括： - **入门**: 教导如何连接数据库、声明映射和创建会话。 - **实例操作**: 如创建对象实例、添加新记录、事务处理和查询操作。 - **高级特性**: 如过滤器操作、返回列表和单值、使用SQL子句、计数、关系建立与查询、懒加载和多对多关系的配置等。 7. **关系映射配置** (Section 2.2): 包括定制列属性、延迟加载、表达式作为属性、更改行为、复合列类型和多表或多选择查询的映射。 8. **关系配置** (Section 2.3): 介绍了不同类型的关联，如一对一、一对多、多对一和多对多关系，以及自引用查询和回显链接等高级关系设置。 9. **集合配置** (Section 2.4): 探讨了如何管理和操作数据库集合，包括自环关系和更新级联等问题。通过这个文档，用户可以深入了解如何在Python项目中利用SQLAlchemy高效地管理数据库，实现数据持久化和对象关系映射操作。

SQLAlchemy Documentation, Release 0.7.2

1.2 Documentation Overview

The documentation is separated into three sections: SQLAlchemy ORM, SQLAlchemy Core, and Dialects.

In SQLAlchemy ORM, the Object Relational Mapper is introduced and fully described. New users should begin with

the Object Relational Tutorial. If you want to work with higher-level SQL which is constructed automatically for you,

as well as management of Python objects, proceed to this tutorial.

In SQLAlchemy Core, the breadth of SQLAlchemy’s SQL and database integration and description services are doc-

umented, the core of which is the SQL Expression language. The SQL Expression Language is a toolkit all its

own, independent of the ORM package, which can be used to construct manipulable SQL expressions which can be

programmatically constructed, modiﬁed, and executed, returning cursor-like result sets. In contrast to the ORM’s

domain-centric mode of usage, the expression language provides a schema-centric usage paradigm. New users should

begin here with SQL Expression Language Tutorial. SQLAlchemy engine, connection, and pooling services are also

described in SQLAlchemy Core.

In Dialects, reference documentation for all provided database and DBAPI backends is provided.

1.3 Code Examples

Working code examples, mostly regarding the ORM, are included in the SQLAlchemy distribution. A description of

all the included example applications is at Examples.

There is also a wide variety of examples involving both core SQLAlchemy constructs as well as the ORM on the wiki.

See Theatrum Chemicum.

1.4 Installation Guide

1.4.1 Supported Platforms

SQLAlchemy has been tested against the following platforms:

• cPython since version 2.4, through the 2.xx series

• cPython version 3, throughout all 3.xx series

• Jython 2.5 or greater

• Pypy 1.5 or greater

1.4.2 Supported Installation Methods

SQLAlchemy supports installation using standard Python “distutils” or “setuptools” methodologies. An overview of

potential setups is as follows:

• Plain Python Distutils - SQLAlchemy can be installed with a clean Python install using the services provided

via Python Distutils, using the setup.py script. The C extensions as well as Python 3 builds are supported.

• Standard Setuptools - When using setuptools, SQLAlchemy can be installed via setup.py or

easy_install, and the C extensions are supported. setuptools is not supported on Python 3 at the time

of of this writing.

• Distribute - With distribute, SQLAlchemy can be installed via setup.py or easy_install, and the C

extensions as well as Python 3 builds are supported.

4 Chapter 1. Overview

SQLAlchemy Documentation, Release 0.7.2

• pip - pip is an installer that rides on top of setuptools or distribute, replacing the usage of

easy_install. It is often preferred for its simpler mode of usage.

1.4.3 Install via easy_install or pip

When easy_install or pip is available, the distribution can be downloaded from Pypi and installed in one step:

easy_install SQLAlchemy

Or with pip:

pip install SQLAlchemy

This command will download the latest version of SQLAlchemy from the Python Cheese Shop and install it to your

system.

1.4.4 Installing using setup.py

Otherwise, you can install from the distribution using the setup.py script:

python setup.py install

1.4.5 Installing the C Extensions

SQLAlchemy includes C extensions which provide an extra speed boost for dealing with result sets. Currently, the

extensions are only supported on the 2.xx series of cPython, not Python 3 or Pypy.

setup.py will automatically build the extensions if an appropriate platform is detected. If the build of the C extensions

fails, due to missing compiler or other issue, the setup process will output a warning message, and re-run the build

without the C extensions, upon completion reporting ﬁnal status.

To run the build/install without even attempting to compile the C extensions, pass the ﬂag

--without-cextensions to the setup.py script:

python setup.py --without-cextensions install

1.4.6 Installing on Python 3

SQLAlchemy ships as Python 2 code. For Python 3 usage, the setup.py script will invoke the Python 2to3 tool on

the build, plugging in an extra “preprocessor” as well. The 2to3 step works with Python distutils (part of the standard

Python install) and Distribute - it will not work with a non-Distribute setuptools installation.

1.4.7 Installing a Database API

SQLAlchemy is designed to operate with a DB-API implementation built for a particular database, and includes

support for the most popular databases. The current list is at Supported Databases.

1.4.8 Checking the Installed SQLAlchemy Version

This documentation covers SQLAlchemy version 0.7. If you’re working on a system that already has SQLAlchemy

installed, check the version from your Python prompt like this:

1.4. Installation Guide 5

CHAPTER

TWO

SQLALCHEMY ORM

2.1 Object Relational Tutorial

2.1.1 Introduction

The SQLAlchemy Object Relational Mapper presents a method of associating user-deﬁned Python classes with

database tables, and instances of those classes (objects) with rows in their corresponding tables. It includes a sys-

tem that transparently synchronizes all changes in state between objects and their related rows, called a unit of work,

as well as a system for expressing database queries in terms of the user deﬁned classes and their deﬁned relationships

between each other.

The ORM is in contrast to the SQLAlchemy Expression Language, upon which the ORM is constructed. Whereas the

SQL Expression Language, introduced in SQL Expression Language Tutorial, presents a system of representing the

primitive constructs of the relational database directly without opinion, the ORM presents a high level and abstracted

pattern of usage, which itself is an example of applied usage of the Expression Language.

While there is overlap among the usage patterns of the ORM and the Expression Language, the similarities are more

superﬁcial than they may at ﬁrst appear. One approaches the structure and content of data from the perspective of

a user-deﬁned domain model which is transparently persisted and refreshed from its underlying storage model. The

other approaches it from the perspective of literal schema and SQL expression representations which are explicitly

composed into messages consumed individually by the database.

A successful application may be constructed using the Object Relational Mapper exclusively. In advanced situations,

an application constructed with the ORM may make occasional usage of the Expression Language directly in certain

areas where speciﬁc database interactions are required.

The following tutorial is in doctest format, meaning each >>> line represents something you can type at a Python

command prompt, and the following text represents the expected return value.

2.1.2 Version Check

A quick check to verify that we are on at least version 0.7 of SQLAlchemy:

>>> import sqlalchemy

>>> sqlalchemy.__version__

0.7.0

2.1.3 Connecting

For this tutorial we will use an in-memory-only SQLite database. To connect we use create_engine():

SQLAlchemy Documentation, Release 0.7.2

>>> from sqlalchemy import create_engine

>>> engine = create_engine(’sqlite:///:memory:’, echo=True)

The echo ﬂag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python’s standard

logging module. With it enabled, we’ll see all the generated SQL produced. If you are working through this

tutorial and want less output generated, set it to False. This tutorial will format the SQL behind a popup window so

it doesn’t get in our way; just click the “SQL” links to see what’s being generated.

The return value of create_engine() is an instance of Engine, and it represents the core interface to the

database, adapted through a dialect that handles the details of the database and DBAPI in use. In this case the SQLite

dialect will interpret instructions to the Python built-in sqlite3 module.

The Engine has not actually tried to connect to the database yet; that happens only the ﬁrst time it is asked to perform

a task against the database. We can illustrate this by asking it to perform a simple SELECT statement:

>>> engine.execute("select 1").scalar()

select 1

()

As the Engine.execute() method is called, the Engine establishes a connection to the SQLite database, which

is then used to emit the SQL. The connection is then returned to an internal connection pool where it will be reused on

subsequent statement executions. While we illustrate direct usage of the Engine here, this isn’t typically necessary

when using the ORM, where the Engine, once created, is used behind the scenes by the ORM as we’ll see shortly.

2.1.4 Declare a Mapping

When using the ORM, the conﬁgurational process starts by describing the database tables we’ll be dealing with, and

then by deﬁning our own classes which will be mapped to those tables. In modern SQLAlchemy, these two tasks

are usually performed together, using a system known as Declarative, which allows us to create classes that include

directives to describe the actual database table they will be mapped to.

Classes mapped using the Declarative system are deﬁned in terms of a base class which maintains a catalog of classes

and tables relative to that base - this is known as the declarative base class. Our application will usually have just one

instance of this base in a commonly imported module. We create the base class using the declarative_base()

function, as follows:

>>> from sqlalchemy.ext.declarative import declarative_base

>>> Base = declarative_base()

Now that we have a “base”, we can deﬁne any number of mapped classes in terms of it. We will start with just a single

table called users, which will store records for the end-users using our application. A new class called User will

be the class to which we map this table. The imports we’ll need to accomplish this include objects that represent the

components of our table, including the Column class which represents a database column, as well as the Integer

and String classes that represent basic datatypes used in columns:

>>> from sqlalchemy import Column, Integer, String

>>> class User(Base):

... __tablename__ = ’users’

...

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

... name = Column(String)

... fullname = Column(String)

... password = Column(String)

...

... def __init__(self, name, fullname, password):

... self.name = name

8 Chapter 2. SQLAlchemy ORM

剩余515页未读，继续阅读

frank_nxz

粉丝: 0
资源: 2

SQLAlchemy ORM教程：从安装到高级查询

sqlalchemy documentation sqlalchemy 文档

SQLAlchemy技术文档(中文版)

AttributeError: module 'sqlalchemy.sql.schema' has no attribute '_schema_getter'

flask migrate

写一份python学生管理系统

中国石油大学(华东)在北京2021-2024各专业最低录取分数及位次表.pdf

【C#绘图进阶】实时曲线绘制+精准坐标轴，让你的数据可视化更加专业！

长安大学在广东2021-2024各专业最低录取分数及位次表.pdf

新疆政法学院在广东2021-2024各专业最低录取分数及位次表.pdf

最新资源