To manage databases using Python, you'll need to use the mysql.connector module to connect to the MySQL server and execute SQL queries. Here’s how you can create a new database and retrieve a list of existing databases:
To retrieve the list of all existing databases, you can use the SQL query SHOW DATABASES;. Here’s how to execute this query using Python:
import mysql.connector
# Create the connection object
myconn = mysql.connector.connect(
host="localhost",
user="root",
passwd="your_password"
)
# Create the cursor object
cur = myconn.cursor()
# Execute the query to get the list of databases
cur.execute("SHOW DATABASES")
# Fetch and print all the databases
databases = cur.fetchall()
for db in databases:
print(db)
# Close the cursor and connection
cur.close()
myconn.close()
To create a new database, you can use the SQL query CREATE DATABASE database_name;. Here’s how you can execute this query using Python:
import mysql.connector
# Create the connection object
myconn = mysql.connector.connect(
host="localhost",
user="root",
passwd="your_password"
)
# Create the cursor object
cur = myconn.cursor()
# Execute the query to create a new database
cur.execute("CREATE DATABASE PythonDB")
print("Database 'PythonDB' created successfully.")
# Close the cursor and connection
cur.close()
myconn.close()
• Connecting to MySQL: Use the mysql.connector.connect() function with parameters such as host, user, and passwd to establish a connection.
• Creating a Cursor Object: Create a cursor object using myconn.cursor() to execute SQL queries.
• Executing SQL Queries: Use cur.execute() to run SQL commands. For listing databases, use SHOW DATABASES; and for creating a database, use CREATE DATABASE database_name;.
• Fetching Results: Use cur.fetchall() to retrieve results from a query like SHOW DATABASES;.
• Closing Resources: Always close the cursor and connection after completing database operations.
You can adapt these examples to suit your needs, such as handling exceptions or performing additional operations on the database.