SQLAlchemy入门指南（第2版）：映射Python到数据库

5星 · 超过95%的资源需积分: 17 157 浏览量更新于2024-07-21 4 收藏 4.16MB PDF 举报

"《Essential SQLAlchemy 2nd Edition》(2015年11月)是一本由Jason Myers和Rick Copeland合著的专业书籍，主要聚焦于将Python编程语言与数据库进行高效而深入的集成。这本书是SQLAlchemy库的实用指南，第2版更新了针对2015年的最新内容，旨在帮助读者理解和掌握如何利用 SQLAlchemy 这一强大的ORM（对象关系映射）工具，实现数据库操作的简化和代码组织的优化。在本书中，作者详细介绍了如何通过 SQLAlchemy 将 Python 对象映射到数据库表，以及如何执行复杂的查询、事务处理、数据持久化和高级特性如连接池和多态。读者可以学习到如何优雅地处理数据模型设计，包括元类和继承，以及如何在大型项目中管理和维护数据库操作。本书适合那些已经熟悉Python编程基础，想要进一步提升对数据库操作效率和代码可维护性的开发者阅读，无论是初学者还是有经验的开发人员都能从中获益匪浅。此外，书中还包含了示例代码和实战案例，便于读者通过实践来巩固理论知识。第2版的修订历史表明，该书在2015年11月进行了首次发布，这意味着它可能包含了一些当时SQLAlchemy库的新特性和最佳实践。对于那些需要跟上SQLAlchemy最新进展的读者，这本书是一个不可或缺的参考资料。作为一本技术性很强的图书，它不仅涵盖了基础概念，还包括了如何解决实际问题，因此，在学习过程中，读者可能会遇到关于Python语法、数据库理论和ORM设计的具体问题。然而，有了这本书的指导，读者能够建立起坚实的基础，为在实际项目中高效地运用SQLAlchemy打下坚实的基础。"

SQLAlchemy Core and the SQL Expression Language

The SQL Expression Language is a Pythonic way of representing common SQL state‐

ments and expressions, and is only a mild abstraction from the typical SQL language.

It is focused on the actual database schema; however, it is standardized in such a way

that it provides a consistent language across a large number of backend databases.

The SQL Expression Language also acts as the foundation for the SQLAlchemy ORM.

ORM

The SQLAlchemy ORM is similar to many other object relational mappers (ORMs)

you may have encountered in other languages. It is focused around the domain

model of the application and leverages the Unit of Work pattern to maintain object

state. It also provides a high-level abstraction on top of the SQL Expression Language

that enables the user to work in a more idiomatic way. You can mix and match use of

the ORM with the SQL Expression Language to create very powerful applications.

The ORM leverages a declarative system that is similar to the active-record systems

used by many other ORMs such as the one found in Ruby on Rails.

While the ORM is extremely useful, you must keep in mind that there is a difference

between the way classes can be related, and how the underlying database relation‐

ships work. We’ll more fully explore the ways in which this can affect your implemen‐

tation in Chapter 6.

Choosing Between SQLAlchemy Core and ORM

Before you begin building applications with SQLAlchemy, you will need to decide if

you are going to primarly use the ORM or Core. The choice of using SQLAlchemy

Core or ORM as the dominant data access layer for an application often comes down

to a few factors and personal preference.

The two modes use slightly different syntax, but the biggest difference between Core

and ORM is the view of data as schema or business objects. SQLAlchemy Core has a

schema-centric view, which like traditional SQL is focused around tables, keys, and

index structures. SQLAlchemy Core really shines in data warehouse, reporting, analy‐

sis, and other scenarios where being able to tightly control the query or operating on

unmodeled data is useful. The strong database connection pool and result-set opti‐

mizations are perfectly suited to dealing with large amounts of data, even in multiple

databases.

However, if you intend to focus more on a domain-driven design, the ORM will

encapsulate much of the underlying schema and structure in metadata and business

objects. This encapsulation can make it easy to make database interactions feel more

like normal Python code. Most common applications lend themselves to being mod‐

eled in this way. It can also be a highly effective way to inject domain-driven design

xiv | Introduction to SQLAlchemy

into a legacy application or one with raw SQL statements sprinkled throughout.

Microservices also benefit from the abstraction of the underlying database, allowing

the developer to focus on just the process being implemented.

However, because the ORM is built on top of SQLAlchemy Core, you can use its abil‐

ity to work with services like Oracle Data Warehousing and Amazon Redshift in the

same manner that it interoperates with MySQL. This makes it a wonderful compli‐

ment to the ORM when you need to combine business objects and warehoused data.

Here’s a quick checklist to help you decide which option is best for you:

•

If you are working with a framework that already has an ORM built in, but want

to add more powerful reporting, use Core.

• If you want to view your data in a more schema-centric view (as used in SQL),

use Core.

• If you have data for which business objects are not needed, use Core.

• If you view your data as business objects, use ORM.

•

If you are building a quick prototype, use ORM.

• If you have a combination of needs that really could leverage both business

objects and other data unrelated to the problem domain, use both!

Now that you know how SQLAlchemy is structured and the difference between Core

and ORM, we are ready to install and start using SQLAlchemy to connect to a data‐

