Python3-访问数据库

园搬家测试账号 2020-03-25

为了便于程序保存和读取数据,而且,能直接通过条件快速查询到指定 的数据,就出现了数据库(Database)这种专门用于集中存储和查询的软件。


数据库类别

   付费的商用数据库:

  • Oracle,典型的高富帅;
  • SQL Server,微软自家产品,Windows 定制专款;
  • DB2,IBM的产品,听起来挺高端;
  • Sybase,曾经跟微软是好基友,后来关系破裂,现在家境惨淡。


这些数据库都是不开源而且付费的,最大的好处是花了钱出了问题可以 找厂家解决,不过在Web 的世界里,常常需要部署成千上万的数据库 服务器,当然不能把大把大把的银子扔给厂家,所以,无论是Google、 Facebook,还是国内的 BAT,无一例外都选择了免费的开源数据库:

  • MySQL,大家都在用,一般错不了;
  • PostgreSQL,学术气息有点重,其实挺不错,但知名度没有MySQL 高;
  • sqlite,嵌入式数据库,适合桌面和移动应用。

使用SQLite

SQLite 是一种嵌入式数据库,它的数据库就是一个文件。由于 SQLite 本身是C写的,而且体积很小,所以,经常被集成到各种应用程序中, 甚至在 iOS 和Android 的App 中都可以集成。
  Python 就内置了 SQLite3,所以,在 Python 中使用 SQLite,不需要安装 任何东西,直接使用。

在使用 SQLite 前,我们先要搞清楚几个概念:

  • 表是数据库中存放关系数据的集合,一个数据库里面通常都包含多个 表,比如学生的表,班级的表,学校的表,等等。
  • 表和表之间通过外键 关联。
  • 要操作关系数据库,首先需要连接到数据库,一个数据库连接称为 Connection;
  • 连接到数据库后,需要打开游标,称之为Cursor,通过Cursor 执行 SQL 语句,然后获得执行结果。

Python 定义了一套操作数据库的API 接口,任何数据库要连接到 Python,只需要提供符合 Python 标准的数据库驱动即可。 由于 SQLite 的驱动内置在 Python 标准库中,所以我们可以直接来操作 SQLite 数据库。 我们在 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 0x10f8aa260>
# 继续执行一条 SQL 语句,插入一条记录:
>>> cursor.execute(‘insert into user (id, name) values (\‘1\‘,\‘Michael\‘)‘)
<sqlite3.Cursor object at 0x10f8aa260>
# 通过 rowcount 获得插入的行数:
>>> cursor.rowcount
1
# 关闭 Cursor:
>>> cursor.close()
# 提交事务:
>>> conn.commit()
# 关闭 Connection:
>>> conn.close()
我们再试试查询记录:
>>> conn = sqlite3.connect(‘test.db‘)
>>> cursor = conn.cursor()
# 执行查询语句:
>>> cursor.execute(‘select * from user where id=?‘, ‘1‘)
<sqlite3.Cursor object at 0x10f8aa340>
# 获得查询结果集:
>>> values = cursor.fetchall()
>>> values
[(‘1‘, ‘Michael‘)]
>>> cursor.close()
>>> conn.close()

注意:

  1. 使用 Python 的DB-API 时,只要搞清楚 Connection 和 Cursor 对象,打开后记得关闭
  2. 使用Cursor对象执行insert,update,delete语句时,rowcount返回影响的行数,就可以拿到执行结果。
  3. 使用 Cursor 对象执行 select 语句时,通过 featchall()可以拿到结果集。结果集是一个 list,每个元素都是一个 tuple,对应一行记录。
  4. 如果 SQL语句带有参数,那么需要把参数按照位置传递给 execute()方法,有几个?占位符就必须对应几个参数,例如:  cursor.execute(‘select * from user where id=?‘, ‘1‘)
  5. SQLite 支持常见的标准 SQL语句以及几种常见的数据类型。
  6. 具体文档在 Python 中操作数据库时,要先导入数据库对应的驱动,然后,通过Connection 对象和 Cursor 对象操作数据。
  7. 要确保打开的 Connection 对象和 Cursor对象都正确地被关闭,否则,资源就会泄露。( try:...except:...finally:...)


练习

