Title : Python MySQL
Python MySQL: Using Python to interact with MySQL databases.
Get Started: Ensure MySQL is installed, Python MySQL library is installed (
mysql-connector-python
is common), and a MySQL server is running.Create Database:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="username",
password="password"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")
- Create Table:
mycursor.execute("USE mydatabase")
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
- 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.")
- Select:
mycursor.execute("SELECT * FROM customers")
result = mycursor.fetchall()
for row in result:
print(row)
- Where:
sql = "SELECT * FROM customers WHERE address = 'Highway 21'"
mycursor.execute(sql)
result = mycursor.fetchall()
for row in result:
print(row)
- Order By:
sql = "SELECT * FROM customers ORDER BY name"
mycursor.execute(sql)
result = mycursor.fetchall()
for row in result:
print(row)
- Delete:
sql = "DELETE FROM customers WHERE address = 'Highway 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")
-
Drop Table:
sql = "DROP TABLE customers" mycursor.execute(sql) print("Table dropped successfully")
-
Update:
sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Highway 21'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) updated")
-
Limit:
sql = "SELECT * FROM customers LIMIT 5" mycursor.execute(sql) result = mycursor.fetchall() for row in result: print(row)
-
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)