Python笔记12（访问数据库）

最新推荐文章于 2022-08-03 16:15:54 发布

程序员老五

最新推荐文章于 2022-08-03 16:15:54 发布

阅读量306

点赞数 3

分类专栏： Python笔记与练习题文章标签：数据库 python mysql orm SQLAlchemy

本文链接：https://blog.csdn.net/weixin_45579026/article/details/117653755

版权

Python笔记与练习题专栏收录该内容

21 篇文章 1 订阅

订阅专栏

18. 访问数据库

数据库(Database)：专门用于集中存储和查询的软件，能直接通过条件快读查询到指定的数据。

关系型数据库类别：

收费：Oracle、SQL Server、DB2、Sybase
免费：MySQL、PostgreSQL、sqlite

1. 使用SQLite

SQLite概念：

SQLite是一种嵌入式数据库，它的数据库就是一个文件。

SQLite的特点：

本身是C写的，体积很小。轻量级、可嵌入
但不能承受高并发访问，适合桌面和移动应用。

Python内置了SQLite3，可直接使用。

相关概念：

表是数据库中存放关系数据的集合
一个数据库里通常都包含多个表
表与表之间通过外键关联。

操作关系数据库的步骤：

连接到数据库，一个数据库连接称为Connection
打开游标Cursor，通过游标执行SQL语句，获得执行结果

在Python交互命令行实践一下：

# 导入SQLite驱动
>>> import sqlite3
# 连接到SQLite数据库，数据库文件是test.db，如果文件不存在会自动在当前目录创建
>>> conn = sqlite3.connect('test.db')
# 创建一个Cursor
>>> cursor = conn.cursor()
# 执行一条SQL语句，创建user表
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
<sqlite3.Cursor object at 0x000001FC7DB667A0>
# 继续执行一条SQL语句，插入一条记录
>>> cursor.execute('insert into user (id,name) values (\'1\',\'Jason\')')
<sqlite3.Cursor object at 0x000001FC7DB667A0>
# 通过rowcount获得插入的行数
>>> cursor.rowcount		   
1
# 关闭Cursor
>>> cursor.close()
# 提交事务
>>> conn.commit()
# 关闭连接Connection
>>> conn.close()

然后再试试查询记录：

# 创建Connection连接
>>> conn = sqlite3.connect('test.db')
# 创建游标Cursor
>>> cursor = conn.cursor()
# 执行一条查询的SQL
>>> cursor.execute('select * from user where id=?', ('1',))
<sqlite3.Cursor object at 0x000001FC7DB66730>
# 获取查询结果集
>>> values = cursor.fetchall()
# 打印values
>>> values
[('1', 'Jason')]
# 关闭游标
>>> cursor.close()
# 关闭连接
>>> conn.close()

说明：

使用Python的DB-API时，只要搞清楚Connection和Cursor对象，打开后一定记得关闭(否则资源就会泄露)，就可以放心的使用。如何确保出错的情况下也关闭调Connection对象和Cursor对象？使用try...except...finally。
使用Cursor对象执行insert、update、delete语句时，执行结果由rowcount返回影响的行数，就可以拿到执行结果
使用Cursor对象执行select语句时，通过fetchall()可以拿到结果集。结果集是一个list，每个元素都是一个tuple，对应一行记录
如果SQL语句带有参数，需要把参数按位置传递给execute()方法，有几个?占位符就必须对应几个参数。例如：

cursor.execute('select * from user where name=? and pwd=?', ('abc', 'password'))

SQLite支持常见的标准SQL语句以及几种常见的数据类型

2. 使用MySQL

MySQL是Web世界中使用最广泛的数据库服务器。

MySQL概述：

MySQL是为服务端设计的数据库，能承受高并发访问
跨平台
占用的内存也远远大于SQLite
MySQL内部有多种数据库引擎，最常用的引擎是支持数据库事务的InnoDB

1. 安装MySQL

安装时会提示输入root用户的口令，可把口令设为password
Windows安装选utf-8编码，以便正确的处理中文
Mac/Linux安装需编辑MySQL配置文件(位于/etc/my.conf或/etc/mysql/my.conf)，把数据库默认的编码全部改为utf-8

启动MySQL：

>>> mysql -uroot -p
# 输入密码
>>> show variables like '%char%';

+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | gbk                                           |
| character_set_connection | gbk                                           |
| character_set_database   | utf8mb4                                       |
| character_set_filesystem | binary                                        |
| character_set_results    | gbk                                           |
| character_set_server     | utf8mb4                                       |
| character_set_system     | utf8                                          |
| character_sets_dir       | D:\Python\mysql-8.0.21-winx64\share\charsets\ |
+--------------------------+-----------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

