run sql or .sql file from python via sqlplus

本文针对没有安装cx_Oracle模块的前提下需要使用python来执行sql语句或者执行sql脚本。
使用Popen和PIPE。

python版本2.7

#!/usr/bin/env python
# -*- coding: utf8 -*-

import os
from subprocess import Popen, PIPE

connectString = 'blue/blue@IP:1521/service_name'
sqlCommand = 'select * from blue.t;'
#sqlCommand = b’select * from blue.t;’  --for python 3 
CommandFile = "@C:\\10053\\crt_test.sql"


def runSqlQuery(sqlCommand, connectString):
   session = Popen(['sqlplus', '-S', connectString], stdin=PIPE, stdout=PIPE, stderr=PIPE)
   session.stdin.write(sqlCommand)
   return session.communicate()

def runSqlFile(CommandFile, connectString):
    session = Popen(['sqlplus','-S',connectString],stdin=PIPE,stdout=PIPE,stderr=PIPE)
    session.stdin.write(CommandFile)
    return session.communicate()

commandResult,errorMessage = runSqlFile(CommandFile,connectString)
queryResult, errorMessage = runSqlQuery(sqlCommand, connectString)
print queryResult

在python3中Popen.stdin.write的sqlCommand为bytes对象,需要从python 2.7的string语法转换为bytes,参见脚本中的注释。

此条目发表在Python分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s