Parsing Python Dictionary into Mysql Database

Tutorial kali ini akan melakukan parsing dari data dictionary python kedalam mysql database. pertama instalasi dan konfigurasi mysql server :

$ sudo apt install mysql-server

nano /etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address           = 127.0.0.1
bind-address            = 0.0.0.0

$ mysql -u root -p
CREATE USER 'vyosuser'@'%' IDENTIFIED BY 'vyospassword';
CREATE DATABASE vyos;
USE vyos;
CREATE TABLE User (
    name varchar(255)	NOT NULL,
    ipaddress varchar(255) NOT NULL,
    user varchar(255) 	NOT NULL,
    password varchar(255)	NOT NULL

);
GRANT ALL PRIVILEGES ON vyos.* TO 'vyosuser'@'%';
FLUSH PRIVILEGES;

root@ubuntu:~# systemctl restart mysql
root@ubuntu:~# systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: active (running) since Fri 2018-07-13 13:02:43 EDT; 7s ago
  Process: 3229 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=ex
  Process: 3217 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exit
 Main PID: 3228 (mysqld)
    Tasks: 28
   Memory: 134.0M
      CPU: 297ms
   CGroup: /system.slice/mysql.service
           └─3228 /usr/sbin/mysqld

Jul 13 13:02:42 ubuntu systemd[1]: Stopped MySQL Community Server.
Jul 13 13:02:42 ubuntu systemd[1]: Starting MySQL Community Server...
Jul 13 13:02:43 ubuntu systemd[1]: Started MySQL Community Server.

siapkan data yang akan diparsing :

{
    "host": [{
            "ipaddress": "192.168.1.1",
            "user": "vyos1",
            "password": "vyos1"
        },
        {
            "ipaddress": "192.168.1.2",
            "user": "vyos2",
            "password": "vyos2"
        }
    ]
}

instalasi module mysql di server otomasi :

sudo apt install libmysqlclient-dev
sudo pip install MySQL-python

buat program :

# Import mysql python module
import ast
import MySQLdb

# import data from inventory
# open json and read into raw text
raw = open('inventory.json').read()

# loads the raw into loads() function to create dict string type
data = ast.literal_eval(raw)

# connect to mysql database
conn = MySQLdb.connect(host= "192.168.122.60",user="vyosuser",passwd="vyospassword",db="vyos")

# setup cursor
cursor=conn.cursor()

# use looping & append data
for host in data["host"].iteritems():
    cursor.execute("""INSERT INTO User (name, ipaddress, user, password) VALUES ('%s','%s','%s','%s')"""%(host[0],host[1]["ipaddress"],host[1]["user"],host[1]["password"]))
    conn.commit()

# close connection
conn.close()

jalankan program tersebut, lalu coba cek di database :

mysql> SELECT * FROM User;
Empty set (0.00 sec)

mysql> SELECT * FROM User;
+--------+-------------+-------+----------+
| name   | ipaddress   | user  | password |
+--------+-------------+-------+----------+
| Spine1 | 192.168.1.1 | vyos1 | vyos1    |
| Spine2 | 192.168.2.1 | vyos2 | vyos2    |
+--------+-------------+-------+----------+
2 rows in set (0.00 sec)

mysql>

 

Comments are closed.