Python MySQL DB Tutorial

---

    MySQL is a Open Source Multi-User Relational Database Management System, Python program can Query, Update and Delete MySQL Database Objects. For this you need to have a MySQL connector module. Using MySQL Connector module, User can perform

• DML(Data Manipulation Language) Operations(INSERT,UPDATE,DELETE)
• DDL(Data Definition Language) Operations(CREATE TABLE,VIEW,TRIGGER,ALTER TABLE etc.,)
• DRL(Data Retreival Language) Operation(SELECT)

MySQL Data Types

    Every Database system suppots 4 different types of data, i.e Numeric,Text, Binary Data and Date Time data. So MySQL Database also supports all these core Data Types, apartment from that it supports Boolean, Bit,Enum Data Types

• Numeric Data Types
• Text Data Types
• Date,Time,DateTime Data Types
• Binary Data Types
• Bit Data Type
• Boolean Data Type
• Enum Data Type

Import MySQL DB Module

	      		import mysql.connector
	      

MySQl.connector not found , then install pip

      			$ python -m pip install mysql-connector
      		

Python MySQL Connection

      			import mysql.connector as mysql
      			
      			conn=mysql.connect(user="",password="",host="",port=3306,database="TestDB");

      			cur=conn.cursor(buffered=True)

      			cur.execute("SELECT * FROM t1");

      			print(cur.fetchall())
      		
      			cur.close();
      			conn.commit();
      			conn.close();
      		

Handling MySQL Exceptions in Python

         Exceptions raised by MySQL Database, can be handled in Python Program using try-except statements.Types of Errors and Exceptions located in mysql.connector.errors module

Types of Errors and Exceptions:

• DataError and Database Error
• OperationalError
• ProgrammingError
• IntegrityError
• InternalError

	
import mysql.connector as mysql
#conn=mysql.MySQLConnection();      		
try:	

 conn=mysql.connect(user="jhon",
 		    password="mysql12345",
 		    host="localhost",
 		    port=3306,
 		    database="classicmodels1");
 		    
except mysql.errors.ProgrammingError as e:
 print(e)
 exit()

cur=conn.cursor(buffered=True)

try:

  cur.execute("select customername,phone,country from customerss")

except mysql.errors.ProgrammingError as pe:
  print("error","-->",pe)

try:

  for name,ph,country in cur.fetchall():
    print(name,ph,country)

except mysql.errors.InterfaceError as ie:
  print(ie)