Python使用cx_Oracle进行数据库操作
我们常常需要在python脚本中连接数据库,并对数据库进行增删改查,有些数据库可以直接连接python,而有些数据库需要借助第三方包。本文将介绍如何使用cx_Oracle包来对Oracle数据库进行操作。(本文仅介绍对已经存在的Orcale数据库进行操作,也就是仅在客户端进行操作)
1、连接数据库
要对数据库进行操作,我们首先要做的就是连接数据库。cx_Oracle数据库提供了两种连接方式。
1 | connection = cx_Oracle.connect( "用户名" , "密码" , "IP地址/服务名" , encoding = "UTF-8" ) |
或者:
1 | connection = cx_Oracle.connect( "用户名/密码@IP地址/服务名" , encoding = "UTF-8" ) |
这两种方式都是使用用户名和密码的方式登录,如果你想使用管理员登录数据库,你可以再增加一个参数,命令如下:
1 | 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数据库,可以使用下面的命令。
1 | connection = cx_Oracle.connect( "circle" , "123456" , "192.168.1.2/helowin" , encoding = "UTF-8" ) |
2、游标
连接到数据库之后,如果想执行SQL语句或者,执行事务,我们还需要一个游标对象。
1 | cursor = connection.cursor() |
我们有cursor.excute()方法和cursor.executemany()两个方法用于执行SQL语句。excute()方法只能执行一条sql语句,executemany()可以执行多条sql语句(类似于事务SQL脚本执行)。这里需要注意到的是excute()执行sql语句不能以;结尾,并且sql必须为一条str。
3、SQL执行
3.1、SQL语句的拼接和执行
我们可以采用原生的python占位符进行SQL语句的拼接(字符串的拼接),例如下面我们需要对一个表进行插入的操作,可以使用以下代码:
1 2 3 4 5 | 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的方式进行命名,我们还可以使用列表的方式,直接把数据进行插入,只不过需要占位。
1 2 3 4 | 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中插入不同的数据可以使用以下代码:
1 2 3 4 5 6 7 8 9 | 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)进行查询:
1 2 3 4 | cursor = connection.cursor() sql = "SELECT * FROM students" result = cursor.execute(sql) next (result) |
得到的结果将会是(1,"张三",13)。如果继续使用next(result),就可以一直取出所有的结果。
fetchone():一次取出一条数据
这个方法,与next(result)是一样的,一次取出一条数据,知道取完。
1 2 3 4 | cursor = connection.cursor() sql = "SELECT * FROM students" result = cursor.execute(sql) print (result.fetchone()) |
(1,"张三",13)
fetchmany():一次取出多条数据
这个方法可以一下子取出多条数据,采用列表储存。
1 2 3 4 | cursor = connection.cursor() sql = "SELECT * FROM students" result = cursor.execute(sql) print (result.fetchmany( 3 )) |
[(1,"张三",13),(2,"李四",12),(3,"王五",14)]
fetchall():一次取出所有数据
一次性将所有查询到的数据用列表返回:
1 2 3 4 | 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,以及查询语句即可。
1 2 3 4 5 6 | 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) |
采用这种方式得到的将会是全部的查询结果,而且更加方便我们对后续的数据进行操作(推荐!!)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性