python/使用SQLite-使用MySQL-使用SQLAlchemy

使用SQLite

SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。
Python就内置了SQLite3，要使用SQLite3可以直接导入。
要搞清楚的几个概念：
表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，比如学生的表，班级的表，学校的表，等等。表和表之间通过外键关联。
使用：
要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；
连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后，获得执行结果。
Python定义了一套操作数据库的API接口，任何数据库要连接到Python，只需要提供符合Python标准的数据库驱动即可。
由于SQLite的驱动内置在Python标准库中，所以我们可以直接来操作SQLite数据库。
我们实践一下：

import sqlite3
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute('create table user (id varchar(20) primary key, name varchar(20), telephone varchar(20))')
cursor.execute('insert into user (id,name,telephone) values (\'1\',\'Michael\',\'13456\')')
cursor.execute('insert into user (id,name,telephone) values (\'2\',\'jiebao\',\'987654\')')
cursor.rowcount
cursor.close()
conn.commit()
conn.close()

再试试查询记录：

import sqlite3
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
s = cursor.execute('select * from user where id = ?',('1',))
values1 =s.fetchall()
print(values1)--->[('1', 'Michael', '13456')]
cursor.close()
conn.close()

使用Python的DB-API时，只要搞清楚Connection和Cursor对象，打开后一定记得关闭，就可以放心地使用.
使用Cursor对象执行insert，update，delete语句时，执行结果由rowcount返回影响的行数，就可以拿到执行结果。
使用Cursor对象执行select语句时，通过featchall()可以拿到结果集。结果集是一个list，每个元素都是一个tuple，对应一行记录。
如果SQL语句带有参数，那么需要把参数按照位置传递给execute()方法，有几个?占位符就必须对应几个参数，例如：

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

SQLite支持常见的标准SQL语句以及几种常见的数据类型。具体文档请参阅SQLite官方网站。

使用MySQL

MySQL是Web世界中使用最广泛的数据库服务器。SQLite的特点是轻量级、可嵌入，但不能承受高并发访问，适合桌面和移动应用。而MySQL是为服务器端设计的数据库，能承受高并发访问，同时占用的内存也远远大于SQLite。

安装MySQL

可以直接从MySQL官方网站下载最新的Community Server 5.6.x版本。MySQL是跨平台的，选择对应的平台下载安装文件，安装即可。
分享：一个博客上的安装教程，但是在安装的时候，需要注意两点：

1. 第四步从cmd命令窗口台进入到MySQL解压目录下的 bin 目录下，需要以管理员权限打开C:\Windows\System32下的cmd。
2. 在第六步修改mysql密码时，还是管理员进入cmd，输入mysql –uroot可能会报以下错，右击此电脑–>管理–>服务，打开MySQL服务即可：

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

我的：password=PASSWORD(“123456”) WHERE user=‘root’
在Mac或Linux上，需要编辑MySQL的配置文件，见廖大大教程

安装MySQL驱动

由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以，需要支持Python的MySQL驱动来连接到MySQL服务器。MySQL官方提供了mysql-connector-python驱动，但是安装的时候需要给pip命令加上参数--allow-external：

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

如果上面的命令安装失败，可以试试另一个驱动：

$ pip install mysql-connector

若是按照上面安装后，建立conn的时候报错

"mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported"

可以先卸载：

$ pip uninstall mysql-connector

再正确安装另一个版本：

$ pip install mysql-connector-python

如果是使用集成环境的软件，如anaconda，就可以直接去anaconda Navigator里面安装 mysql-connector-python。
如何连接到MySQL服务器的test数据库：

import mysql.connector

# 注意把password设为你的root口令:
conn = mysql.connector.connect(user='root', password='123456', database='test')
cursor = conn.cursor()
# 创建user表:
cursor.execute('create table user (id varchar(20) primary key,name varchar(20))')
cursor.execute('insert into user (id , name) values (%s, %s)', ['1','Fan'])
print(cursor.rowcount)
#提交事务
conn.commit()
cursor.close()

#运行查询
cursor = conn.cursor()
cursor.execute('select name from user where id = %s',('1',))
values = cursor.fetchall()
print(values)
cursor.close()
conn.close()

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

使用SQLAlchemy

数据库表是一个二维表，包含多行多列。把一个表的内容用Python的数据结构表示出来的话，可以用一个list表示多行，list的每一个元素是tuple，表示一行记录，比如，包含id和name的user表：

[
    ('1', 'Michael'),
    ('2', 'Bob'),
    ('3', 'Adam')
]

Python的DB-API返回的数据结构就是像上面这样表示的。
但是用tuple表示一行很难看出表的结构。如果把一个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的用法。
通过pip安装SQLAlchemy：

$ pip install sqlalchemy

然后，利用上次我们在MySQL的test数据库中创建的user表，用SQLAlchemy来试试：

# 导入:
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:123456@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

---

一般来说，session在需要访问数据库的时候创建，在session访问数据库的时候，准确来说，应该是“add/update/delete”数据库的时候，会开启database transaction。
假设没有修改autocommit的默认值(False), 那么，database transaction 一直会保持，只有等到session发生rolled back、
committed、或者closed的时候才结束，一般建议，当database transaction结束的时候，同时close session,以保证，每次发起请求，都会创建一个新的session

该sessionmaker()创建了一个工厂类，在创建这个工厂类时我们配置了参数绑定了引擎。将其赋值给Session。每次实例化Session都会创建一个绑定了引擎的Session。这样这个session在访问数据库时都会通过这个绑定好的引擎来获取连接资源
当你编写应用程序时， 请将sessionmaker 工厂放在全局级别，视作应用程序配置的一部分。例如：应用程序包中有三个.py文件，您可以将该sessionmaker行放在__init__.py文件中; 在其他模块“from mypackage import Session”。这样，所有的Session()的配置都由该配置中心控制。

---

以上代码完成SQLAlchemy的初始化和具体每个表的class定义。如果有多个表=，就继续定义其他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

可见，ORM就是把数据库表的行与相应的对象建立关联，互相转换。
由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。
一对多：
例如，如果一个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。