Python使用cx_Oracle进行数据库操作
我们常常需要在python脚本中连接数据库,并对数据库进行增删改查,有些数据库可以直接连接python,而有些数据库需要借助第三方包。本文将介绍如何使用cx_Oracle包来对Oracle数据库进行操作。(本文仅介绍对已经存在的Orcale数据库进行操作,也就是仅在客户端进行操作)
1、连接数据库
要对数据库进行操作,我们首先要做的就是连接数据库。cx_Oracle数据库提供了两种连接方式。
connection = cx_Oracle.connect("用户名", "密码", "IP地址/服务名", encoding="UTF-8")
或者:
connection = cx_Oracle.connect("用户名/密码@IP地址/服务名", encoding="UTF-8")
这两种方式都是使用用户名和密码的方式登录,如果你想使用管理员登录数据库,你可以再增加一个参数,命令如下:
connection = cx_Oracle.connect("用户名/密码@IP地址/服务名", encoding="UTF-8",mode=cx_Oracle.SYSDBA)
比如我们需要连接一个这样的数据库:HWL=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=helowin)))(这个是Orcale数据库配置数据库的配置文件tnsnames中的某一个数据库),如果我们想要连接这样的一个Orcale数据库,可以使用下面的命令。
connection = cx_Oracle.connect("circle", "123456", "192.168.1.2/helowin", encoding="UTF-8")
2、游标
连接到数据库之后,如果想执行SQL语句或者,执行事务,我们还需要一个游标对象。
cursor = connection.cursor()
我们有cursor.excute()方法和cursor.executemany()两个方法用于执行SQL语句。excute()方法只能执行一条sql语句,executemany()可以执行多条sql语句(类似于事务SQL脚本执行)。这里需要注意到的是excute()执行sql语句不能以;结尾,并且sql必须为一条str。
3、SQL执行
3.1、SQL语句的拼接和执行
我们可以采用原生的python占位符进行SQL语句的拼接(字符串的拼接),例如下面我们需要对一个表进行插入的操作,可以使用以下代码:
cursor = connection.cursor()
sql_insert = "INSERT INTO table(column1, column2, column3) \
VALUES ({}, '{}', {})".format(1,"字符",123.11)
cursor.execute(sql_insert)
connection.commit()
这里我们需要注意在插入sql语句中单引号不能省略(SQL语言中字符用单引号来引起来)。当游标执行之后(类似于SQL事务执行完成),我们最后还需要使用connection.commit()提交这次事务执行。
除了可以使用原生额python字符串进行操作,cursor.excute()还提供变量名来运行SQL语句,上述例子我们可以改一种方式:
cursor = connection.cursor()
student = {"age":1, "name":"字符", "score":123.11}
sql_insert = "INSERT INTO table(column1, column2, column3) VALUES (:age, :name, :score)"
cursor.execute(sql_insert, student)
connection.commit()
注意到了这里在sql_insert里是没有单引号的哟。
如果你不想使用dict的方式进行命名,我们还可以使用列表的方式,直接把数据进行插入,只不过需要占位。
cursor = connection.cursor()
sql_insert = "INSERT INTO table(column1, column2, column3) VALUES (:1, :2, :3)"
cursor.execute(sql_insert, [1, "字符", 123.11])
connection.commit()
3.2、executemany()
上面小结中说到了使用cursor.excute()的三种方式,如果你需要大批量的执行某种模板的操作,比如连续的插入多个数值,我们并不需要写一个for循环来进行,excutemany()可以帮助我们完成。继续上个例子,如果我们想要往table中插入不同的数据可以使用以下代码:
cursor = connection.cursor()
insert_data = [
[1, "字符", 123.11],
[2, "字2", 3124],
[3, "字符3", 2194]
]
sql_insert = "INSERT INTO table(column1, column2, column3) VALUES (:1, :2, :3)"
cursor.executemany(sql_insert, insert_data)
connection.commit()
cursor.excute()第二个参数是一个可循环对象,里面每个元素都会执行一次cursor.excute(),所以这种大规模插入和更新采用这种方式更加迅速。
3.3、获取查询结果
前面了解如何使用游标执行插入的操作(更新的操作类似),需要注意的是在完成操作之后需要记得connection.commit()。
下面我们看一下如何获取查询到的数据,这里提供两种方式。
3.3.1、内置方法:fetchone()、fetchmany()、fetchall()
当我们使用cursor.excute(sql)执行查询操作时,会返回一个可迭代对象(iterator),我们可以使用next()来得到一条查询的结果。比如我有一张表students,结构如下:
学号 | 姓名 | 年龄 |
1 | 张三 | 13 |
2 | 李四 | 12 |
3 | 王五 | 14 |
我对这张表使用cursor.excute(sql)进行查询:
cursor = connection.cursor()
sql = "SELECT * FROM students"
result = cursor.execute(sql)
next(result)
得到的结果将会是(1,”张三”,13)。如果继续使用next(result),就可以一直取出所有的结果。
fetchone():一次取出一条数据
这个方法,与next(result)是一样的,一次取出一条数据,知道取完。
cursor = connection.cursor()
sql = "SELECT * FROM students"
result = cursor.execute(sql)
print(result.fetchone())
(1,”张三”,13)
fetchmany():一次取出多条数据
这个方法可以一下子取出多条数据,采用列表储存。
cursor = connection.cursor()
sql = "SELECT * FROM students"
result = cursor.execute(sql)
print(result.fetchmany(3))
[(1,”张三”,13),(2,”李四”,12),(3,”王五”,14)]
fetchall():一次取出所有数据
一次性将所有查询到的数据用列表返回:
cursor = connection.cursor()
sql = "SELECT * FROM students"
result = cursor.execute(sql)
print(result.fetchall())
[(1,”张三”,13),(2,”李四”,12),(3,”王五”,14)]
3.3.2、使用panda读取成DataFrame
这种方式就需要借助pands库,将查询出来的结果采用DataFrame储存。这种方式就用不到游标么,只需要给出数据库的连接头也就是这里的connection,以及查询语句即可。
import pandas as pd
import cx_Oracle
connection = cx_Oracle.connect("circle", "123456", "192.168.1.2/helowin", encoding="UTF-8")
sql = "SELECT * FROM students"
df = pd.read_sql(sql, connection)
采用这种方式得到的将会是全部的查询结果,而且更加方便我们对后续的数据进行操作(推荐!!)