Interface Python with mySQL - Revision Notes

 CBSE Class 12 Computer Science (Python)

Interface Python with mySQL
Revision Notes

  • Some important points are as follows:
  • To connect to a database from within a programming application, you need a framework that facilitates communication between two different genres of software (programming application and DBMS).
  • To connect from Python to MYSQL, you need a library called MySQL connector.
  • You must import mysql.connector in the Python program/script before writing code of connectivity.
  • Steps to create a database connectivity Python application are:
    Step 1. Start Python: start Python's editor where you can create your python scripts, i.e; IDE or IDLE.
    Step 2. Import the package required for database programming.
    Here you need to import mysql.connector package in your python scripts.
    Step 3. Open a connection: Here we need to establish a connection to MYSQL database using connect(). This requires 4 parameters, the syntax for this is as follows:
    <Connection_object>  = mysql.connector.connect(host= <host_name>, user=<username>, passwd =<password> , [database = <database>])
    where user is the user name
    password is the password of the user
    host_name is IP address.
    database is the database name of MYSQL
    for example:
    ##  import mysql.connector as sqltor
          mycon = sqltor.connect(host ="localhost" , user ="root", passwd = "mypass", database ="test")
    Step 4. Create a cursor instance.
    Here we have to create an instance of cursor by using cursor(), the syntax for the following is as follows:
    <cursorobject> =<connectionobject>.cursor()
    i.e; in the above connection we can create cursor() by writing:
     cursor = mycon.cursor()
  • Step 5. Execute a query: here we use the execute() with following syntax.
    < cursorobject>.execute(<sql query string>)
    cursor.execute("select*from data)
    Step 6. Extract data from result set. Here you can fetch the data from the resultset by using fetch( ) functions. [fetchall(), fetchmany(<n>), fetchone()] 
    Step 7. Clean up the environment.
  • A database Connection object controls the connection to the database. It represents a unique session with a database connected from within a script/program.
  • A Database Cursor is a special control structure that facilitates the row by row processing of records in the resultset, i.e., the set of records retrieved as per query.
  • The resultset refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.
  • You can use connect() method for establishing database connection, cursor() to create a cursor and execute() to execute an SQL query.
  • For INSERT, UPDATE and DELETE queries, you must run commit() with the connection object.