MySQL Programmer's Chapter-python

• Connection Database case:

• Import Mysql.connector

• CNX = Mysql.connector.connect (user= ' Scott ', password= ' Tiger ',

• Host= ' 127.0.0.1 ',

• database= ' employees ')

• Cnx.close ()

• Or

• From Mysql.connector Import (connection)

• CNX = connection. Mysqlconnection (user= ' Scott ', password= ' Tiger ',

• Host= ' 127.0.0.1 ',

• database= ' employees ')

• Exception handling

• Import Mysql.connector

• From Mysql.connector import errorcode

• 
  

• Try

• CNX = Mysql.connector.connect (user= ' Scott ',

• Database= ' Testt ')

• Except Mysql.connector.Error as err:

• if Err.errno = = ErrorCode. Er_access_denied_error:

• Print ("Something is wrong with your user name or password")

• elif Err.errno = = ErrorCode. Er_bad_db_error:

• Print ("Database does not exist")

• Else

• Print (ERR)

• Else

• Cnx.close ()

• Many parameters when

• Import Mysql.connector

• 
  

• Config = {

• ' User ': ' Scott ',

• ' Password ': ' Tiger ',

• ' Host ': ' 127.0.0.1 ',

• ' Database ': ' Employees ',

• ' Raise_on_warnings ': True,

• }

• 
  

• CNX = Mysql.connector.connect (**config)

• 
  

• Cnx.close ()

• Using an expanded connection method

• Import Mysql.connector

• 
  

• CNX = Mysql.connector.connect (user= ' Scott ', password= ' Tiger ',

• Host= ' 127.0.0.1 ',

• Database= ' Employees ',

• Use_pure=false)

• Cnx.close ()

• Or

• Import Mysql.connector

• 
  

• Config = {

• ' User ': ' Scott ',

• ' Password ': ' Tiger ',

• ' Host ': ' 127.0.0.1 ',

• ' Database ': ' Employees ',

• ' Raise_on_warnings ': True,

• ' Use_pure ': False,

• }

• 
  

• CNX = Mysql.connector.connect (**config)

• 
  

• Cnx.close ()

• Examples of use of DDL statements

• Creating databases and Tables

• From __future__ import print_function

• 
  

• Import Mysql.connector

• From Mysql.connector import errorcode

• 
  

• db_name = ' Employees '

• 
  

• TABLES = {}

