Aug 17, 2018

Textual description of firstImageUrl

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

Location: Bengaluru, Karnataka, India

1 comment:

  1. I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
    Python Projects for Students

    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

    Project Center in Chennai

    ReplyDelete