Interface Python with mySQL

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>)
i.e;
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.

Interface Python with mySQL - Revision Notes