import mysql.connector
# Connect to MySQL server
db = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password"
)
cursor = db.cursor()
# Create a new database
cursor.execute("CREATE DATABASE mydatabase")
# Use the new database
cursor.execute("USE mydatabase")
# Create a new table
cursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)")
# Insert records
query = "INSERT INTO students (name, age) VALUES (%s, %s)"
values = [
("John", 20),
("Amy", 22),
("Mark", 19)
]
cursor.executemany(query, values)
db.commit()
# Retrieve all records
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
for record in records:
print(record)
# Update a record
cursor.execute("UPDATE students SET age = 21 WHERE name = 'John'")
db.commit()
# Retrieve and print the updated record
cursor.execute("SELECT * FROM students WHERE name = 'John'")
record = cursor.fetchone()
print(record)
# Delete a record
cursor.execute("DELETE FROM students WHERE name = 'Mark'")
db.commit()
# Retrieve all records again
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
for record in records:
print(record)