To work with MySQL in Python 3, you can use the Python MySQL Connector module. Here are the steps to connect to a MySQL database and perform CRUD (Create, Read, Update, Delete) operations using Python:
You can install MySQL Connector using pip command in your terminal:
pip install mysql-connector-python
To connect to the MySQL database, you need to import the mysql.connector module and create a connection object by specifying the host, user, password, and database name. Here is an example:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="yourdatabase" ) print(mydb)
After connecting to the MySQL database, you need to create a cursor object to execute SQL queries. Here is an example:
mycursor = mydb.cursor()
Once you have the cursor object, you can execute SQL queries using the execute() method. Here are some examples of SQL queries:
Create a table:
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="yourdatabase" ) mycursor = mydb.cursor() # Create a table mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
# Insert a record sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" val = ("John", "Highway 21") mycursor.execute(sql, val) mydb.commit() print(mycursor.rowcount, "record inserted.")
Selecting data from a table:
# Select all records mycursor.execute("SELECT * FROM customers") myresult = mycursor.fetchall() for x in myresult: print(x)
Updating a row in Table:
# Update a record sql = "UPDATE customers SET address = 'Canyon 123' WHERE name = 'John'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) affected")
Deleting a record from a Table:
# Delete a record sql = "DELETE FROM customers WHERE address = 'Highway 21'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) deleted")
After you finish working with the database, you should close the connection using the close() method. Here is an example:
In the next part, we will cover advance tasks we can perform on MySQL using Pythong.
Host your Django Application for free on PythonAnyWhere.
If you want complete control of your application and server, you should consider DigitalOcean. Create an account with this link and get $200 credits.