#oracle版

首先新建python腳本(d:/python/orclImport.py)

import os #引入os包

if __name__ == '__main__':

#遍歷文件夾下所有的dmp文件，其中filename為所有文件、文件夾的名稱。

#因為我文件夾下確定都是dmp文件，所以無需進行特殊判斷

for filename in os.listdir("D:\dmp"):

# 調用cmd窗口，并執行dmp的導入命令

# 因為dmp文件數據量太大，我選擇一條一條的去執行，就是如此，電腦已經打字都卡了

os.system("imp orcl/123456@127.0.0.1/zrr file=\"D:\dmp\\"+filename+"\" full=y ignore=y")

在命令行執行 python d:/python/orclImport.py

# mysql版

import os

import pymysql

# import datetime, time

# from config import *

database_table_name = "stockdata"

infile_path = 'E:/學習資料/stock_data'

outfile_path = 'E:/學習資料/stock_data1'

# 刪除首行的股票代碼和空行

def delblankline(infile, outfile):

infp = open(infile, "r")

outfp = open(outfile, "w")

lines = infp.readlines()

for li in lines[1:]:

if li.split():

outfp.writelines(li)

infp.close()

outfp.close()

def csv2bigcsv(path, outfile):

"""1.刪除股票代碼+空行 2.合并所有文件數據"""

# infp = open(infile, "r")

outfp = open(outfile, "w")

i = 1

for filename in os.listdir(path):

domain = os.path.abspath(path) # 獲取文件夾的路徑，也可去掉

filepath = os.path.join(domain, filename) # 文件的完整路徑

infp = open(filepath, 'r') # 讀取文件內容

lines = infp.readlines()

if i == 1: outfp.writelines(lines[3].split())

for li in lines[5:]:

if li.split():

outfp.writelines(li)

infp.close()

i += 1

outfp.close()

# 執行sql語句

def db_excute(sql):

# local_infile = 1 執行load data infile

db_info = {

'host': '127.0.0.1',

'user': 'root',

'password': 'root',

'port': 3306,

'database': 'test',

'charset': 'utf8',

'local_infile': 1

}

db = pymysql.connect(**db_info)

# db.set_charset('utf8')

cursor = db.cursor()

try:

cursor.execute(sql)

db.commit()

except pymysql.Error as e:

print(e)

finally:

db.close()

# 將單個csv文件插入數據庫

def csv2mysql(file_path, database_table_name):

''' LOAD DATA LOCAL INFILE "/root/test/infohash_20180603.txt"

INTO TABLE xl_log_analysis.infohash_20180603

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES ( infohash );'''

'''LINES TERMINATED BY \\r\\n WIN 為\\r,LINUX 為\\n'''

# " LOAD DATA LOCAL INFILE '" + file_path + "'"\

# + " INTO TABLE " + database_table_name \

# + " FIELDS TERMINATED BY ',' " \

# + " OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' " \

# + " LINES TERMINATED BY '\\r\\n' " \

# + " IGNORE 5 LINES; "

# 報1148錯誤，可以本地開啟local_infile服務

# set global local_infile = 'ON';

sql = '''LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1}

CHARACTER SET UTF8

FIELDS TERMINATED BY ','

ENCLOSED BY '\"' ESCAPED BY '\"'

LINES TERMINATED BY '\\r\\n'

IGNORE 5 LINES (`symbol`, `date`, `opening_price`, `highest_price` , `lowest_price`, `closing_price`,

`change`, `trading_volume`, `turnover`, `circulation_market_value`, `total_value`,

`turnover_rate`, `post_resumption_price`, `report_type`, `report_time`, `PE_TTM`,

`PS_TTM`, `PCF_TTM`, `PB`, `ex_duplicate_price`, `tradable_share`, `total_stock_issue`,

`total_assets`, `current_asset`, `gross_liabilities`, `current_liabilities`, `total_operating_income`,

`operating_income`, `total_operating_costs`, `operating_costs`, `operating_profit`, `net_profits`,

`selling_expenses`, `administration_expenses`, `financial_expenses`, `net_cash_flow`,

`NOCF`, `NICF`, `NFCF`, `ROE_TTM`);'''.format(file_path, database_table_name)

db_excute(sql)

# print(sql)

# 將文件路徑下的所有csv文件插入數據庫

def to_mysql(infile_path, database_table_name):

for filename in os.listdir(infile_path):

print('正在上傳：', filename)

# domain = os.path.abspath(infile_path) # 獲取文件夾的路徑，也可去掉

