Python 数据库接口支持非常多的数据库:MySQL 、 PostgreSQL、Microsoft SQL Server 2000、 Informix 、Interbase、Oracle 、Sybase。通过python连接MySQL可以通过以下python库:
MySQLdb
MySQLdb 是用于Python链接Mysql数据库的接口,MySQLdb又叫MySQL-python ,是 Python 连接 MySQL 最流行的一个驱动,很多框架也是依据其开发的。只支持python2.x。
Mysqlclient
完全兼容MySQLdb的衍生版;同时兼容Python3.x,原生SQL操作数据库。由于MySQLdb年久失修,后来出现了它的 Fork 版本 mysqlclient,完全兼容 MySQLdb
PyMysql
纯Python实现的驱动。速度不如MySQLdb,兼容MySQLdb,推荐使用PyMysql连接mysql
Python里封装的数据库操作很简单:
1、获取连接conn;
2、获取游标cursor;
3、使用cursor.execute()执行SQL语句;
4、使用cursor.rowcount返回执行insert,update,delete语句受影响的行数;
5、使用cursor.fetchall()获取查询的结果集。结果集是一个list,每个元素都是一个tuple,对应一行记录;
6、关闭游标和连接。
如果SQL语句带有参数,那么需要把参数按照位置传递给cursor.execute()方法,有几个?占位符就必须对应几个参数,示例:
cursor.execute('select * from user where name=? ', ['abc'])
为了能在出错的情况下也关闭掉Connection对象和Cursor对象,建议实际项目里使用try:...except:...finally:...结构。
一、SQLite
SQLite是一种嵌入式数据库,SQLite本身是C写的体积很小,它的数据库就是一个文件,所以经常被集成到各种应用程序中,甚至在iOS和Android的App中都可以集成。Python内置了sqlite3。在Python中使用SQLite,不需要安装任何东西,直接使用。
在使用SQLite前先要搞清楚几个概念:表是数据库中存放关系数据的集合,一个数据库里面通常都包含多个表,表和表之间通过外键关联。要操作关系数据库,首先需要连接到数据库,一个数据库连接称为Connection
;连接到数据库后,需要打开游标(Cursor)
,通过Cursor
执行SQL语句,然后获得执行结果。
#coding:utf-8
import sqlite3
# 连接到SQLite数据库,数据库文件是test.db # 如果文件不存在,会自动在当前目录创建
conn = sqlite3.connect('test.db')
# 创建一个Cursor
cursor = conn.cursor()
# sqlite创建表时,若id为INTEGER类型且为主键,可以自动递增,在插入数据时id填NULL即可
# cursor.execute('create table user(id integer primary key, name varchar(25))') #执行一次
# 插入一条数据
cursor.execute('insert into user(id,name)values(NULL,"yjc")')
# 返回影响的行数
print(cursor.rowcount)
#提交事务,否则上述SQL不会提交执行
conn.commit()
# 执行查询
cursor.execute('select * from user')
# 获取查询结果
print(cursor.fetchall())
# 关闭游标和连接
cursor.close()
conn.close()
输出:
1
[(1, 'yjc'), (2, 'yjc')]
二、Mysql
1. 安装 PyMysql 库
pip3 install pymysql
2. 连接数据库的几种方法
第一种:将各类字段写上
db = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", db="Geek_Web", charset="utf8mb4")
第二种:省略字段
db = pymysql.connect(root,root, Geek_Web)
第三种:构建配置文件
config = {
'host':'localhost',
'port':3306,
'user':'root',
'passwd':'root',
'db':'Geek_Web',
'charset':'utf8mb4',
}
db = pymysql.connect(**config)
connect对象:
db = pymysql.connect(参数列表)
1、host :主机地址,本地 localhost
2、port :端口号,默认3306
3、user :用户名
4、password :密码
5、database :库
6、charset :编码方式,推荐使用 utf8
连接对象的方法:
数据库连接对象(db)的方法
1、db.close() 关闭连接
2、db.commit() 提交到数据库执行
3、db.rollback() 回滚
4、cur = db.cursor() 返回游标对象,用于执行具体SQL命令
游标对象的方法:
游标对象(cur)的方法
1、cur.execute(sql命令,[列表]) 执行SQL命令
2、cur.close() 关闭游标对象
3、cur.fetchone() 获取查询结果集的第一条数据
4、cur.fetchmany(n) 获取n条
5、cur.fetchall() 获取所有记录
3. 操作数据库
from pymsql import * #导入模块
(db = pymysql.connect(...)) #建立数据库连接
cursor = db.cursor() # cursor() 创建操作游标
sql = "SELECT * FROM main"
cursor.execute(sql) # 执行SQL语句
results = cursor.fetchall() # 获取所有记录列表
results = cursor.fetchone() # 获取一条记录列表
db.commit() # 除了查询其他操作都需要保存执行,且需要主动提交,以保存所执行的语句
cursor.close()
db.close() # 关闭数据库连接
4. PyMysql 返回字典数据
PyMysql 默认返回是元组, 在连接数据库时候加上 cursorclass 就可以将数据库内容以字典格式返回
cursorclass=pymysql.cursors.DictCursor
import pymysql
# 连接数据库0
def Mysql_test0():
config = {
'host':'localhost',
'port':3306,
'user':'root',
'passwd':'lwwroot',
'db':'sakila',
'charset':'utf8mb4',
'cursorclass':pymysql.cursors.DictCursor #将数据库内容以字典格式返回,非必须参数
}
db = pymysql.connect(**config)
cursor = db.cursor() #获取操作游标
try:
return (db, cursor)
except:
print("数据库访问失败")
# 连接数据库1
def Mysql_test():
# 连接数据库,cursorclass=pymysql.cursors.DictCursor可以使数据库内容以字典格式返回,非必须参数
db = pymysql.connect(host="localhost", port=3306, user="root", passwd="lwwroot", db="test",cursorclass=pymysql.cursors.DictCursor, charset="utf8")
#方法获取操作游标
cursor = db.cursor()
try:
return (db, cursor)
except:
print("数据库访问失败")
db, cursor = Mysql_test()
print("\n-------------数据库初始状态-------------")
sqla = "SELECT * FROM city"
print(cursor.execute(sqla))
print("\n-------------数据库插入数据-------------")
sqlb = "insert into city(ID, Name, CountryCode, Population) values (1002, 'shanghai', 'CN', 4000000)"
cursor.execute(sqlb)
db.commit()
# print("\n-------------数据库修改数据-------------")
# sqlc = "update city set ID=1003 where name='beijing'"
# cursor.execute(sqlc)
# db.commit()
# print("\n-------------数据库删除数据-------------")
# sqld = "delete from city where name='shanghai'"
# cursor.execute(sqld)
# db.commit()
print("\n-------------数据库插入后状态-------------")
sqle = "SELECT * FROM city"
print(cursor.execute(sqle))
cursor.close()
db.close()
三、Mssql
1. 安装 PyMssql 库
pip3 install pymysql
2. 连接数据库的方法
Mssql 用字典配置不可以用,connect() 用来连接数据库
db = pymssql.connect(host="192.0.0.200",user="ymyg",password="ymyg",database="Geek_Web")
3. 操作数据库
和 Mysql 操作方法一模一样,只不过将 PyMysql 改为 PyMssql 即可,参考上面 PyMssql
4. PyMssql 返回字典数据
只需要在连接数据库时候加上一个 as_dict 字段,将值改为 True 即可
db = pymssql.connect(host="192.0.0.200",user="ymyg",password="ymyg",database="Geek_Web",as_dict=True)
5. PyMssql 参数
connect() 参数
- dsn 连接字符串 主要用于与之前版本的pymssql兼容
- user 用户名
- password 密码
- trusted 布尔值 指定是否使用windows身份认证登陆
- host 主机名
- database 数据库
- timeout 查询超时
- login_timeout 登陆超时
- charset 数据库的字符集
- as_dict 布尔值 指定返回值是字典还是元组
- max_conn 最大连接数
操作方法
- close() 关闭游标
- execute(operation) 执行操作
- execute(operation params) 执行操作 可以提供参数进行相应操作
- executemany(operation paramsseq) 执行操作 Paramsseq 为元组
- fetchone() 在结果中读取下一行
- fetchmany(size=None) 在结果中读取指定数目的行
- fetchall() 读取所有行
- nextset() 游标跳转到下一个数据集
其他方法
- autocommit(status) 布尔值 指示是否自动提交事务 默认的状态是关闭的 如果打开 你必须调用commit()方法来提交事务
- close() 关闭连接
- cursor() 返回游标对象 用于查询和返回数据
- commit() 提交事务
- rollback() 回滚事务
- pymssqlCursor类 用于从数据库查询和返回数据
- rowcount 返回最后操作影响的行数
- connection 返回创建游标的连接对象
- lastrowid 返回插入的最后一行
- rownumber 返回当前数据集中的游标(通过索引)
import time
import pymssql
class MSSQL:
def __init__(self,host,user,pwd,db):
self.host=host
self.user=user
self.pwd=pwd
self.db=db
def GetConnect(self):
if not self.db:
raise(NameError,'没有目标数据库')
self.connect=pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset='utf8')
cur=self.connect.cursor()
if not cur:
raise(NameError,'数据库访问失败')
else:
return cur
def ExecSql(self,sql):
cur=self.GetConnect()
cur.execute(sql)
self.connect.commit()
self.connect.close()
def ExecQuery(self,sql):
cur=self.GetConnect()
cur.execute(sql)
resList = cur.fetchall()
self.connect.close()
return resList
def main():
ms = MSSQL(host="192.168.0.108", user="sa", pwd="sa", db="ComPrject")
resList = ms.ExecQuery("select *from TestModel")
print(resList)
if __name__ == '__main__':
main()
print("执行完成")
四、Oracle
1. 安装 cx_Oracle 库
pip3 install cx_Oracle
2.Oracle instant client 下载安装
下载地址http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html。然后解压下载的压缩包,并将对应的解压位置加入系统变量Path中,将Oracle instant client目录下的oraocci12.dll、oraociei12.dll、oci.dll复制到python安装目
3. 连接数据库的几种方法
第一种:Oracle 连接方法
db = cx_Oracle.connect('root/root@localhost: 1523/orcl')
第二种:省略字段连接方法
db = cx_Oracle.connect('root', 'root', 'localhost: 1523/orcl')
第三种:dsn 方法
makedsn(IP/HOST, PORT, TNSNAME)
dsn = cx_Oracle.makedsn('localhost','1523','orcl')
db = cx_Oracle.connect('root','root',dsn)
4. 操作数据库
和 Mysql 操作方法一模一样,只不过将 PyMysql 改为 cx_Oracle 即可
5. PyMssql 返回字典数据
Oracle 返回字典类型比较麻烦,因为 cx_Oracle 没有集成,所以需要我们自己写返回字典的方法。cx_Oracle 的 Cursor 对象有一个属性 rowfactory 是是用来自定义查询结果的预处理方法的,定义一个闭包
def makedict(cursor):
cols = [d[0] for d in cursor.description]
def createrow(*args):
return dict(zip(cols, args))
return createrow
并将其注册给游标对象的rowfactory属性 cursor.rowfactory = makedict(cursor) 得到的结果自动由元组转为字典了,但要注意,注册的动作需要在每次执行 cursor.execute 之后都重复一次。最终的方法是定义了一个类来继承 Cursor 对象,这样就不需要重复注册了
import cx_Oracle
#连接数据库,下面括号里内容根据自己实际情况填写
conn = cx_Oracle.connect('用户名/密码@IP:端口号/SERVICE_NAME')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
#使用execute方法执行SQL语句
result=cursor.execute('Select member_id from member')
#获取所有数据
all_data=cursor.fetchall()
#使用fetchone()方法获取一条数据
#data=cursor.fetchone()
#获取部分数据,8条
#many_data=cursor.fetchmany(8)
print (all_data)
db.close()
SQLite是一种嵌入式数据库,SQLite本身是C写的体积很小,它的数据库就是一个文件,所以经常被集成到各种应用程序中,甚至在iOS和Android的App中都可以集成。Python内置了sqlite3。在Python中使用SQLite,不需要安装任何东西,直接使用。
在使用SQLite前先要搞清楚几个概念:表是数据库中存放关系数据的集合,一个数据库里面通常都包含多个表,表和表之间通过外键关联。要操作关系数据库,首先需要连接到数据库,一个数据库连接称为Connection
;连接到数据库后,需要打开游标(Cursor)
,通过Cursor
执行SQL语句,然后获得执行结果。
#coding:utf-8
import sqlite3
# 连接到SQLite数据库,数据库文件是test.db # 如果文件不存在,会自动在当前目录创建
conn = sqlite3.connect('test.db')
# 创建一个Cursor
cursor = conn.cursor()
# sqlite创建表时,若id为INTEGER类型且为主键,可以自动递增,在插入数据时id填NULL即可
# cursor.execute('create table user(id integer primary key, name varchar(25))') #执行一次
# 插入一条数据
cursor.execute('insert into user(id,name)values(NULL,"yjc")')
# 返回影响的行数
print(cursor.rowcount)
#提交事务,否则上述SQL不会提交执行
conn.commit()
# 执行查询
cursor.execute('select * from user')
# 获取查询结果
print(cursor.fetchall())
# 关闭游标和连接
cursor.close()
conn.close()
输出:
1
[(1, 'yjc'), (2, 'yjc')]
二、Mysql
1. 安装 PyMysql 库
pip3 install pymysql
2. 连接数据库的几种方法
第一种:将各类字段写上
db = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", db="Geek_Web", charset="utf8mb4")
第二种:省略字段
db = pymysql.connect(root,root, Geek_Web)
第三种:构建配置文件
config = {
'host':'localhost',
'port':3306,
'user':'root',
'passwd':'root',
'db':'Geek_Web',
'charset':'utf8mb4',
}
db = pymysql.connect(**config)
connect对象:
db = pymysql.connect(参数列表)
1、host :主机地址,本地 localhost
2、port :端口号,默认3306
3、user :用户名
4、password :密码
5、database :库
6、charset :编码方式,推荐使用 utf8
连接对象的方法:
数据库连接对象(db)的方法
1、db.close() 关闭连接
2、db.commit() 提交到数据库执行
3、db.rollback() 回滚
4、cur = db.cursor() 返回游标对象,用于执行具体SQL命令
游标对象的方法:
游标对象(cur)的方法
1、cur.execute(sql命令,[列表]) 执行SQL命令
2、cur.close() 关闭游标对象
3、cur.fetchone() 获取查询结果集的第一条数据
4、cur.fetchmany(n) 获取n条
5、cur.fetchall() 获取所有记录
3. 操作数据库
from pymsql import * #导入模块
(db = pymysql.connect(...)) #建立数据库连接
cursor = db.cursor() # cursor() 创建操作游标
sql = "SELECT * FROM main"
cursor.execute(sql) # 执行SQL语句
results = cursor.fetchall() # 获取所有记录列表
results = cursor.fetchone() # 获取一条记录列表
db.commit() # 除了查询其他操作都需要保存执行,且需要主动提交,以保存所执行的语句
cursor.close()
db.close() # 关闭数据库连接
4. PyMysql 返回字典数据
PyMysql 默认返回是元组, 在连接数据库时候加上 cursorclass 就可以将数据库内容以字典格式返回
cursorclass=pymysql.cursors.DictCursor
import pymysql
# 连接数据库0
def Mysql_test0():
config = {
'host':'localhost',
'port':3306,
'user':'root',
'passwd':'lwwroot',
'db':'sakila',
'charset':'utf8mb4',
'cursorclass':pymysql.cursors.DictCursor #将数据库内容以字典格式返回,非必须参数
}
db = pymysql.connect(**config)
cursor = db.cursor() #获取操作游标
try:
return (db, cursor)
except:
print("数据库访问失败")
# 连接数据库1
def Mysql_test():
# 连接数据库,cursorclass=pymysql.cursors.DictCursor可以使数据库内容以字典格式返回,非必须参数
db = pymysql.connect(host="localhost", port=3306, user="root", passwd="lwwroot", db="test",cursorclass=pymysql.cursors.DictCursor, charset="utf8")
#方法获取操作游标
cursor = db.cursor()
try:
return (db, cursor)
except:
print("数据库访问失败")
db, cursor = Mysql_test()
print("\n-------------数据库初始状态-------------")
sqla = "SELECT * FROM city"
print(cursor.execute(sqla))
print("\n-------------数据库插入数据-------------")
sqlb = "insert into city(ID, Name, CountryCode, Population) values (1002, 'shanghai', 'CN', 4000000)"
cursor.execute(sqlb)
db.commit()
# print("\n-------------数据库修改数据-------------")
# sqlc = "update city set ID=1003 where name='beijing'"
# cursor.execute(sqlc)
# db.commit()
# print("\n-------------数据库删除数据-------------")
# sqld = "delete from city where name='shanghai'"
# cursor.execute(sqld)
# db.commit()
print("\n-------------数据库插入后状态-------------")
sqle = "SELECT * FROM city"
print(cursor.execute(sqle))
cursor.close()
db.close()
三、Mssql
1. 安装 PyMssql 库
pip3 install pymysql
2. 连接数据库的方法
Mssql 用字典配置不可以用,connect() 用来连接数据库
db = pymssql.connect(host="192.0.0.200",user="ymyg",password="ymyg",database="Geek_Web")
3. 操作数据库
和 Mysql 操作方法一模一样,只不过将 PyMysql 改为 PyMssql 即可,参考上面 PyMssql
4. PyMssql 返回字典数据
只需要在连接数据库时候加上一个 as_dict 字段,将值改为 True 即可
db = pymssql.connect(host="192.0.0.200",user="ymyg",password="ymyg",database="Geek_Web",as_dict=True)
5. PyMssql 参数
connect() 参数
- dsn 连接字符串 主要用于与之前版本的pymssql兼容
- user 用户名
- password 密码
- trusted 布尔值 指定是否使用windows身份认证登陆
- host 主机名
- database 数据库
- timeout 查询超时
- login_timeout 登陆超时
- charset 数据库的字符集
- as_dict 布尔值 指定返回值是字典还是元组
- max_conn 最大连接数
操作方法
- close() 关闭游标
- execute(operation) 执行操作
- execute(operation params) 执行操作 可以提供参数进行相应操作
- executemany(operation paramsseq) 执行操作 Paramsseq 为元组
- fetchone() 在结果中读取下一行
- fetchmany(size=None) 在结果中读取指定数目的行
- fetchall() 读取所有行
- nextset() 游标跳转到下一个数据集
其他方法
- autocommit(status) 布尔值 指示是否自动提交事务 默认的状态是关闭的 如果打开 你必须调用commit()方法来提交事务
- close() 关闭连接
- cursor() 返回游标对象 用于查询和返回数据
- commit() 提交事务
- rollback() 回滚事务
- pymssqlCursor类 用于从数据库查询和返回数据
- rowcount 返回最后操作影响的行数
- connection 返回创建游标的连接对象
- lastrowid 返回插入的最后一行
- rownumber 返回当前数据集中的游标(通过索引)
import time
import pymssql
class MSSQL:
def __init__(self,host,user,pwd,db):
self.host=host
self.user=user
self.pwd=pwd
self.db=db
def GetConnect(self):
if not self.db:
raise(NameError,'没有目标数据库')
self.connect=pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset='utf8')
cur=self.connect.cursor()
if not cur:
raise(NameError,'数据库访问失败')
else:
return cur
def ExecSql(self,sql):
cur=self.GetConnect()
cur.execute(sql)
self.connect.commit()
self.connect.close()
def ExecQuery(self,sql):
cur=self.GetConnect()
cur.execute(sql)
resList = cur.fetchall()
self.connect.close()
return resList
def main():
ms = MSSQL(host="192.168.0.108", user="sa", pwd="sa", db="ComPrject")
resList = ms.ExecQuery("select *from TestModel")
print(resList)
if __name__ == '__main__':
main()
print("执行完成")
四、Oracle
1. 安装 cx_Oracle 库
pip3 install cx_Oracle
2.Oracle instant client 下载安装
下载地址http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html。然后解压下载的压缩包,并将对应的解压位置加入系统变量Path中,将Oracle instant client目录下的oraocci12.dll、oraociei12.dll、oci.dll复制到python安装目
3. 连接数据库的几种方法
第一种:Oracle 连接方法
db = cx_Oracle.connect('root/root@localhost: 1523/orcl')
第二种:省略字段连接方法
db = cx_Oracle.connect('root', 'root', 'localhost: 1523/orcl')
第三种:dsn 方法
makedsn(IP/HOST, PORT, TNSNAME)
dsn = cx_Oracle.makedsn('localhost','1523','orcl')
db = cx_Oracle.connect('root','root',dsn)
4. 操作数据库
和 Mysql 操作方法一模一样,只不过将 PyMysql 改为 cx_Oracle 即可
5. PyMssql 返回字典数据
Oracle 返回字典类型比较麻烦,因为 cx_Oracle 没有集成,所以需要我们自己写返回字典的方法。cx_Oracle 的 Cursor 对象有一个属性 rowfactory 是是用来自定义查询结果的预处理方法的,定义一个闭包
def makedict(cursor):
cols = [d[0] for d in cursor.description]
def createrow(*args):
return dict(zip(cols, args))
return createrow
并将其注册给游标对象的rowfactory属性 cursor.rowfactory = makedict(cursor) 得到的结果自动由元组转为字典了,但要注意,注册的动作需要在每次执行 cursor.execute 之后都重复一次。最终的方法是定义了一个类来继承 Cursor 对象,这样就不需要重复注册了
import cx_Oracle
#连接数据库,下面括号里内容根据自己实际情况填写
conn = cx_Oracle.connect('用户名/密码@IP:端口号/SERVICE_NAME')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
#使用execute方法执行SQL语句
result=cursor.execute('Select member_id from member')
#获取所有数据
all_data=cursor.fetchall()
#使用fetchone()方法获取一条数据
#data=cursor.fetchone()
#获取部分数据,8条
#many_data=cursor.fetchmany(8)
print (all_data)
db.close()