#请编写函数,在 Sqlite 中根据分数段查找指定的名字:

# -*- coding: utf-8 -*-
import os, sqlite3
db_file = os.path.join(os.path.dirname(__file__), ‘test.db‘)
if os.path.isfile(db_file):
    os.remove(db_file)
# 初始数据:
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute(‘create table user(id varchar(20) primary key, name varchar(20), score int)‘)
cursor.execute(r"insert into user values (‘A-001‘, ‘Adam‘, 95)")
cursor.execute(r"insert into user values (‘A-002‘, ‘Bart‘, 62)")
cursor.execute(r"insert into user values (‘A-003‘, ‘Lisa‘, 78)")
cursor.close()
conn.commit()
conn.close()
def get_score_in(low, high):
    #返回指定分数区间的名字,按分数从低到高排序 ‘
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()
    cursor.execute(‘select name from user where score between ? and ? order by score asc‘,(low,high))
    values = cursor.fetchall()
    name= [x[0]for x in values]
    cursor.close()
    conn.close()
    #print(name)
    return name


# 测试:
assert get_score_in(80, 95) == [‘Adam‘], get_score_in(80, 95)
assert get_score_in(60, 80) == [‘Bart‘, ‘Lisa‘], get_score_in(60, 80)
assert get_score_in(60, 100) == [‘Bart‘, ‘Lisa‘, ‘Adam‘],get_score_in(60, 100)
print(‘Pass‘)


使用 MySQL


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

SQLite 的特点是量级、可嵌入,但不能承受高并发访问,适合桌面和移动应用。

而MySQL是为服务器端设计的数据库,能承受高并发访问,同时占用的内存也远 远大于 SQLite。

此外,MySQL 内部有多种数据库引擎,最常用的引擎是支持数据库事务的 InnoDB。

MySQL安装:

https://www.cnblogs.com/jpga/p/12566697.html

在Mysql的官网上https://dev.mysql.com/doc/connector-python/en/connector-python-versions.html查看对应关系:

Python3-访问数据库

安装驱动:(我用的第二个,第一个报错说没有--allow-external操作)

$ pip install mysql-connector-python --allow-external mysql-connector-python 或 <span>$ pip install mysql-connector</span>

  第二个驱动装好后使用过程中还是报错,重装了一个pymysql,用法改一下就行

#书中源码

# 导入 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‘, ‘Michael‘])
>>> cursor.rowcount 1 
# 提交事务:
>>> conn.commit() 
>>> cursor.close() 
# 运行查询:
>>> cursor = conn.cursor()
>>> cursor.execute(‘select * from user where id = %s‘, [‘1‘])
>>> values = cursor.fetchall() 
>>> values
[(‘1‘, ‘Michael‘)] 
# 关闭 Cursor 和 Connection:
>>> cursor.close() True
>>> conn.close() 


#由于 Python 的DB-API 定义都是通用的,所以,操作MySQL的数据库 代码和 SQLite 类似。
#修改后

import pymysql
conn = pymysql.connect(‘localhost‘,‘root‘, ‘password‘, ‘test‘)
cursor = conn.cursor()
#创建 user 表:
cursor.execute(‘create table user1 (id varchar(20) primary key, name varchar(20))‘)
#插入一行记录,注意 MySQL 的占位符是%s:
cursor.execute(‘insert into user1 (id, name) values (%s, %s)‘, [‘1‘, ‘Michael‘])
print(cursor.rowcount)
conn.commit()
cursor.close()

#运行查询
cursor = conn.cursor()
cursor.execute(‘select * from user1 where id = %s‘, [‘1‘])
values = cursor.fetchall()#[(‘1‘, ‘Michael‘)]
# 关闭 Cursor 和 Connection: >>> cursor.close() True
conn.close()

连接的test数据库需要事先在mysql中创建

 其他常用操作:

show databases;        # 显示所有数据库
create database t1;     # 创建数据库t1
use database t1;       #指定当前操作的数据库为t1
drop database t1;      #删除数据库t1

mysql-connector使用案例:

https://www.cnblogs.com/vathe/p/6964086.html


使用 SQL Alchemy

数据库表是一个二维表,包含多行多列。