# file_path = os.path.join(domain, filename) # 文件的完整路徑

file_path = infile_path + '/' + filename

# print('file_path: ',file_path)

csv2mysql(file_path, database_table_name)

if __name__ == '__main__':

# delblankline("sh600000.csv", "sh600000_.csv")

# csv2bigcsv(infile_path, "all-groups.csv")

# 創建新表

createtable_sql = '''CREATE TABLE IF NOT EXISTS `{0}` (

`id` INT NOT NULL AUTO_INCREMENT COMMENT 'ID',

`symbol` VARCHAR ( 10 ) DEFAULT NULL COMMENT '股票代碼',

`date` DATE DEFAULT NULL COMMENT '日期',

`opening_price` FLOAT DEFAULT NULL COMMENT '開盤價',

`highest_price` FLOAT DEFAULT NULL COMMENT '最高價',

`lowest_price` FLOAT DEFAULT NULL COMMENT '最低價',

`closing_price` FLOAT DEFAULT NULL COMMENT '收盤價',

`change` FLOAT DEFAULT NULL COMMENT '漲跌幅',

`trading_volume` BIGINT DEFAULT NULL COMMENT '交易量',

`turnover` BIGINT DEFAULT NULL COMMENT '交易額',

`circulation_market_value` BIGINT DEFAULT NULL COMMENT '流通市值',

`total_value` BIGINT DEFAULT NULL COMMENT '總市值',

`turnover_rate` FLOAT DEFAULT NULL COMMENT '換手率',

`post_resumption_price` FLOAT DEFAULT NULL COMMENT '后復權價格',

`report_type` VARCHAR ( 64 ) DEFAULT NULL COMMENT '報告類型',

`report_time` DATE DEFAULT NULL COMMENT '報告時間',

# 市盈率PE 市凈率PB 市銷率PS 市現率PCF

`PE_TTM` FLOAT DEFAULT NULL COMMENT '市盈率TTM',

`PS_TTM` FLOAT DEFAULT NULL COMMENT '市銷率TTM',

`PCF_TTM` FLOAT DEFAULT NULL COMMENT '市現率TTM',

`PB` FLOAT DEFAULT NULL COMMENT '市凈率',

`ex_duplicate_price` FLOAT DEFAULT NULL COMMENT '前復權價格',

`tradable_share` BIGINT DEFAULT NULL COMMENT '流通股本',

`total_stock_issue` BIGINT DEFAULT NULL COMMENT '總股本',

`total_assets` BIGINT DEFAULT NULL COMMENT '總資產',

`current_asset` BIGINT DEFAULT NULL COMMENT '流動資產',

`gross_liabilities` BIGINT DEFAULT NULL COMMENT '總負債',

`current_liabilities` BIGINT DEFAULT NULL COMMENT '流動負債',

`total_operating_income` BIGINT DEFAULT NULL COMMENT '營業總收入',

`operating_income` BIGINT DEFAULT NULL COMMENT '營業收入',

`total_operating_costs` BIGINT DEFAULT NULL COMMENT '營業總成本',

`operating_costs` BIGINT DEFAULT NULL COMMENT '營業成本',

`operating_profit` BIGINT DEFAULT NULL COMMENT '營業利潤',

`net_profits` BIGINT DEFAULT NULL COMMENT '凈利潤',

`selling_expenses` BIGINT DEFAULT NULL COMMENT '銷售費用',

`administration_expenses` BIGINT DEFAULT NULL COMMENT '管理費用',

`financial_expenses` BIGINT DEFAULT NULL COMMENT '財務費用',

`net_cash_flow` BIGINT DEFAULT NULL COMMENT '凈現金流',

`NOCF` BIGINT DEFAULT NULL COMMENT '經營活動凈現金流operating activities',

`NICF` BIGINT DEFAULT NULL COMMENT '投資活動凈現金流investment activities',

`NFCF` BIGINT DEFAULT NULL COMMENT '籌資活動凈現金流financing activities',

`ROE_TTM` BIGINT DEFAULT NULL COMMENT '凈資產收益率TTM Net asset yield',

PRIMARY KEY ( `id` )

) ENGINE = INNODB DEFAULT CHARSET = UTF8MB4;'''.format(database_table_name)

db_excute(createtable_sql) # 創建新表

print(createtable_sql)

to_mysql(infile_path, database_table_name) # 導入infohash數據到mysql xl_log_analysis 表

總結

以上是生活随笔為你收集整理的python 导入csv文件到oracle_python将文件夹下的所有csv文件存入mysql和oracle数据库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。