Python can be used to create database application in MySQL. To access MySQL database with Python you need to install MySQL driver.
Python comes with an inbuilt package management system, pip. Pip can install, update, or delete any official package. You can install MySQL Connector via the command line (cmd) by entering:
python -m pip install mysql-connector-python
To test if the installation was successful, type the following command on shell:
>>> import mysql.connector
To interact with a MySQL server first you need to establish a connection. To do this, you need to call connect() constructor that creates a connection to the MySQL server and returns a MySQLConnection object. The following coding examples illustrate this:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword"
)
A database cursor is a mechanism that enables retrieval, insertion and removal of database records. Cursors are used to process rows / result set returned by SQL queries.
The MySQLCursor class instantiates objects that can execute operations such as SQL statements. Cursor objects interact with the MySQL server using a MySQLConnection object.
To create a cursor, use the cursor() method of a connection object:
mycursor = mydb.cursor()
This method executes the given database query. Tuple or dictionary is passed as parameter.
This method retrieves all the rows in the result set of a query and returns them as list of tuples.
This method retrieves first row in the result set of a query and returns them as tuple.
Returns the number of rows in case of SELECT, DELETE and UPDATE operations.
Following examples illustrate how to develop Python applications which connect to MySQL Server using MySQL Connector/Python. First create a database 'school' and a table 'student' having following structure.
Field | Type | Null | Key |
---|---|---|---|
rno | int | NO | PRI |
name | char(30) | YES | |
fees | int | YES | |
class | char(3) | YES |
mysql> create database school;
Query OK, 1 row affected (0.13 sec)
mysql> use school;
Database changed
mysql> create table student
-> (
-> rno int primary key,
-> name char(30),
-> fees int,
-> class char(3)
-> );
Query OK, 0 rows affected (0.23 sec)
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = mydb.cursor()
sql = "INSERT INTO student(rno, name, fees, class) VALUES (%s, %s, %s, %s)"
val = ("2", "Ananya","7800","K12")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
mycursor.close()
mydb.close()
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = mydb.cursor()
sql = "DELETE FROM student WHERE rno = %s"
val = ("1",)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record deleted.")
mycursor.close()
mydb.close()
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = mydb.cursor()
sql = "UPDATE student SET class = %s WHERE rno = %s"
val = ("11","1")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record updated.")
mycursor.close()
mydb.close()
Select all records from the "student" table, and display the result:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = mydb.cursor()
sql = "select * from student"
mycursor.execute(sql)
data = mycursor.fetchall()
for row in data:
print(row[0],row[1],row[2],row[3])
mycursor.close()
mydb.close()
Some of the features in this project to insert contacts, delete contacts, update contact information, and display saved contacts. Contact details are saved in mysql database. view project