Page 2 :
Installing mysql connector, Packages are available at the Connector/Python download site. For some packaging formats, there are different packages for different versions of Python; choose the one appropriate for the version of Python installed on your system., Installing Connector/Python with pip, This is the recommended way to install Connector/Python., Make sure you have a recent pip version installed on your system. If your system already has pip installed, you might need to update it. Or you can use the standalone pip installer.
Page 3 :
Install MySQL Driver, Python needs a MySQL driver to access the MySQL database., In this tutorial we will use the driver "MySQL Connector"., We recommend that you use PIP to install "MySQL Connector"., PIP is most likely already installed in your Python environment., Navigate your command line to the location of PIP, and type the following:
Page 4 :
Windows OS, Download and install "MySQL Connector":, C:\Users\YourName\AppData\Local\Programs\Python\Python36-32\Scripts>python -m pip install mysql-connector-python, , Now you have downloaded and installed a MySQL driver., , Installing Connector/Python on Microsoft Windows Using an MSI Package, To use the MSI Installer, launch it and follow the prompts in the screens it presents to install Connector/Python in the location of your choosing.
Page 5 :
Installing Connector/Python on Linux Using the MySQL Yum Repository, Administrative privileges on a Linux system can be obtained legitimately in one of the following three ways:, Log into the system as the root user (not recommended), Switch user to the root user using su, Use sudo to execute a single command as the root user, You must have the MySQL Yum repository on your system’s repository list. To make sure that your Yum repository is up-to-date, use this command:, shell> sudo yum update mysql-community-release, Then install Connector/Python as follows:, shell> sudo yum install mysql-connector-python
Page 6 :
Accessing connector module, The name of the project MySQL for Python is the current version of a project that began under the rubric MySQLdb. Consequently, unlike most Python modules, the, MySQL for Python module is not called by its name, but by its historic handle. To import the module, insert the following into a Python program or simply type it in a, following Python shell:, import MySQLdb, To make working with the module easier, you can also import it with an alias:, import MySQLdb as mysql, This allows us to use mysql instead of MySQLdb when we access parts of the module.
Page 7 :
Using connect, cursor, execute & close functions, Database Connection, The steps to connect the python application to the database. There are the following steps to connect a python application to our database., Import mysql.connector module, Create the connection object., Create the cursor object, Execute the query, Close the query
Page 8 :
1.Creating the connection, To create a connection between the MySQL database and the python application, the connect() method of mysql.connector module is used., Pass the database details like HostName, username, and the database password in the method call. The method returns the connection object.
Page 9 :
Arguments required to connect MySQL from Python, You need to know the following detail of the MySQL server to perform the connection from Python., Username: The username that you use to work with MySQL Server. The default username for the MySQL database is a root., Password: Password is given by the user at the time of installing the MySQL server. If you are using root then you won’t need the password., Host name: The server name or Ip address on which MySQL is running. if you are running on localhost, then you can use localhost or its IP 127.0.0.0, Database name: The name of the database to which you want to connect and perform the operations.
Page 11 :
2.Creating a cursor object, The cursor object can be defined as an abstraction specified in the Python DB-API 2.0. It facilitates us to have multiple separate working environments through the same connection to the database. We can create the cursor object by calling the 'cursor' function of the connection object. The cursor object is an important aspect of executing queries to the databases.
Page 13 :
3.Execute queries using execute() Method, , The cursor.execute() execute SQL query and returns the result., Syntax:, Cursor.execute(query [,args]), This method executes a SQL query against the database. This is a DB API compliant call. Parameters are substituted using question marks, e.g. "SELECT name FROM table WHERE id=?". The parameter args is a tuple. It returns None on success or raises an exception in the case of an error., Arguments, query: The select statement to be executed, args: A tuple of one or more parameters to be inserted wherever a question mark appears in the query string.
Page 14 :
4. close() :close the cursor and db connection, , Close cursor and connection objects use cursor.close() and connection.close() method to close open connections after your work completes, Syntax:, cursor.close()
Page 15 :
Steps to connect, Connect to MySQL database from Python, Install MySQL connector Python moduleUse the pip command to install MySQL connector Python. pip install mysql-connector-python, Import MySQL connector moduleImport using import mysql.connector, Use the connect() methodUse the connect() method of the MySQL Connector module with the required parameters to connect MySQL. It returns a MySQLConnection object, Use the cursor() methodUse the cursor() method of a MySQLConnection object to create a cursor object to perform various SQL operations., Use execute() methodThe cursor.execute() execute SQL query and returns the result., Extract result using fetchall()Use cursor.fetchall() or fetchone() to read query result., Close cursor and connection objectsuse cursor.clsoe() and connection.clsoe() method to close open connections after your work completes
Page 17 :
Reading single & multiple results of query execution, READ Operation on any database means to fetch some useful information from the database., Once our database connection is established, you are ready to make a query into this database. You can use either fetchone() method to fetch single record or fetchall() method to fetch multiple values from a database table.
Page 18 :
Reading single & multiple results of query execution, fetchone(), This method returns one record as a tuple, If there are no more records then it returns None, Syntax: cursor. fetchone(), , fetchmany(number_of_records), This method accepts number of records to fetch and returns tuple where each records itself is a tuple. If there are not more records then it returns an empty tuple., Syntax: cursor. fetchmany(2), fetchall() , fetches all the rows of a query result. It returns all the rows as a list of tuples. An empty list is returned if there is no record to fetch., Syntax: cursor. fetchall()
Page 19 :
import mysql.connector, from mysql.connector import Error, , try:, connection = mysql.connector.connect(host='localhost',, database='electronics',, user='pynative',, password='pynative@#29'), , mySql_select_Query = "select * from laptop", cursor = connection.cursor(buffered=True), cursor.execute(mySql_select_Query), record = cursor.fetchone(), print(record), , except mysql.connector.Error as error:, print("Error while connecting to MySQL", error), finally:, if (connection.is_connected()):, cursor.close(), connection.close(), print("MySQL connection is closed")
Page 21 :
Executing different types of statements, 1.Creating the table, In this section of the tutorial, we will create the new table Employee. We have to mention the database name while establishing the connection object., We can create the new table by using the CREATE TABLE statement of SQL. In our database PythonDB, the table Employee will have the four columns, i.e., name, id, salary, and department_id initially., > create table Employee, (name varchar(20) not null, , id int primary key, , salary float not null, , Dept_Id int not null)
Page 22 :
Example, import mysql.connector , , #Create the connection object , myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") , , #creating the cursor object , cur = myconn.cursor() , , try: , #Creating a table with name Employee having four columns i.e., name, id, salary, and department id , dbs = cur.execute("create table Employee(name varchar(20) not null, id int(20) not null primary key, salary float not null, Dept_id int not null)") , except: , myconn.rollback() , , myconn.close()
Page 23 :
2. Insert Operation, Adding a record to the table, The INSERT INTO statement is used to add a record to the table. In python, we can mention the format specifier (%s) in place of values., We provide the actual values in the form of tuple in the execute() method of the cursor.
Page 24 :
import mysql.connector , #Create the connection object , myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") , #creating the cursor object , cur = myconn.cursor() , sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)" , , #The row values are provided in the form of tuple , val = ("John", 110, 25000.00, 201, "Newyork") , , try: , #inserting the values into the table , cur.execute(sql,val) , , #commit the transaction , myconn.commit() , , except: , myconn.rollback() , , print(cur.rowcount,"record inserted!") , myconn.close()
Page 25 :
3.Read Operation, The SELECT statement is used to read the values from the databases. We can restrict the output of a select query by using various clause in SQL like where, limit, etc., Python provides the fetchall() method returns the data stored inside the table in the form of rows. We can iterate the result to get the individual rows.
Page 26 :
import mysql.connector , , #Create the connection object , myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") , , #creating the cursor object , cur = myconn.cursor() , , try: , #Reading the Employee data , cur.execute("select * from Employee") , , #fetching the rows from the cursor object , result = cur.fetchall() , #printing the result , , for x in result: , print(x); , except: , myconn.rollback() , , myconn.close()
Page 27 :
Output:, ('John', 101, 25000.0, 201, 'Newyork'), ('John', 102, 25000.0, 201, 'Newyork') , ('David', 103, 25000.0, 202, 'Port of spain') ('Nick', 104, 90000.0, 201, 'Newyork'), ('Mike', 105, 28000.0, 202, 'Guyana')
Page 28 :
4.Update Operation, The UPDATE-SET statement is used to update any column inside the table. The following SQL query is used to update a column., > update Employee set name = 'alex' where id = 110
Page 30 :
5.Delete Operation, The DELETE FROM statement is used to delete a specific record from the table. Here, we must impose a condition using WHERE clause otherwise all the records from the table will be removed., The following SQL query is used to delete the employee detail whose id is 110 from the table., > delete from Employee where id = 110
Page 31 :
Executing transactions, Transactions ensure the data consistency of the database. We have to make sure that more than one applications must not modify the records while performing the database operations. The transactions have the following properties., Atomicity Either the transaction completes, or nothing happens. If a transaction contains 4 queries then all these queries must be executed, or none of them must be executed., Consistency The database must be consistent before the transaction starts and the database must also be consistent after the transaction is completed., Isolation Intermediate results of a transaction are not visible outside the current transaction., Durability Once a transaction was committed, the effects are persistent, even after a system failure.
Page 32 :
1. Python commit() method, Python provides the commit() method which ensures the changes made to the database consistently take place., The syntax to use the commit() method is given below., conn.commit() #conn is the connection object , All the operations that modify the records of the database do not take place until the commit() is called.
Page 33 :
2.Python rollback() method, The rollback() method is used to revert the changes that are done to the database. This method is useful in the sense that, if some error occurs during the database operations, we can rollback that transaction to maintain the database consistency., The syntax to use the rollback() is given below., conn.rollback() , , 3.Closing the connection, We need to close the database connection once we have done all the operations regarding the database. Python provides the close() method. The syntax to use the close() method is given below., conn.close()
Page 34 :
In the following example, we are deleting all the employees who are working for the CS department., , Example, import mysql.connector , , #Create the connection object , myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB") , , #creating the cursor object , cur = myconn.cursor() , , try: , cur.execute("delete from Employee where Dept_id = 201") , myconn.commit() , print("Deleted !") , except: , print("Can't delete !") , myconn.rollback() , , myconn.close()
