Using Python to Operate MySQL
To operate a MySQL database using Python, you first need to install the MySQL Connector/Python library. You can use the pip command for installation:
python
pip install mysql-connector-python
Next, we can operate the MySQL database as follows:
1. Import the required class library:
python
import mysql.connector
2. Create a connection to the database:
python
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)
Note: In the 'connect()' function, you need to provide the correct host name (host), username (user), password (password), and database name (database).
3. Create a cursor object and execute SQL query statements:
python
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM yourtable")
4. Obtain query results:
python
result = mycursor.fetchall()
for row in result:
print(row)
5. Insert data:
python
sql = "INSERT INTO yourtable (column1, column2, column3) VALUES (%s, %s, %s)"
val = ("value1", "value2", "value3")
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)
6. Modify data:
python
sql = "UPDATE yourtable SET column1 = %s WHERE column2 = %s"
val = ("newvalue", "existingvalue")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
7. Delete data:
python
sql = "DELETE FROM yourtable WHERE column1 = %s"
val = ("value1",)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")
This is a simple example that covers the basic operations of MySQL databases. You can try different SQL statements and operations according to your needs.
