Python and MariaDB: How to access Maria Database with Python

Database table: Customer table with following columns.

database.py
:  Create database connection and close connection.
# -*- coding: utf-8 -*-
"""
Created on Tue Jul 24 09:40:55 2018

@author: n0r0082
"""

from mysql.connector import MySQLConnection, Error
import six
import os
#To sypport pyhton 2 and 3 
try:
    from configparser import ConfigParser
except:
    import ConfigParser

def read_db_config(filename='db_config.ini', section='maria_local'):
   #Read database configuration file and return a dictionary object
    # create parser and read ini configuration file 
    if six.PY2: #pyhton 2
        parser = ConfigParser.ConfigParser()
        curr_dir_path = os.path.dirname(os.path.realpath(__file__))
        config_full_path = os.path.join(curr_dir_path, filename)
        parser.read(config_full_path)
    else: #pyhton 3
        parser = ConfigParser()
        parser.read(filename)
    
    # get section, default to mysql
    db = {}
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
    else:
        raise Exception('{0} not found in the {1} file'.format(section, filename))
 
    return db

def get_connetion(db_config):
    try:
        #conn = MySQLdb.connect(host=migration_const.DB_HOST_NAME,user=migration_const.DB_USER_NAME,passwd=migration_const.DB_USER_PASSWORD)
        conn = MySQLConnection(**db_config)
        if conn.is_connected():
            print('Connected to MySQL database ' + db_config['host'])
        return conn
    except Error as e:
        print("Exception occured while creating database connection with host" + db_config['host'] +" and port#" + db_config['port'] )
        print(e)
        #log.exception('Error from throws():')
        raise
    finally:
        pass
        #conn.close()

def close_connetion(connection):
    try:
        connection.close()
    except Exception:
        print("Error occured while closing connection!!")

db_config.ini
: database details
[maria_local]
host = localhost
database = DIPC
user = root
password =root
port=3306

[maria_qa]


[maria_stg]

db_operation.py : All database operation - Insert, Update, Select
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Sat Aug 11 13:29:17 2018

@author: n0r0082
"""
import migration_const
import database 
from contextlib import closing

def get_connection(db = 'maria_local'):
    #Get database config 
    db_config = database.read_db_config(migration_const.DB_CONFIG_FILE_NAME,db)
    #Get Cconnection object 
    conn = database.get_connetion(db_config)
    return conn

def update_entity():
    phone = '041-237645' 
    email = 'rao@gmail.com'
    conn = get_connection()
    #SQl Query prep 
    sql_update_customer = "UPDATE Customer SET phone= {} where email = {}"
    email = "'{}'".format(email)
    phone = "'{}'".format(phone)
    sql_update_customer = sql_update_customer.format(phone, email)
    print(sql_update_customer)
    try:
        with closing(conn.cursor()) as cur:
           # Execute the SQL command
           cur.execute(sql_update_customer)
           conn.commit()
    except Exception as e:
            print(e)
    database.close_connetion(conn)
    
def select_entity():
    conn = get_connection()
    email = 'nikhilranjan235@gmail.com'
    #SQl Query prep 
    sql_select_employee = "select * from Customer where email = {}"
    email = "'{}'".format(email)
    sql_select_employee = sql_select_employee.format(email)
    print(sql_select_employee)
    try:
        with closing(conn.cursor()) as cur:
           # Execute the SQL command
           cur.execute(sql_select_employee)
           # Fetch all the rows in a list of lists.
           results = cur.fetchall()
           if len(results) > 0:
              row = results[0]
              name = row[1]
              phone = row[2]
              print("Name: " + str(name) + " and Phone: "+ str(phone))
    except Exception as e:
            print(e)
    database.close_connetion(conn)
    
def insert_entity():
    conn = get_connection()
    name = 'Kumkum'
    email= 'kumkum23@devinline.com'
    phone = '8769814567'
    sql_insert_customer = "INSERT INTO Customer (NAME , EMAIL, PHONE) values ({},{},{})"
    email = "'{}'".format(email)
    name = "'{}'".format(name)
    phone = "'{}'".format(phone)
    sql_insert_customer = sql_insert_customer.format(name,email,phone)
    print(sql_insert_customer)
    try:
        with closing(conn.cursor()) as cur:
           # Execute the SQL command
           cur.execute(sql_insert_customer)
           conn.commit()
    except Exception as e:
            print(e)
    database.close_connetion(conn)

def na():
    print("Invalid choice")
    
switcher = {
        1: select_entity,
        2: insert_entity,
        3: update_entity
    }

def execute_db_operation(argument):
    # Get the function from switcher dictionary
    func = switcher.get(argument, "na")
    # Execute the function
    return func()

def db_operations():
    x = 1
    while True:
        print( "Operation (1: select, 2: insert, 3: uupdate)")
        data = input("Enter a number 1-3 : ")
        execute_db_operation(data)
        x += 1
        
#starts here.....    
if __name__=="__main__":
    print ("************** Starting DB Operation ****************")
    db_operations()
    print ("************** Finished DB Operation ****************")

Sample output:
➜  MariaDBPython git:(master) ✗ python db_operation.py
************** Starting DB Operation ****************
Operation (1: select, 2: insert, 3: uupdate)
Enter a number 1-3 : 1
Connected to MySQL database localhost
select * from Customer where email = 'nikhilranjan235@gmail.com'
Name: Nikhil and Phone: nikhilranjan235@gmail.com
Operation (1: select, 2: insert, 3: uupdate)
Enter a number 1-3 : 2
Connected to MySQL database localhost
INSERT INTO Customer (NAME , EMAIL, PHONE) values ('Kumkum','kumkum23@devinline.com','8769814567')
Operation (1: select, 2: insert, 3: uupdate)
Enter a number 1-3 : 3
Connected to MySQL database localhost
UPDATE Customer SET phone= '041-237645' where email = 'rao@gmail.com'
Operation (1: select, 2: insert, 3: uupdate)
Enter a number 1-3 :

Git Source: https://github.com/zytham/Python/tree/master/MariaDBPython

1 Comments

Previous Post Next Post