from __future__ import print_function
import cx_Oracle
con = None
try:
con = cx_Oracle.connect('H27091/2kl03KL@BIA.HU:5211/T10')
print ('Orcale db verzio: ', con.version)
except cx_Oracle.Error as error:
print(error) ## ORA-12514: TNS: a figyelő nem ismeri a kapcsolatleíróban kért szolgáltatást
finally:
if con:
con.close() ## Csak akkor zárjuk be ha létezik a kapcsolat
Orcale db verzio: 12.1.0.2.0
# Eredmény csv filebe írása
import cx_Oracle
import csv
con = cx_Oracle.connect('H27091/2kl03KL@BIA.HU:5211/T10')
cur = con.cursor()
f = open("job_history.csv", "w")
writer = csv.writer(f, lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)
cur.execute(" SELECT owner, table_name FROM all_tables where owner like 'HN_EBH27091' and table_name like 'OPT_F%' ")
for row in cur:
print(row)
writer.writerow(row)
f.close()
cur.close()
con.close()
('HN_EBH27091', 'OPT_F_BALANCE_SHEET') ('HN_EBH27091', 'OPT_F_BALANCE_SHEET_ROW') ('HN_EBH27091', 'OPT_F_LEADER') ('HN_EBH27091', 'OPT_F_BALANCE_SHEET_ROW_TMP') ('HN_EBH27091', 'OPT_F_BANK') ('HN_EBH27091', 'OPT_F_BANK_ROW') ('HN_EBH27091', 'OPT_F_OWNER') ('HN_EBH27091', 'OPT_F_CP_PROPERTY') ('HN_EBH27091', 'OPT_F_RELATION') ('HN_EBH27091', 'OPT_F_NEG_EVENT') ('HN_EBH27091', 'OPT_F_GROUP_PROPERTY') ('HN_EBH27091', 'OPT_F_CP_EBH_PROPERTY')
# Eredmény csv filebe írása fejléccel
import cx_Oracle
import csv
con = cx_Oracle.connect('H27091/2kl03KL@BIA.HU:5211/T10')
cur = con.cursor()
SQL = r" select * from CALENDAR where rownum <= 10 "
cur.execute(SQL)
headers = [i[0] for i in cur.description] ## fejléc kiolvasása
# csv file megnyítása írásra:
with open("DATA.csv", "w") as f:
fcsv = csv.writer(f)
fcsv.writerow(headers) ## Fejléc kiírása
for record in cur: fcsv.writerow(record) ## adat kiírása
assert f.closed ## ha minden OK lezár
# kiírt adatok visszaolvasása
with open("DATA.csv", "r") as f:
fcsv = csv.reader(f)
for record in fcsv: print(record)
['SYM_RUN_DATE', 'YYYYMMDD', 'YYYYMM', 'YYYY', 'MONTH_NUM', 'MONTH_NUM_CH', 'DAY_NUM', 'DAY_NUM_CH', 'HOLIDAY_DESC', 'DAY_NO', 'DAY_OF_WEEK_NUM_HU', 'DAY_OF_WEEK_NUM_US', 'DAY_OF_YEAR_NUM', 'DAY_OF_QUARTER_NUM', 'WEEK_OF_MONTH_NUM', 'WEEK_OF_YEAR_NUM', 'QUARTER_NO', 'QUARTER', 'DAY_OF_WEEK_SH_EN', 'DAY_OF_WEEK_SH_HU', 'DAY_OF_WEEK_LO_EN', 'DAY_OF_WEEK_LO_HU', 'IS_WORKDAY', 'IS_WEEKEND', 'WD_BY_YEAR', 'WD_BY_MONTH', 'WD_BY_WEEK', 'WEEKD_BY_DAYNAME_BY_MONTH', 'WEEKD_BY_DAYNAME_BY_YEAR', 'SUM_DAYS_IN_MONTH', 'SUM_DAYS_IN_QUARTER', 'SUM_WD_IN_MONTH'] [] ['2014-08-26 00:00:00', '20140826', '201408', '2014', '8', '08', '26', '26', '', '5352', '2', '3', '238', '57', '4', '34', '3', 'Q3', 'tue', 'k.', 'tuesday', 'kedd', 'Y', 'N', '169', '18', '2', '4', '34', '31', '92', '21'] [] ['2014-08-27 00:00:00', '20140827', '201408', '2014', '8', '08', '27', '27', '', '5353', '3', '4', '239', '58', '4', '35', '3', 'Q3', 'wed', 'sze', 'wednesday', 'szerda', 'Y', 'N', '170', '19', '3', '4', '35', '31', '92', '21'] [] ['2014-08-28 00:00:00', '20140828', '201408', '2014', '8', '08', '28', '28', '', '5354', '4', '5', '240', '59', '4', '35', '3', 'Q3', 'thu', 'cs.', 'thursday', 'csütörtök', 'Y', 'N', '171', '20', '4', '4', '35', '31', '92', '21'] [] ['2014-08-29 00:00:00', '20140829', '201408', '2014', '8', '08', '29', '29', '', '5355', '5', '6', '241', '60', '5', '35', '3', 'Q3', 'fri', 'p.', 'friday', 'péntek', 'Y', 'Y', '172', '21', '5', '5', '35', '31', '92', '21'] [] ['2014-08-30 00:00:00', '20140830', '201408', '2014', '8', '08', '30', '30', 'SZOMBAT', '5356', '6', '7', '242', '61', '5', '35', '3', 'Q3', 'sat', 'szo', 'saturday', 'szombat', 'N', 'Y', '0', '0', '0', '5', '35', '31', '92', '21'] [] ['2014-08-31 00:00:00', '20140831', '201408', '2014', '8', '08', '31', '31', 'VASÁRNAP', '5357', '7', '1', '243', '62', '5', '35', '3', 'Q3', 'sun', 'v.', 'sunday', 'vasárnap', 'N', 'N', '0', '0', '0', '5', '35', '31', '92', '21'] [] ['2014-09-01 00:00:00', '20140901', '201409', '2014', '9', '09', '1', '01', '', '5358', '1', '2', '244', '63', '1', '35', '3', 'Q3', 'mon', 'h.', 'monday', 'hétfő', 'Y', 'N', '173', '1', '1', '1', '35', '30', '92', '22'] [] ['2014-09-02 00:00:00', '20140902', '201409', '2014', '9', '09', '2', '02', '', '5359', '2', '3', '245', '64', '1', '35', '3', 'Q3', 'tue', 'k.', 'tuesday', 'kedd', 'Y', 'N', '174', '2', '2', '1', '35', '30', '92', '22'] [] ['2014-09-03 00:00:00', '20140903', '201409', '2014', '9', '09', '3', '03', '', '5360', '3', '4', '246', '65', '1', '36', '3', 'Q3', 'wed', 'sze', 'wednesday', 'szerda', 'Y', 'N', '175', '3', '3', '1', '36', '30', '92', '22'] [] ['2014-09-04 00:00:00', '20140904', '201409', '2014', '9', '09', '4', '04', '', '5361', '4', '5', '247', '66', '1', '36', '3', 'Q3', 'thu', 'cs.', 'thursday', 'csütörtök', 'Y', 'N', '176', '4', '4', '1', '36', '30', '92', '22'] []
import cx_Oracle
import time
con = cx_Oracle.connect('H27091/2kl03KL@BIA.HU:5211/T10')
start = time.time()
cur = con.cursor()
cur.execute(" SELECT owner, table_name FROM all_tables where owner like 'H27091' and table_name like 'OPT_F%' ")
res = cur.fetchmany(numRows=3) ## Csak három rekord
## res = cur.fetchall()
##print (res)
columns = [i[0] for i in cur.description] ## Fejléc kikkérése
for column in columns:
print(column, end='|') ## Fejléc kiírása
print()
for row in res:
for field in row:
print(field, end='|' ) ## adat kiírása
print()
print(' ---------------------- ')
elapsed = (time.time() - start)
print (elapsed, " másodperc alatt futott le.")
cur.close()
con.close()
OWNER|TABLE_NAME| HN_EBH27091|OPT_F_BALANCE_SHEET| HN_EBH27091|OPT_F_BALANCE_SHEET_ROW| HN_EBH27091|OPT_F_LEADER| ---------------------- 0.42289042472839355 másodperc alatt futott le.
## Paraméteres lekérdezés
import cx_Oracle
con = cx_Oracle.connect('H27091/2kl03KL@BIA.HU:5211/T10')
start = time.time()
cur = con.cursor()
cur.prepare(r" SELECT owner, table_name, :mask as mask FROM all_tables where owner like 'H27091' and table_name like :mask ")
cur.execute(None, {'mask': r'OPT_F%'})
res = cur.fetchmany(numRows=3)
# res = cur.fetchall()
# print (res)
columns = [i[0] for i in cur.description] ## Fejléc kikkérése
for column in columns:
print(column, end='|') ## Fejléc kiírása
print()
for row in res:
for field in row:
print(field, end='|' ) ## adat kiírása
print()
print(' ----------------- ')
cur.execute(None, {'mask': r'OPT_D%'})
res = cur.fetchmany(numRows=3)
# res = cur.fetchall()
# print (res)
columns = [i[0] for i in cur.description] ## Fejléc kikkérése
for column in columns:
print(column, end='|') ## Fejléc kiírása
print()
for row in res:
for field in row:
print(field, end='|' ) ## adat kiírása
print()
print(' ----------------- ')
elapsed = (time.time() - start)
print (elapsed, " másodperc alatt futott le.")
cur.close()
con.close()
OWNER|TABLE_NAME|MASK| HN_EBH27091|OPT_F_BALANCE_SHEET|OPT_F%| HN_EBH27091|OPT_F_BALANCE_SHEET_ROW|OPT_F%| HN_EBH27091|OPT_F_LEADER|OPT_F%| ----------------- OWNER|TABLE_NAME|MASK| HN_EBH27091|OPT_D_TEAOR|OPT_D%| HN_EBH27091|OPT_D_GFO|OPT_D%| HN_EBH27091|OPT_D_KSH|OPT_D%| ----------------- 0.8238465785980225 másodperc alatt futott le.
## Oracle lekérdezés panda kezeléssel
import cx_Oracle
import os
import pandas as pd
def connectToOracle(url, username, password, mode=None):
if mode is not None:
connection = cx_Oracle.Connection (user=username, password=password, dsn=url, mode=mode)
else:
connection = cx_Oracle.Connection (user=username, password=password, dsn=url)
return connection
try:
c=cx_Oracle.Connection
stmt=" SELECT owner, table_name FROM all_tables where owner like 'H27091' and table_name like 'OPT_F%' "
c=connectToOracle("ora","neevem","jelsz",mode=None)
dataframe=pd.read_sql(stmt,con=c)
print("------\nDF Data tartalom : \n",dataframe)
print("------\nDF Data tipus : \n",dataframe.dtypes)
print("------\nDF szűrés : \n",dataframe[dataframe['TABLE_NAME']>='OPT_F_BANK'])
except cx_Oracle.DatabaseError as ex:
err, =ex.args
print("Error code = ",err.code)
print("Error Message = ",err.message)
# os._exit(1) ## Kilépés
else:
c.close()
------ DF Data tartalom : OWNER TABLE_NAME 0 HN_EBH27091 OPT_F_BALANCE_SHEET 1 HN_EBH27091 OPT_F_BALANCE_SHEET_ROW 2 HN_EBH27091 OPT_F_LEADER 3 HN_EBH27091 OPT_F_BALANCE_SHEET_ROW_TMP 4 HN_EBH27091 OPT_F_BANK 5 HN_EBH27091 OPT_F_BANK_ROW 6 HN_EBH27091 OPT_F_OWNER 7 HN_EBH27091 OPT_F_CP_PROPERTY 8 HN_EBH27091 OPT_F_RELATION 9 HN_EBH27091 OPT_F_NEG_EVENT 10 HN_EBH27091 OPT_F_GROUP_PROPERTY 11 HN_EBH27091 OPT_F_CP_EBH_PROPERTY ------ DF Data tipus : OWNER object TABLE_NAME object dtype: object ------ DF szűrés : OWNER TABLE_NAME 2 HN_EBH27091 OPT_F_LEADER 4 HN_EBH27091 OPT_F_BANK 5 HN_EBH27091 OPT_F_BANK_ROW 6 HN_EBH27091 OPT_F_OWNER 7 HN_EBH27091 OPT_F_CP_PROPERTY 8 HN_EBH27091 OPT_F_RELATION 9 HN_EBH27091 OPT_F_NEG_EVENT 10 HN_EBH27091 OPT_F_GROUP_PROPERTY 11 HN_EBH27091 OPT_F_CP_EBH_PROPERTY
# Munkalap cella érték kiolvasása
import openpyxl
workbook = openpyxl.load_workbook(r'c:\Users\kecskemetil\Documents\kl_sj\sj\python_oktatas\minta_py\csv_xls\cell_format.xlsx')
#people = workbook.get_sheet_by_name('People') ### Regi módszer
people = workbook['People']
print ("Első cella kiolvasása : ", people['A1'])
print ("További cellák olvasása : ", people.cell(row=3, column=2))
print ("Értékek :", people['B2'].value, people['C2'].value)
Első cella kiolvasása : <Cell 'People'.A1> További cellák olvasása : <Cell 'People'.B3> Értékek : Más22 Más32
import xlsxwriter
#Create a workbook and add a worksheet
workbook = xlsxwriter.Workbook('chart_line.xlsx')
worksheet = workbook.add_worksheet()
#Data to be plotted as a chart in worksheet
data = [10, 40, 50, 20, 10, 50]
#Add the data to the columns A1-A6 in worksheet
worksheet.write_column('A1', data)
#Create a line chart object using data from cells A1-A6
chart = workbook.add_chart({'type': 'line'})
chart.add_series({'values': '=Sheet1!$A$1:$A$6'})
#Insert chart in the worksheet
worksheet.insert_chart('C1', chart)
#Close workbook object
workbook.close()
## Munka könyvtár lekérdezése
import os
print(os.getcwd()) ## Munka könyvtár helyének kiíratása
os.chdir(r'c:\Users\kecskemetil\Documents\kl_sj\sj\python_oktatas\minta_py\csv_xls') ## Munka könyvtár beállítása
print(os.getcwd()) ## Munka könyvtár helyének kiíratása
C:\Users\kecskemetil\Documents\kl_sj\sj\python_oktatas\ipynb c:\Users\kecskemetil\Documents\kl_sj\sj\python_oktatas\minta_py\csv_xls
import xlsxwriter
import os
print(os.getcwd()) ## Munka könyvtár helyének kiíratása
#Create a workbook and add a worksheet
workbook = xlsxwriter.Workbook('Minta1.xlsx')
worksheet = workbook.add_worksheet()
#Some data that we want to write to the worksheet
expenses = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
#Start from the first cell. Rows and columns are zero indexed
row = 0
col = 0
#Iterate over the data and write it out row by row
for item, cost in (expenses):
worksheet.write(row, col, item)
worksheet.write(row, col + 1, cost)
row += 1
#Create a total cell using a formula
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')
workbook.close()
#Add a new sheet to the workbook
import xlsxwriter
workbook = xlsxwriter.Workbook('Minta2.xlsx')
worksheet = workbook.add_worksheet(name='Uj munkalap')
#Close workbook object
workbook.close()
c:\Users\kecskemetil\Documents\kl_sj\sj\python_oktatas\minta_py\csv_xls