• tables[' Employees ' = (

• "CREATE TABLE ' employees ' ("

• "' Emp_no ' int (one) not NULL auto_increment,"

• "' birth_date ' date not NULL,"

• "' first_name ' varchar () not NULL,"

• "' last_name ' varchar (+) not NULL,"

• "' Gender ' enum (' M ', ' F ') is not NULL,"

• "' hire_date ' date not NULL,"

• "PRIMARY KEY (' Emp_no ')"

• ") Engine=innodb")

• 
  

• tables[' Departments ' = (

• "CREATE TABLE ' departments ' ("

• "' Dept_no ' char (4) Not NULL,"

• "' dept_name ' varchar (+) not NULL,"

• "PRIMARY key (' Dept_no '), UNIQUE key ' dept_name ' (' Dept_name ')"

• ") Engine=innodb")

• 
  

• tables[' salaries ' = (

• "CREATE TABLE ' salaries ' ("

• "' Emp_no ' int (one) is not NULL,"

• "' Salary ' int (one) is not NULL,"

• "' From_date ' date not NULL,"

• "' to_date ' date not NULL,"

• "PRIMARY key (' Emp_no ', ' from_date '), key ' Emp_no ' (' Emp_no '),"

• "CONSTRAINT ' Salaries_ibfk_1 ' FOREIGN KEY (' Emp_no ')"

• "REFERENCES ' Employees ' (' Emp_no ') on DELETE CASCADE"

• ") Engine=innodb")

• 
  

• tables[' dept_emp ' = (

• "CREATE TABLE ' dept_emp ' ("

• "' Emp_no ' int (one) is not NULL,"

• "' Dept_no ' char (4) Not NULL,"

• "' From_date ' date not NULL,"

• "' to_date ' date not NULL,"

• "PRIMARY key (' Emp_no ', ' dept_no '), key ' Emp_no ' (' Emp_no '),"

• "KEY ' dept_no ' (' Dept_no '),"

• "CONSTRAINT ' Dept_emp_ibfk_1 ' FOREIGN KEY (' Emp_no ')"

• "REFERENCES ' Employees ' (' emp_no ') on the DELETE CASCADE,"

• "CONSTRAINT ' dept_emp_ibfk_2 ' FOREIGN KEY (' Dept_no ')"

• "REFERENCES ' departments ' (' Dept_no ') on DELETE CASCADE"

• ") Engine=innodb")

• 
  

• tables[' Dept_manager ' = (

• "CREATE TABLE ' Dept_manager ' ("

• "' Dept_no ' char (4) Not NULL,"

• "' Emp_no ' int (one) is not NULL,"

• "' From_date ' date not NULL,"

• "' to_date ' date not NULL,"

• "PRIMARY KEY (' emp_no ', ' dept_no '),"

• "KEY ' emp_no ' (' Emp_no '),"

• "KEY ' dept_no ' (' Dept_no '),"

• "CONSTRAINT ' Dept_manager_ibfk_1 ' FOREIGN KEY (' Emp_no ')"

• "REFERENCES ' Employees ' (' emp_no ') on the DELETE CASCADE,"

• "CONSTRAINT ' dept_manager_ibfk_2 ' FOREIGN KEY (' Dept_no ')"

• "REFERENCES ' departments ' (' Dept_no ') on DELETE CASCADE"

• ") Engine=innodb")

• 
  

• tables[' titles '] = (

• "CREATE TABLE ' titles ' ("

• "' Emp_no ' int (one) is not NULL,"

• "' title ' varchar (not NULL),"

• "' From_date ' date not NULL,"

• "' to_date ' date DEFAULT NULL,"

• "PRIMARY key (' Emp_no ', ' title ', ' From_date '), key ' Emp_no ' (' Emp_no '),"

• "CONSTRAINT ' Titles_ibfk_1 ' FOREIGN KEY (' Emp_no ')"

• "REFERENCES ' Employees ' (' Emp_no ') on DELETE CASCADE"

• ") Engine=innodb")

• 
  

• CNX = Mysql.connector.connect (user= ' Scott ')

• cursor = Cnx.cursor ()

• def create_database (cursor):

• Try

• Cursor.execute (

• "CREATE DATABASE {} DEFAULT CHARACTER SET ' UTF8 '". Format (db_name))

• Except Mysql.connector.Error as err:

• Print ("Failed Creating database: {}". Format (ERR))

• Exit (1)

• 
  

• Try

• Cnx.database = db_name

• Except Mysql.connector.Error as err:

• if Err.errno = = ErrorCode. Er_bad_db_error:

• Create_database (cursor)

• Cnx.database = db_name

• Else

• Print (ERR)

• Exit (1)

• 
  

• For name, DDL in Tables.iteritems ():

• Try

• Print ("Creating Table {}:". Format (name), end= ")

• Cursor.execute (DDL)

• Except Mysql.connector.Error as err:

• if Err.errno = = ErrorCode. Er_table_exists_error:

• Print ("already exists.")

• Else

• Print (ERR.MSG)

• Else

• Print ("OK")

• 
  

• Cursor.close ()

• Cnx.close ()

• Inserting data

• From __future__ import print_function

• From datetime import Date, datetime, Timedelta

• Import Mysql.connector

• 
  

• CNX = Mysql.connector.connect (user= ' Scott ', database= ' employees ')

• cursor = Cnx.cursor ()

• 
  

• Tomorrow = DateTime.Now (). Date () + Timedelta (Days=1)

• 
  

• Add_employee = ("INSERT into Employees"

• "(First_Name, last_name, hire_date, Gender, birth_date)"

• "VALUES (%s,%s,%s,%s,%s)")

• Add_salary = ("INSERT into salaries"

• "(Emp_no, Salary, from_date, to_date)"

• "VALUES (% (emp_no) s,% (salary) s,% (from_date) s,% (to_date) s)")

• 
  

• Data_employee = (' Geert ', ' Vanderkelen ', Tomorrow, ' M ', Date (1977, 6, 14))

• 
  

• # Insert New Employee

• Cursor.execute (Add_employee, Data_employee)

• Emp_no = Cursor.lastrowid

• 
  

• # Insert Salary Information

• Data_salary = {

• ' Emp_no ': emp_no,

• ' Salary ': 50000,

• ' From_date ': Tomorrow,

• ' To_date ': Date (9999, 1, 1),

• }

• Cursor.execute (Add_salary, Data_salary)

• 
  

• # Make sure data are committed to the database

• Cnx.commit ()

• 
  

• Cursor.close ()

• Cnx.close ()

• Querying data

• Import datetime

• Import mysql.connector

• 
  

• CNX = mysq L.connector.connect (user= ' Scott ', database= ' employees ')

• cursor = cnx.cursor ()

• 
  /p>

• Query = ("Select First_Name, last_name, hire_date from Employees"

•     &NBSP ;     "WHERE hire_date between%s and%s")

• 
  

• Hire_start = Datetime.date (1999 , 1, 1)

• Hire_end = Datetime.date (1999, +)

• 
  

• Cursor.execute (q Uery, (Hire_start, hire_end))

• 
  

• For (first_name, last_name, hire_date) in cursor:

•   print ("{}, {} is hired on {:%d%b%Y}". Format (

•     last_name, First_n Ame, hire_date))

• 
  

• Cursor.close ()

• Cnx.close ()

• Query case

• Import Mysql.connector

• From Mysql.connector import errorcode

• 
  

• Config = {

• ' User ': ' Test ',

• ' Password ': ' Test ',

• ' Host ': ' 127.0.0.1 ',

• ' Port ': ' 3306 ',

• ' Database ': ' Test ',

• }

• 
  

• Try

• con = mysql.connector.connect (**config)

• Except Mysql.connector.Error as err:

• if Err.errno = = ErrorCode. Er_access_denied_error:

• Print "Sometine is wrong with your user name or password"

• elif Err.errno = = ErrorCode. Er_bad_db_error:

• Print "Database does not exist"

• Else

• Print (ERR)

• 
  

• cursor = Con.cursor ()

• 
  

• Cursor.execute ("SELECT * from students")

• 
  

• For (no, name, age, Sex) in cursor:

• Print "{} {} {} {}". Format (No, name, age, Sex)

MySQL Programmer's Chapter-python