In this Python MySQL tutorial we want to learn about Python MySQL CRUD Operations, Python and MySQL are two powerful tools for managing data. you can write programs that interact with MySQL databases to perform CRUD operations (Create, Read, Update, and Delete), which are essential for managing data, in this article we want to talk about Python MySQL CRUD operations, including how to connect to MySQL database, create a table, insert data, retrieve data, update data and delete data.
For working with MySQL database, we are going to use a library that is called MySQL Connector.
|
1 |
pip install mysql-connector-python |
After installation we can connect to MySQL database. for this we need to create a connection object using mysql.connector.connect() method, which takes several parameters such as the host, user, password and database name.
|
1 2 3 4 5 6 7 8 |
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) |
After connecting to the MySQL database, we can create a table using cursor object returned by the connection object. cursor object allows us to execute SQL queries on the database. this is an example of creating a table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
import mysql.connector from mysql.connector import Error # establish connection to the MySQL database try: connection = mysql.connector.connect( host='localhost', port='3306', database='mydatabase', user='root', password='' ) if connection.is_connected(): print('Connected to MySQL database') except Error as e: print(e) # create table in the MySQL database try: cursor = connection.cursor() create_table_query = ''' CREATE TABLE IF NOT EXISTS customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, phone VARCHAR(20) NOT NULL ) ''' cursor.execute(create_table_query) connection.commit() print('Table created successfully') except Error as e: print(e) # close the connection to the MySQL database connection.close() |
Now if you check WAMP Server, you will see customers table in our database.
Now we have our table and for inserting data into the table, we can use cursor object execute() method with the SQL INSERT INTO statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
import mysql.connector from mysql.connector import Error # establish connection to the MySQL database try: connection = mysql.connector.connect( host='localhost', port='3306', database='mydatabase', user='root', password='' ) if connection.is_connected(): print('Connected to MySQL database') except Error as e: print(e) # insert some data into the MySQL database try: cursor = connection.cursor() insert_query = ''' INSERT INTO customers (name, email, phone) VALUES (%s, %s, %s) ''' data = ('GeeksCoders', 'admin@geekscoders.com', '123-456-7890') cursor.execute(insert_query, data) connection.commit() print('Data inserted successfully') except Error as e: print(e) # close the connection to the MySQL database connection.close() |
Now if you run your code and check WAMP server, you will see a new data that is inserted in the table.
Now let’s retrieve our data, for retrieving data from the table, we can use cursor object execute() method with SQL SELECT statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
import mysql.connector from mysql.connector import Error # establish connection to the MySQL database try: connection = mysql.connector.connect( host='localhost', port='3306', database='mydatabase', user='root', password='' ) if connection.is_connected(): print('Connected to MySQL database') except Error as e: print(e) # retrieve data from the MySQL database try: cursor = connection.cursor() select_query = ''' SELECT * FROM customers ''' cursor.execute(select_query) rows = cursor.fetchall() for row in rows: print(row) except Error as e: print(e) # close the connection to the MySQL database connection.close() |
This will be the result
For updating data in the table, we can use cursor object execute() method with SQL UPDATE statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
import mysql.connector from mysql.connector import Error # establish connection to the MySQL database try: connection = mysql.connector.connect( host='localhost', port='3306', database='mydatabase', user='root', password='' ) if connection.is_connected(): print('Connected to MySQL database') except Error as e: print(e) # update data from the MySQL database try: cursor = connection.cursor() sql = "UPDATE customers SET name = 'GeeksCodersUpdated' WHERE name = 'GeeksCoders'" cursor.execute(sql) connection.commit() print(cursor.rowcount, "record(s) affected") except Error as e: print(e) # close the connection to the MySQL database connection.close() |
Now if you see WAMP Server the record is updated
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
import mysql.connector from mysql.connector import Error # establish connection to the MySQL database try: connection = mysql.connector.connect( host='localhost', port='3306', database='mydatabase', user='root', password='' ) if connection.is_connected(): print('Connected to MySQL database') except Error as e: print(e) # delete data from the MySQL database try: cursor = connection.cursor() sql = "DELETE FROM customers WHERE name = 'Parwiz'" cursor.execute(sql) connection.commit() print(cursor.rowcount, "record(s) deleted") except Error as e: print(e) # close the connection to the MySQL database connection.close() |