按 Python 的 数据结构:list 表示多行,list 的每一个元素是 tuple,表示一行记录,例如包含 id 和 name 的 user 表:[(‘1‘, ‘Michael‘), (‘2‘, ‘Bob‘), (‘3‘, ‘Adam‘)]

但是用 tuple 表示一行表的结构不明显,用 class 实例来表示,会更清晰:

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

[User(‘1‘, ‘Michael‘), User(‘2‘, ‘Bob‘), User(‘3‘, ‘Adam‘)]

ORM技术:Object-Relational Mapping,把关系数据库的表结构映射到对象上。

ORM框架可完成此转换。在 Python 中,最有名的ORM框架是 SQLAlchemy。

SQLAlchemy 的用法如下。

  1. 首先通过 pip 安装 SQLAlchemy: $ pip install sqlalchemy
  2. 然后,利用上次我们在MySQL的test 数据库中创建的 user表,用 SQLAlchemy 来试试:

以下代码完成 SQLAlchemy 的初始化和具体每个表的 class 定义。

# 导入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker 
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))

# 初始化数据库连接:
engine =
create_engine(‘mysql+mysqlconnector://root::3306/test‘) # 创建 DBSession 类型:
DBSession = sessionmaker(bind=engine)

如果 有多个表,就继续定义其他 class,例如 School:

class School(Base):
  __tablename__ = ‘school‘

id = ...

name = ...

create_engine()用来初始化数据库连接。

SQLAlchemy 用一个字符串表示连接信息:

‘数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名‘

你只需要根据需要替换掉用户名、口令等信息即可。 下面,我们看看如何向数据库表中添加一行记录。 由于有了ORM,我们向数据库表中添加一行记录,可以视为添加一个 User 对象:

# 创建 session 对象:
session = DBSession() 
# 创建新 User 对象:
new_user = User(id=‘5‘, name=‘Bob‘) 
# 添加到 session: 
session.add(new_user) 
# 提交即保存到数据库: 
session.commit() 
# 关闭 session: 
session.close()

可见,关键是获取 session,然后把对象添加到 session,最后提交并关 闭。DBSession 对象可视为当前数据库连接。
如何从数据库表中查询数据呢?有了ORM,查询出来的可以不再是 tuple,而是 User对象。SQLAlchemy 提供的查询接口如下:

# 创建 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: Bob
session = DBSession()
# 创建Query查询,filter是where条件,最后调用one()返回唯一行,如果调用all()则返回所有行:
user = session.query(User).filter(User.name==‘Bob‘).all()
# 打印类型和对象的name属性:
print(‘type:‘, type(user))
for row in user:
    print(‘id: %s , name: %s‘%(row.id, row.name))

type: <class ‘list‘>
id: 10 , name: Bob
id: 111 , name: Bob
id: 112 , name: Bob
id: 132 , name: Bob
id: 138 , name: Bob
id: 14 , name: Bob
id: 5 , name: Bob
id: 6 , name: Bob
id: 8 , name: Bob
id: 9 , name: Bob

基本使用:

https://www.jianshu.com/p/cf77beef7a25

可见,ORM就是把数据库表的行与相应的对象建立关联,互相转换。
由于关系数据库的多个表还可以用外键实现一对多、多对多等关联,相 应地,ORM框架也可以提供两个对象之间的一对多、多对多等功能。 例如,如果一个User 拥有多个Book,就可以定义一对多关系如下:

#例如,如果一个User 拥有多个Book,就可以定义一对多关系如下:
class User(Base):
    __tablename__ = ‘user‘
    id = Column(String(20), primary_key=True) 
    name = Column(String(20)) 
    # 一对多:
    books = relationship(‘Book‘) 

class Book(Base): 
    __tablename__ = ‘book‘
    id = Column(String(20), primary_key=True) 
    name = Column(String(20)) 
    # “多”的一方的 book 表是通过外键关联到 user 表的: 
    user_id = Column(String(20), ForeignKey(‘user.id‘))

当我们查询一个User 对象时,该对象的 books 属性将返回一个包含若 干个Book 对象的 list。 小结 ORM框架的作用就是把数据库表的一行记录与一个对象互相做自动转 换。
正确使用ORM的前提是了解关系数据库的原理。

相关推荐

我的剑就是你的剑 / 0评论 2020-02-01