IT Learning


Bulk insert to Oracle with Python

2020/07/04   -Oracle, Python

Overview There is an example code to insert data in bulk to Oracle database with python cx_Oracle Environments python 3.7.3Oracle 18c Express Edition Step1 : Creating the table Creating the table ‘oracle_insert’ in the schema ‘USER01’. CREATE TABLE USER01.ORACLE_INSERT( col1 int ,col2 int ,col3 int ) Step2 : Insert in bulk with executemany dataset is the dataset to be insertedMaking a connection to database with cx_OracleInserting in bulk with using cur.executemany() import cx_Oracle dataset =[ [1,2,3] ,[4,5,6] ,[7,8,9] ,[10,11,12] ,[13,14,15] ,[16,17,18] ,[19,20,21] ] HOST = ‘localhost’ PORT = ‘1521’ DB_NAME = ‘xepd1’ USER = ‘user01’ PASSWORD = ‘password01’ SERVICE_NAME = …

【Python】Transforming datetime to date and time with pandas dateframe

2020/07/01   -Python

Original Data This code is to make sample dataframe. import pandas as pd import datetime as dt df = pd.DataFrame([dt.datetime(2020,6,1,0,0,0),dt.datetime(2020,6,2,10,0,0),dt.datetime(2020,6,3,15,0,0)],columns=[’datetimes’])     datetimes 0 2020-06-01 00:00:00 1 2020-06-02 10:00:00 2 2020-06-03 15:00:00 Transforming dataframe to date and time. It can realize to use lambda & apply functions like following. df[’dates’] = df[’datetimes’].apply(lambda x :,x.month, df[’times’] = df[’datetimes’].apply(lambda x : dt.time(x.hour,x.minute,x.second))      datetimes      dates   times 0 2020-06-01 00:00:00 2020-06-01 00:00:00 1 2020-06-02 10:00:00 2020-06-02 10:00:00 2 2020-06-03 15:00:00 2020-06-03 15:00:00 If you want to get with string, these code is better. df[’dates’] = df[’datetimes’].apply(lambda x : x.strftime(‘%Y/%m/%d’)) df[’times’] = df[’datetimes’].apply(lambda x …

Python : Insert dataframe data into MySQL table 

2020/05/31   -Python

Overview Dataframe type in python is so useful to data processing and it’s possible to insert data as dataframe into MySQL . There is a sample of that. Environments Python 3.7.3MySQL 5.5.62 Step1 : Making the table Defining a table like the following. > CREATE DATABASE testdb; > CREATE TABLE testdb.mysql_table( col1 int ,col2 int ,col3 int ); Step2 : Making data Making data for insert from python. the data should be the same type as the table you will insert it. The column name of dataframe is also same as the table if they are different you will get …

Connection to PostgreSQL, Oracle&MySQL from Python

2020/05/21   -Python

Overview There are some samples to connect PostgreSQL, Oracle, MySQL from Python. How to connect PostgreSQL Package installation pip install psycopg2 Example import psycopg2 HOST = ‘your_host’ PORT = ‘5432’ DB_NAME = ‘your_db_name’ USER = ‘your_user_name’ PASSWORD = ‘your_password’ conn = psycopg2.connect("host=" + HOST + " port=" + PORT + " dbname=" + DB_NAME + " user=" + USER + " password=" + PASSWORD ) cur = conn.cursor() cur.execute("select version()") rows = cur.fetchall() cur.close() conn.close() print(rows) Connect to MySQL Package installation pip install mysqlclient Example import MySQLdb HOST = ‘your_host’ PORT = 3306 #Not str but int DB_NAME = ‘your_db_name’ …

Installation of MySql on ReadyNAS

2020/05/14   -ReadyNAS

Overview MySQL plugin for ReadyNAS is available if your NETGEATR NAS device is connected to online. The following shows how to install MySQL plugin and enable it. Environment ReadyNAS 6.10.3MySQL Server1.0.1 (plugin) Step1 : Install MySQL plugin At first let’s open ReadyNAS service from web browser. This page is refence to access it. NETGEAR ReadyNASの設定 After opening ReadyNAS home, select a Available Apps tab. When you input ‘mysql’ into the search box at left side , you can find MySQL server pugin and click install. Completed installation, then MySql Server shows at Installed Apps list. You may find ‘Launch’ button …

【Oracle】How to fix ORA-65096 at creating user

2020/05/09   -Oracle

Ovewivew I encountered ORA-65096 when I tried to create user at Oracle 18c Express Edition. This is a example to fix ORA-65096. Environments OS : Windows10Oracle : 18c Express Edition Error SQL> CREATE USER user01 identified by password01 default tablespace USERS; ORA-65096: ORA-65096: invalid common user or role name Step1 : Checking database Oracle has two databases ,’CDB’ and ‘PDB’. It seems it’s not possible to create local user on CDB, so we have to check which database are used. SQL> show con_name; CON_NAME —————————— CDB$ROOT If you find CDB lie the above, It’s good to try change it to …

Rundeck installation on CentOS7

2020/05/09   -Rundeck

Overview Rundeck is functional Open Source Software for job management. It is more useful than cron because it can realize workflow, scheduling and so on. Following shows how to install Rundeck on CenOS and access it from a web browser. Rundeck Environment OS : CentOS7JDK : Openjdk8Rundeck : 3.0.13 Step1 : Installation of JDK At first you have to install JRE because Rundeck requires java runtime environment. This is a example to install JDK for it. #Installation of jdk $ sudo yum -y install java-1.8.0-openjdk java-1.8.0-openjdk-devel #Adding JAVA_HOME path in .bash_profile $vim .bash_profile export JAVA_HOME=/lib/jvm/java-1.8.0-openjdk- export PATH=$PATH:$JAVA_HOME/bin export PATH=$PATH:$JAVA_HOME/jre/bin …

【Python】Changing images periodically with tkinter

2020/05/03   -Python

Overview There is a good library in python to make GUI, that is tkinter. It can display images too. Here, we are trying to change images periodically with tkinter. Python library to be used Following three libraries are used. tkinter ‘tkinter’ is used to make GUI. PIL(pillow) ‘PIL’ is used to deal with images in the python. threading ‘threading’ is used to change images automatically. Detail about that will be explained later. By the way, if you are using anaconda which contains these three libraries already , you don’t have to install them with pip command. Step1 : Display a …

Cleaning up Rundeck database

2020/05/03   -Rundeck

Overivew As a result of mistake of crontab setting for a job, Over 200 thousands of activity log was created. Somehow it’s not possible to delete project. Maybe it has too much activity and it takes too much time more than accepted. In the following , There are procedure to delete whole MySQL database for rundeck and clean all jobs and activities so on. Environments OS : CentOS7MySQL:Ver. 15.1Rundeck:Ver. 3.0.13 Step1 : Backup of jobs If you drop the MySQL database for rundeck all jobs are removed. So you have to export the important jobs for you beforehand.The procedure is …

Anaconda installation on CentOS

2020/04/29   -Python

Overview Anaconda package is very useful tool for data analysis by python language. This article shows how to install it on CentOS. Environments OS:CentOS7Anaconda3:3-5.3.1 What is Anaconda Nowadays the usage of python language is being more popular than before for the purpose of machine learning or processing data. Anaconda package is consists of not only python engine but also IDE, web-base editor or some useful tools. When you just install anaconda, you can use these bundled useful tools. CentOS7 has python 2 as a default but in this example we use Anaconda with python3. Step1 : pyenvのインストール At first let’s …