03.连接到数据库

作者: gthank | 来源:发表于2020-05-09 14:17 被阅读0次

03.连接到数据库
解决通过端口1433连接到主机TCP/IP连接失败
Redis 2018-07-28
MySQL -- 短连接
PyPyODBC 使用
信（2）
我一连接到…就很兴奋
连接到Oracle数据库
Mysql 命令 ( 基础 )
记一次wxss公开课的笔记

03.连接到数据库

在使用实体开始工作之前，你必须先创建数据库对象。你在 Python 代码中声明的实体将通过这个对象映射到数据库。

将实体映射到数据库可以分为四个步骤：

创建数据库对象
定义与此数据库对象相关的实体
将数据库对象绑定到一个特定的数据库
将实体映射到数据表

现在我们将描述用数据库对象及其方法的主要工作流程。当你需要更多的细节时，你可以在API参考找到它们。

创建数据库对象

在这一步，我们简单创建了一个数据库的实例：

db = Database()

数据库类实例有一个属性Entity，它代表一个基类，用于实体声明。

定义与数据库对象相关的实体

Entity应该继承自数据库对象的基类:

class MyEntity(db.Entity):
    attr1 = Required(str)

我们将在下一章中详细讨论实体的定义，现在，让我们来看看将实体映射到数据库的步骤。

将数据库对象绑定到特定的数据库

在我们将实体映射到数据库之前，我们需要先建立连接，这可以通过bind()方法来完成。

db.bind(provider='postgres', user=''', password=''', host='''', database=''')

这个方法的第一个参数是数据库提供者的名称。数据库提供者是一个模块，它位于pony.orn.dbproviders包中，并且知道如何与特定的数据库一起工作。

在数据库提供者名称之后，你应该指定参数，这些参数将被传递给相应的DBAPI驱动的connect()方法。

目前Pony可以和以下数据库系统一起工作：SQLite、PostgreSQL、MySQL、Oracle、CockroachDB，对应的Pony提供程序名为：'sqlite'、'postgres'、'mysql'、'oracle'和'cockroach'。

Pony可以很容易地扩展到包含其他数据库提供者。

当你刚刚开始使用Pony时，你可以使用SQLite数据库，这个数据库已经包含在Python发行版中，你不需要单独安装任何东西。

使用SQLite，你可以在文件中或内存中创建数据库，为了创建文件数据库，可以使用如下命令：

db.bind(provider='sqlite', filename='database.sqlite', create_db=True)

当create_db=True时，如果数据库文件不存在，Pony将创建数据库文件，如果数据库文件已经存在，Pony将会使用它。

对于内存中的数据库，使用这个方法：

db.bind(provider='sqlite', filename=':memory:')

在创建内存内数据库时，不需要参数 create_db。
这样在交互式shell中测试Pony时，可以方便地创建一个SQLite数据库，但你要记住，在程序退出时，整个内存中的数据库会丢失。

下面是与其他数据库绑定的例子：

db.bind(provider='sqlite', filename=':memory:')
db.bind(provider='sqlite', filename='filename', create_db=True)
db.bind(provider='mysql', host='', user='', passwd='', db='')
db.bind(provider='oracle', user='', password='', dsn='')
db.bind(provider='cockroach', user='', password='', host='', database='', sslmode='disable')

您可以在API参考资料中找到更多关于每个数据库的工作细节。

将实体映射到数据表

当数据库对象被创建、实体被定义、并且数据库被绑定之后，下一步就是使用generate_mapping()方法将实体映射到数据表。

db.generate_mapping(create_tables=True)

这个方法可以创建表、外键引用和索引（如果它们不存在的话）。

实体被映射后，你可以在你的Python代码中开始使用它们--选择(select)、创建(create)、修改(update)对象并保存到数据库中。

数据库对象的方法和属性

数据库对象有一组方法，你可以在API参考中查看。

使用数据库对象进行原始SQL查询

通常情况下，你将使用实体工作，并让Pony与数据库进行交互，但Pony也允许你使用SQL语句与数据库进行交互，甚至可以将两种方式结合起来。

当然，你可以直接使用DBAPI接口与数据库进行工作，但使用数据库对象给你带来以下优点：

使用db_session()装饰器或上下文管理器自动进行事务管理。事务完成后，所有的数据都会存储到数据库中，如果发生异常，则回滚到数据库中。
连接池。不需要跟踪数据库的连接，当你需要连接的时候，你就会拥有这个连接，当你完成事务后，连接会返回到池中。
统一的数据库异常。每个DBAPI模块都定义了自己的异常，Pony允许你在处理任何数据库时，都可以使用同一组异常。这有助于你创建可以从一个数据库移植到另一个数据库的应用程序。
统一传递参数到SQL查询的方式，防止注入攻击。不同的数据库驱动使用不同的参数方式--DBAPI规范提供了5种不同的参数传递给SQL查询的方式。使用数据库对象，你可以使用一种方式为所有数据库传递参数，消除了SQL注入的风险。
当使用Database对象的get()或select()方法时，可以自动解压单列结果。如果select()方法只返回一个列，Pony会返回一个值的列表，而不是像DBAPI那样，返回一个tuple的列表，每个tuple只有一个项。如果get()方法只返回一个列，它只返回一个值，而不是一个由一个项组成的tuple。这只是为了方便。
当select()或get()方法返回一个以上的列时，Pony使用智能tuples，它允许使用列名作为tuple属性访问项，而不仅仅是tuple索引。

换句话说，数据库对象可以帮助您节省完成常规任务的时间，并提供了方便和统一性。

在原生SQL查询中使用参数

使用Pony，你可以轻松地将参数传递到SQL查询中。为了指定一个参数，你需要在变量名称前加上$符号。

x = "John"
data = db.select("select * from Person where name = $x")

当Pony在SQL查询中遇到这样的参数时，它将从当前帧（来自globals和locals）或从作为第二个参数传递的字典中获取变量值。

在上面的例子中，Pony会尝试从变量x中获取$x的值，并将这个值作为参数传递给SQL查询，这样就消除了SQL注入的风险。

下面你可以看到如何传递一个带参数的字典：

data = db.select("select * from Person where name = $x", {"x" : "Susan"})

这种向SQL查询传递参数的方法非常灵活，不仅可以使用单个变量，还可以使用任何Python表达式。

为了指定一个表达式，你需要把它放在$符号后面的括号里：

data = db.select("select * from Person where name = $(x.lower()) and age > $(y + 2)")

使用$符号，所有的参数都可以用Pony统一的方式传递到查询中，独立于DBAPI提供者。

在上面的例子中，我们将姓名和年龄参数传入查询中。

也可以在查询文本中加入一个Python表达式，例如

x = 10
a = 20
b = 30
db.execute("SELECT * FROM Table1 WHERE column1 = $x and column2 = $(a + b)")

如果你需要在查询中使用$符号作为字符串的字段，你需要用另一个 $来转义（连续放两个$ 符号：$$）。

定义连接行为

你可以使用db.on_connect()装饰符执行一些查询到你指定的连接。

db = Database()

# 实体声明
@db.on_connect(provider='sqlite')
def sqlite_case_sensitivity(db, connection):
    cursor = connection.cursor()
    cursor.execute('PRAGMA case_sensitive_like = OFF')

db.bind(**options)
db.generate_mapping(create_tables=True)

通过下面的代码，每个新的sqlite连接都会调用这个函数，这个例子显示了如何恢复旧的大小写不敏感，就像sqlite一样。