Database table: Customer table with following columns.
database.py : Create database connection and close connection.
db_config.ini: database details
db_operation.py : All database operation - Insert, Update, Select
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
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
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.
ReplyDeletePython 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
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeletegiá vé máy bay đi Mỹ khứ hồi
giá vé máy bay mỹ về việt nam
vé máy bay khứ hồi từ đức về việt nam
mua vé máy bay từ nga về việt nam
ve may bay tu anh ve viet nam
ve may bay tu phap ve viet nam
các khách sạn cách ly ở quảng ninh