Python MySQL

Title : Python MySQL

1. Python MySQL: Using Python to interact with MySQL databases.

2. Get Started: Ensure MySQL is installed, Python MySQL library is installed (mysql-connector-python is common), and a MySQL server is running.

3. Create Database:
   

   import mysql.connector

   mydb = mysql.connector.connect(
       host="localhost",
       user="username",
       password="password"
   )

   mycursor = mydb.cursor()

   mycursor.execute("CREATE DATABASE mydatabase")

1. Create Table:

   mycursor.execute("USE mydatabase")
   mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

1. Insert:

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

1. Select:

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

1. Where:

   sql = "SELECT * FROM customers WHERE address = 'Highway 21'"
   mycursor.execute(sql)
   result = mycursor.fetchall()
   for row in result:
       print(row)

1. Order By:

   sql = "SELECT * FROM customers ORDER BY name"
   mycursor.execute(sql)
   result = mycursor.fetchall()
   for row in result:
       print(row)

1. Delete:

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

1. Drop Table:
   

   sql = "DROP TABLE customers"
   mycursor.execute(sql)
   print("Table dropped successfully")
   

2. Update:
   

   sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Highway 21'"
   mycursor.execute(sql)
   mydb.commit()
   print(mycursor.rowcount, "record(s) updated")
   

3. Limit:
   

   sql = "SELECT * FROM customers LIMIT 5"
   mycursor.execute(sql)
   result = mycursor.fetchall()
   for row in result:
       print(row)
   

4. Join:
   

   sql = "SELECT customers.name AS customer, products.name AS product FROM customers INNER JOIN products ON customers.product_id = products.id"
   mycursor.execute(sql)
   result = mycursor.fetchall()
   for row in result:
       print(row)
   

Ensure to replace placeholders like 'localhost', 'username', 'password', 'mydatabase', etc., with your actual MySQL server credentials and database details. It's also important to include proper error handling in your code for robustness.