- 环境准备
- 安装Python、cx_oracle, 解压instantclient-basic-windows.x64-12.2.0.1.0.zip
- 将解压后instantclient_11_2路径下的oci.dll、oraocci11.dll、oraociei11.dll三个文件复制到python的安装目录下。
- 在解压后instantclient_12_2路径下新建文件夹,并将tnsnames.ora文件复制过来。
Ora文件用技术本打开写上
ZSJTNW75 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 200.200.19.75)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ghyzsjt)
)
)
再保存
(4)配置环境变量
(a)新建ORACLE_HOME,变量值为配置解压后的instantclient_12_2路径。
(b)在变量名为PATH的变量值后新增python的安装路径、python下script文件夹路径、 解压后的instantclient_11_2路径。
(5)测试
import cx_Oracle
db = cx_Oracle.connect('userName/password@IP/XXXXX')
cursor = db.cursor()
cursor.execute("select * from product_component_version")
data = cursor.fetchone()
print(data)
cursor.close()
db.close()
若结果如下则证明成功
- 将文件从数据库中导出
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' # 解决中文乱码
conn = cx_Oracle.connect('etl/etl_b4200@200.200.19.75/ghyzsjt')
cursor = conn.cursor()
# 从库中以表形式取出
dns = cx_Oracle.makedsn("200.200.19.75",'1521',"ghyzsjt")
engine = create_engine("oracle://etl:etl_b4200@"+dns, encoding='gbk', echo=False)
data = pd.read_sql('SELECT * FROM ETL.GKFW',engine).values.tolist()
# 写入指定的文件夹
# file_csv = open(r"{0}".format('A:\Test/0918.csv'), "w+")
a = input('请输入csv文件名:')
file_csv = open(r'A:/Test/{}.csv'.format(a), "w+")
writer_csv = csv.writer(file_csv,lineterminator='\n')
for i in data:
writer_csv.writerow(i)
3、再将文件从导入
# 作为父类,让下面子类继承
class ImportOracle(object):
def inoracle(self):
pass
# 在数据库中新建一个表,把表头和表中的数据依次放入
def connOracle(self):
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.utf8' #解决中文乱码
conn = cx_Oracle.connect('etl/etl_b4200@200.200.19.75/ghyzsjt')# 连接数据库
cursor = conn.cursor()
fields = [i + ' varchar2(200)' for i in self.title]
fields_str = ','.join(fields)
# 若数据库只已经有取的表名字则建原表删除
try:
sql = 'drop table %s' % (self.table_name)
print