Python MySQL Get Started

1 year ago

3 minutes

To get started with Python and MySQL, you’ll need to install the necessary libraries and follow a few steps to establish a connection between Python and your MySQL database.

Here’s a basic guide to help you:

1. Install MySQL Connector

You’ll need a MySQL connector to allow Python to connect with MySQL. The most common one is mysql-connector-python.

Run the following command to install it:

pip install mysql-connector-python

2. Connect to MySQL Database

Once installed, you can connect to your MySQL database by using the following Python script:

import mysql.connector

# Establish the connection
mydb = mysql.connector.connect(
  host="localhost",       # Change to your MySQL host
  user="yourusername",     # Change to your MySQL username
  password="yourpassword", # Change to your MySQL password
  database="yourdatabase"  # Change to your MySQL database
)

# Print connection info
print(mydb)

If the connection is successful, you should see a connection object printed.

3. Create a Cursor Object

A cursor is used to execute SQL queries and fetch the results. Here’s how you can create a cursor and execute a basic query:

# Create a cursor object
mycursor = mydb.cursor()

# Execute a query
mycursor.execute("SELECT * FROM your_table_name")

# Fetch all the rows from the executed query
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

4. Insert Data into a Table

To insert data into a MySQL table, use the INSERT INTO SQL statement like this:

sql = "INSERT INTO your_table_name (column1, column2) VALUES (%s, %s)"
val = ("value1", "value2")
mycursor.execute(sql, val)

# Commit the transaction
mydb.commit()

print(mycursor.rowcount, "record inserted.")

5. Update Data in a Table

To update data in a MySQL table:

sql = "UPDATE your_table_name SET column1 = %s WHERE column2 = %s"
val = ("new_value", "value_to_match")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

6. Delete Data from a Table

To delete data from a table:

sql = "DELETE FROM your_table_name WHERE column1 = %s"
val = ("value_to_delete",)
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

7. Close the Connection

After you’re done with the database operations, it’s a good practice to close the connection:

mydb.close()

Example: Full Python-MySQL Workflow

import mysql.connector

# Connect to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="testdb"
)

# Create a cursor
mycursor = mydb.cursor()

# Create a table
mycursor.execute("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255))")

# Insert data into the table
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = ("John Doe", "[email protected]")
mycursor.execute(sql, val)

# Commit the transaction
mydb.commit()

# Retrieve data from the table
mycursor.execute("SELECT * FROM users")
result = mycursor.fetchall()

for row in result:
    print(row)

# Close the connection
mydb.close()

This should give you a basic understanding of how to interact with a MySQL database using Python.

Post Views: 44