Mac看到utf-8字样、Windows看到gbk字样就表示编码设置正确

注意：如果MySQL的版本 ≥ 5.5.3，可以把编码设置为utf8mb4，utf8mb4和utf8完全兼容，但它支持最新的Unicode标准，可以显示emoji字符

2. 安装MySQL驱动

MySQL服务器以独立的进程运行，并通过网络对外服务，所以需要支持Python的MySQL驱动来连接MySQL服务器。

安装MySQL：

pip install mysql-connector-python --allow-external mysql-connector-python

或

pip install mysql-connector

连接到MySQL服务器的test数据库：

# 导入MySQL驱动
>>> import mysql.connector
# 注意把password设为你的root口令
>>> conn = mysql.connector.connect(user='root', password='password', database='test')
>>> cursor = conn.cursor()
# 创建user表
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
# 插入一行记录，注意MySQL的占位符是%s
>>> cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Jason'])
>>> cursor.rowcount
1
# 提交事务
>>> conn.commit()
>>> cursor.close()
# 运行查询
>>> cursor = conn.cursor()
>>> cursor.execute('select * from user where id = %s', ('1',))
>>> values = cursor.fetchall()
>>> values
[('1', 'Jason')]
# 关闭cursor和connection
>>> cursor.close()
True
>>> conn.close()

Python的DB-API定义都是通用的，所以操作MySQL的数据库代码和SQLite类似。

小结

执行insert等操作后要调用commit()提交事务
MySQL的SQL占位符是%s

3. 使用SQLAlchemy

数据库表是一个二维表，包含多行多列。把一个表的内容用Python的数据结构-list表示多行，list的每个元素是tuple表示一行数据。比如包含id和name的user表：(Python的DB-API返回的数据结构就是像下面这样表示的)

[
	('1', 'Jason'),
	('2', 'Bob'),
	('3', 'Alice')
]

但tuple很难看出表的结构，把一个tuple用class实例表示会更容易看出表的结构：

class User(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name

data = [
    User('1', 'Jason'),
    User('2', 'Bob'),
    User('3', 'Alice')
]

这就是 ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上。很简单，ORM框架做转换。

安装SQLAlchemy：

pip install sqlalchemy

# 导入
from sqlalchemy import Column, String, create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类
Base = declarative_base()

# 定义User对象
class User(Base):
    # 表的名字
    __tablename__ = 'user'

    # 表的结构
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # 一对多：一个user有多个book
    # 当查询一个User对象时，该对象的books属性将返回一个包含若干个Book对象的list
    books = relationship('Book')

# 还可定义其他表
class School(Base):
    # 表名
    __tablename__ = 'School'

    # 表的结构
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # "多"的一方的book表是通过外键关联到user表的
    user_id = Column(String(20), ForeignKey('user.id'))

# 初始化数据库连接
# create_engine()初始化数据库连接,sqlalchemy用一个字符串表示连接信息
# '数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
### 下行代码执行报错：验证失败，但用户名密码正确，Windows执行，原因暂未知
engine = create_engine('mysql+mysqlconnector://root:mysql@127.0.0.1:3306/test')
# 创建DBSession类型
DBSession = sessionmaker(bind=engine)

## 创建数据并存储
# 创建session对象，该对象可视为数据库连接
session = DBSession()
# 创建新User对象
new_user = User(id='5', name='Jason')
# 添加到session
session.add(new_user)
# 提交即保存到数据库
session.commit()
# 关闭session
session.close()

## 查询数据，查出来的数据不是tuple而是User对象
# 创建session
session = DBSession()
# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()返回所有行
user = session.query(User).filter(User.id == '5').one()
# 打印类型和对象的name属性
print('type:', type(user))
print('name:', user.name)
# 关闭session
session.close()

# 运行结果
'''
type: <class '__main__.User'>
name: Jason
'''

ORM就是把数据库表的行与相应的对象建立关联，互相转换。

小结

ORM框架作用：把数据库表的一行记录与一个对象互相做自动转换
正确使用ORM的前提是了解关系数据库的原理

程序员老五

关注

3
点赞
踩
8

收藏

觉得还不错? 一键收藏
打赏
6
评论
Python笔记12（访问数据库）

数据库(Database)：专门用于集中存储和查询的软件，能直接通过条件快读查询到指定的数据。关系型数据库类别：收费：Oracle、SQL Server、DB2、Sybase免费：MySQL、PostgreSQL、sqlite1. 使用SQLite2. 使用MySQL3. 使用SQLAlchemy...
复制链接

扫一扫