Python:如何从oracle读取数据，然后写入Excel

Question

Python：如何从oracle读取数据，然后写入Excel，Excel列的部分可以只读吗？

Answer 1

安装数据库驱动程序以连接到数据库和熊猫，以简化Excel编写和数据操作。

$ pip install cx_Oracle pandas

在您的脚本中执行以下操作：

import cx_Oracle
con = cx_Oracle.connect('user/password@dsn')
data = pd.read_sql('SELECT * FROM mytable', con)
con.close()
data.head() # take a peek at data
data.to_excel('my_oracle_table.xlsx')

Answer 2

这个答案来自：Python xlwt - 使列只读(单元格保护(

from xlwt import Workbook, Worksheet, easyxf
# ...
# Protect worksheet - all cells will be read-only by default
my_worksheet.protect = True  # defaults to False
my_worksheet.password = "something_difficult_to_guess"
# Create cell styles for both read-only and editable cells
editable = easyxf("protection: cell_locked false;")
read_only = easyxf("")  # "cell_locked true" is default
# Apply your new styles when writing cells
my_worksheet.write(0, 0, "Can't touch this!", read_only)
my_worksheet.write(2, 2, "Erase me :)", editable)

Answer 3

感谢Robin Nemeth的回答。我对它进行了一些修改，并获得了所需的输出。

import cx_Oracle
con = cx_Oracle.connect('user/password@dsn')
data = pd.read_sql('SELECT * FROM mytable', con)
data.to_excel('my_oracle_table.xlsx')  #called before closing the connection
con.close()

在这里，应在关闭连接之前调用data.to_excel((，否则将导致以下错误"数据库错误：DPI-1040：LOB 已关闭"。

甚至删除了 data.head(( 函数，这不会影响我的输出。

Answer 4

import cx_Oracle
import pandas as pd
import xlsxwriter
con = cx_Oracle.connect('user/password@IP:port/service_name')
with pd.ExcelWriter("", engine="xlsxwriter", options = {'strings_to_numbers': True, 'strings_to_formulas': False}) as writer:
                try:
                    df = pd.read_sql("SELECT * FROM myTABLE", con)
                    df.to_excel(writer, sheet_name = "Sheet1", header = True, index = False)
                    print("File saved successfully!")
                    row = len(df.axes[0])
                except:
                    print("There is an error")