Using MySQL with Python

MySQL is a relational database management system (RDBMS) that uses SQL (Structured Query Language) to access, add, or manage content in a database. It is open-source software, which means that anyone can use and modify it according to their needs. Python, on the other hand, is a high-level programming language that is widely used for web development, data analysis, artificial intelligence, and many other high-level tasks.

Combining Python and MySQL provides a powerful platform for managing and manipulating data. To use MySQL with Python, we need a module called 'mysql-connector-python'. This module allows Python to connect to MySQL. To install this module, we use the following pip command:

pip install mysql-connector-python

Once installed, we can start using MySQL with Python. First, we need to establish a connection to the MySQL database. Here is an example of how to do this:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)
print(mydb)

If the connection is successful, you will see a MySQLConnection object in the console. Now that we are connected to MySQL, we can start executing SQL commands.

To create a database, we use the 'cursor()' method to get a cursor object and then use the 'execute()' method to execute SQL commands. Here is an example of how to create a database called 'mydatabase':

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")

To verify that the database was created, we can run the command 'SHOW DATABASES':

mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)

This will print all the databases in your MySQL. You should see 'mydatabase' in the list.

To create a table, we first need to select a database using the 'USE' command. After that, we can create a table using the 'CREATE TABLE' command. Here is an example of how to create a table called 'customers':

mycursor.execute("USE mydatabase")
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

To insert data into a table, we use the 'INSERT INTO' command. Here is an example of how to insert a record into the 'customers' table:

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")

To select data from a table, we use the 'SELECT' command. Here is an example of how to select all records from the 'customers' table:

mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

To update data in a table, we use the 'UPDATE' command. Here is an example of how to update a record in the 'customers' table:

sql = "UPDATE customers SET address = 'Canyon 123' WHERE name = 'John'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")

To delete data from a table, we use the 'DELETE' command. Here is an example of how to delete a record from the 'customers' table:

sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

Finally, to close the database connection, we use the 'close()' method:

mydb.close()

In summary, Python and MySQL are a powerful combination for managing and manipulating data. With the 'mysql-connector-python' module, we can easily execute SQL commands from our Python scripts.