python数据库

2019-07-28 本文已影响0人枫頔

任何应用都需要持久化存儲。一般有三种基础的存储机制：文件、数据库系统及混合类型。

1. 数据库基本操作和SQL

数据库使用文件系统作为基本的持久化存儲，可以是普通的操作系统文件、专用的操作系统文件、原始的磁盘分区等。多数数据库系统提供命令行工具，可执行SQL语句或查询。

关系型数据库管理系统（RDBMS）可管理多个数据库。MySQL是基于服务的RDBMS，其有一个服务器进程始终运行等待命令行输入。

1.1 SQL

数据库命令和查询操作通过SQL语句进行。大部分数据库不区分大小写，一般来说数据库关键字使用大写，命令结尾使用分号表示结束。

SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言。当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库。

SQL语句主要分为：

DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
DCL：数据控制语言，进行授权与权限回收，如grant、revoke
DDL：数据定义语言，进行数据库、表的管理等，如create、drop
CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

1.2 python的DB-API

img

2. 数据库连接

① 使用DB-API的connect()函数传入相关参数连接数据库并返回Connection对象

② 创建Cursor()对象调用execute()方法用来执行相关SQL语句，并进行事务提交（增删改等）

③ 关闭Cursor()和Connection()对象（一般使用try....except.....finally实现）

2.1 MYSQL连接

python3使用pymysql模块实现与数据库的连接和相关操作：

Connection 对象

用于建立与数据库的连接
创建对象：调用connect()方法，conn=connect(参数列表)
参数host：连接的mysql主机，如果本机是'localhost'
参数port：连接的mysql主机的端口，默认是3306
参数database：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，推荐使用utf8

connection对象的方法

close()关闭连接
commit()提交，用于修改数据后进行事务提交（增删改）
cursor()返回Cursor对象，用于执行sql语句并获得结果

Cursor对象

用于执行sql语句，使用频度最高的语句为select、insert、update、delete。
获取Cursor对象：调用Connection对象的cursor()方法，cs1=conn.cursor()
注意将sql语句参数化，避免sql注入。参数化后，执行时要给args传入参数。

cursor对象的方法

close()关闭
execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

对象的属性

rowcount只读属性，表示最近一次execute()执行后受影响的行数
connection获得当前连接对象

# mysql数据库
import pymysql

# 创建connection对象
conn = pymysql.connect(host="localhost", port=3306, database="python_test",
                       user="yuxi", password="199618", charset="utf8")

if conn:
    print("连接数据库成功！")

# 创建cursor对象
cursor = conn.cursor()

# 定义sql执行语句
select_sql = """SELECT * FROM students;"""

# 使用游标对象执行sql语句，返回受影响的行数
count = cursor.execute(select_sql)
print("查出%d条数据，数据如下：" % count)

for info in range(count):
    print(cursor.fetchone())


# 关闭游标对象
cursor.close()

# 关闭connection对象
conn.close()

2.2 PostgreSQL连接

import psycopg2

# 创建connection对象连接数据库
conn = psycopg2.connect(database='testdb', host='localhost', user='fengdi', password='199618', port=5432)

# 创建游标对象
cursor = conn.cursor()

# 使用游标对象执行操作:创建数据表
cursor.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print('数据表创建成功！')

# 事务提交
conn.commit()

# 关闭连接
conn.close()

2.3 sqlite3连接

import sqlite3

# python连接sqlite3
# 创建connection对象连接数据库，文件不存在则会在当前目录新建
conn = sqlite3.connect('sqlite-test.db')
# 创建游标对象
cursor = conn.cursor()
# 执行SQL语句，创建user表
cursor.execute('CREATE TABLE user (id varchar(20) primary key, name varchar(20))')
# 执行SQL语句，插入一条记录
cursor.execute('INSERT INTO user (id, name) values (\'1\', \'fengdi\')')
data = cursor.rowcount
print('插入记录：%d条' % data)
# 关闭游标
cursor.close()
# 事务提交：对数据库、数据表进行更改后需要提交
conn.commit()
# 关闭数据库连接
conn.close()