DEV Community

Avnish
Avnish

Posted on

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")
Enter fullscreen mode Exit fullscreen mode
  1. Create Table:
   mycursor.execute("USE mydatabase")
   mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
Enter fullscreen mode Exit fullscreen mode
  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.")
Enter fullscreen mode Exit fullscreen mode
  1. Select:
   mycursor.execute("SELECT * FROM customers")
   result = mycursor.fetchall()
   for row in result:
       print(row)
Enter fullscreen mode Exit fullscreen mode
  1. Where:
   sql = "SELECT * FROM customers WHERE address = 'Highway 21'"
   mycursor.execute(sql)
   result = mycursor.fetchall()
   for row in result:
       print(row)
Enter fullscreen mode Exit fullscreen mode
  1. Order By:
   sql = "SELECT * FROM customers ORDER BY name"
   mycursor.execute(sql)
   result = mycursor.fetchall()
   for row in result:
       print(row)
Enter fullscreen mode Exit fullscreen mode
  1. Delete:
   sql = "DELETE FROM customers WHERE address = 'Highway 21'"
   mycursor.execute(sql)
   mydb.commit()
   print(mycursor.rowcount, "record(s) deleted")
Enter fullscreen mode Exit fullscreen mode
  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.

Top comments (0)