python数据库

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

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()

推荐阅读更多精彩内容