base.

Installing SQLAlchemy and Connecting to a Database

SQLAlchemy can be used with Python 2.6, Python 3.3, and Pypy 2.1 or greater. I rec‐

ommend using pip to perform the install with the command pip install

sqlalchemy. It’s worth noting that it can also be installed with easy_install and distu‐

tils; however, pip is the more straightforward method. During the install, SQLAl‐

chemy will attempt to build some C extensions, which are leveraged to make working

with result sets fast and more memory efficient. If you need to disable these exten‐

sions due to the lack of a compiler on the system you are installing on, you can use

--global-option=--without-cextensions. Note that using SQLAlchemy without C

extensions will adversely affect performance, and you should test your code on a sys‐

tem with the C extensions prior to optimizing it.

Introduction to SQLAlchemy | xv

Installing Database Drivers

By default, SQLAlchemy will support SQLite3 with no additional drivers; however, an

additional database driver that uses the standard Python DBAPI (PEP-249) specifica‐

tion is needed to connect to other databases. These DBAPIs provide the basis for the

dialect each database server speaks, and often enable the unique features seen in dif‐

ferent database servers and versions. While there are multiple DBAPIs available for

many of the databases, the following instructions focus on the most common:

PostgreSQL

Psycopg2 provides wide support for PostgreSQL versions and features and can be

installed with pip install psycopg2.

MySQL

PyMySQL is my preferred Python library for connecting to a MySQL database

server. It can be installed with a pip install pymysql. MySQL support in

SQLAlchemy requires MySQL version 4.1 and higher due to the way passwords

worked prior to that version. Also, if a particular statement type is only available

in a certain version of MySQL, SQLAlchemy does not provide a method to use

those statements on versions of MySQL where the statement isn’t available. It’s

important to review the MySQL documentation if a particular component or

function in SQLAlchemy does not seem to work in your environment.

Others

SQLAlchemy can also be used in conjunction with Drizzle, Firebird, Oracle, Syb‐

ase, and Microsoft SQL Server. The community has also supplied external dia‐

lects for many other databases like IBM DB2, Informix, Amazon Redshift,

EXASolution, SAP SQL Anywhere, Monet, and many others. Creating an addi‐

tional dialect is well supported by SQLAlchemy, and Chapter 7 will examine the

process of doing just that.

Now that we have SQLAlchemy and a DBAPI installed, let’s actually build an engine

to connect to a database.

Connecting to a Database

To connect to a database, we need to create a SQLAlchemy engine. The SQLAlchemy

engine creates a common interface to the database to execute SQL statements. It does

this by wrapping a pool of database connections and a dialect in such a way that they

can work together to provide uniform access to the backend database. This enables

our Python code not to worry about the differences between databases or DBAPIs.

xvi | Introduction to SQLAlchemy

SQLAlchemy provides a function to create an engine for us given a connection string

and optionally some additional keyword arguments. A connection string is a specially

formatted string that provides:

•

Database type (Postgres, MySQL, etc.)

• Dialect unless the default for the database type (Psycopg2, PyMySQL, etc.)

•

Optional authentication details (username and password)

• Location of the database (file or hostname of the database server)

• Optional database server port

• Optional database name

SQLite database connections strings have us represent a specific file or a storage loca‐

tion. Example P-1 defines a SQLite database file named cookies.db stored in the cur‐

rent directory via a relative path in the second line, an in-memory database on the

third line, and a full path to the file on the fourth (Unix) and fifth (Windows) lines.

On Windows, the connection string would look like engine4; the \\ are required for

proper string escaping unless you use a raw string (r'').

Example P-1. Creating an engine for a SQLite database

from sqlalchemy import create_engine

engine = create_engine('sqlite:///cookies.db')

engine2 = create_engine('sqlite:///:memory:')

engine3 = create_engine('sqlite:////home/cookiemonster/cookies.db')

engine4 = create_engine('sqlite:///c:\\Users\\cookiemonster\\cookies.db')

The create_engine function returns an instance of an engine;

however, it does not actually open a connection until an action is

called that would require a connection, such as a query.

Let’s create an engine for a local PostgreSQL database named mydb. We’ll start by

importing the create_engine function from the base sqlalchemy package. Next,

we’ll use that function to construct an engine instance. In Example P-2, you’ll notice

that I use postgresql+psycopg2 as the engine and dialect components of the connec‐

tion string, even though using only postgres will work. This is because I prefer to be

explicit instead of implicit, as recommended in the Zen of Python.

Introduction to SQLAlchemy | xvii

剩余207页未读，继续阅读

xinconan2

粉丝: 269
资源: 399

SQLAlchemy入门指南（第2版）：映射Python到数据库

sqlalchemy官方文档

Python SQLAlchemy基本操作和常用技巧（包含大量实例,非常好）

Python SQLAlchemy入门教程(基本用法)

apscheduler.jobstores.sqlalchemy

stub file not found for \pgvector.sqlalchemy\"pylancereportmissingtypestubs"

{{ config.SQLALCHEMY_DATABASE_URI }}

sqlalchemy中文文档

sqlalchemy func.sum

oslo_db中engine.connect()中的engine类路径

func.coalesce sqlalchemy

最新资源