一、一般读取数据库代码:
import cx_Oracle # 导入数据库
import pandas as pd #导入操作数据集工具
from sqlalchemy import create_engine #导入 sqlalchemy 库,然后建立数据库连接
import time #导入时间模块
import numpy as np #导入numpy数值计算扩展
os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8' #保证select出来的中文显示没有问题
def getData(user,password,database,targetTable,commandText): # 从数据库提取数据 commandText sql命令命令文本 format格式化字符串
connection = cx_Oracle.connect(user,password,database) # connection数据库连接对象
cursor = connection.cursor() # cursor数据库交互对象
cursor.execute(commandText.format(targetTable)) # 使用execute方法执行SQL语句 Execute 将字符串内容当做命令来执行
x = cursor.description # 描述标签
columns = [y[0] for y in x]
cursor01 = cursor.fetchall() # fetchall返回多个元组
cursor.close() # 关闭数据库连接
data = pd.DataFrame(cursor01,columns = columns) #Python读取mysql数据,转为DataFrame格式并根据原TABLE中的COLUMNS指定columns
return data #返回数据
user = 'XX' #用户名
password = 'XXX' #密码
database = 'XXXX' #数据库
targetTable = 'tablename'
commandText = '''select * from tablename'''
data = getData(user,password,database,targetTable,commandText)
其中第一部分代码主要是导入相关模块,第二部分代码主要是输入数据库连接信息。通过以上两步即可从数据库读取数据到python中使用及分析。
二、通过函数读取写入数据
def get_data(table):
conn = cx_Oracle.connect(' user ', ' password', ' database ')
cursor = conn.cursor()
cursor.execute('select * from {}'.format(table) )
x = cursor.description
columns = [y[0] for y in x]
cursor01 = cursor.fetchall()
cursor.close()
data = pd.DataFrame(cursor01,columns = columns)
return data
def data_output(data,dataTable):
connection = cx_Oracle.connect(' user ', ' password', ' database ')
cursor = connection.cursor()
query = "INSERT INTO "+ dataTable + " VALUES ({})"
columns = list(data.columns)
aidx = list(range(1,len(columns)+1))
aidx = [':'+str(i) for i in aidx]
aname = ','.join(aidx)
dtHigh = data.shape[0]
dtWidth = data.shape[1]
creatVar = locals()
wholeData = []
for i in range(dtHigh):
value_list = []
for j in range(dtWidth):
value_list.append("{}".format(str(data.iloc[i,j])))
wholeData.append(value_list)
cursor.executemany(query.format(aname),wholeData)
connectionmit()
cursor.close()
connection.close()
return
从数据库tablename1读取数据到python:
data = get_data('tablename1')
从python输出数据到数据库tablename2:
data_output(data00,'tablename2')
更多推荐
python读取与写入oracle数据库表信息
发布评论