6+ Pymysql 使い方 Ideas

PyMySQL/example.py at main · PyMySQL/PyMySQL · GitHub from github.com

Introduction

Are you looking for an efficient way to connect your Python application to a MySQL database? Look no further than pymysql! This library allows you to easily communicate with MySQL databases using Python. In this article, we’ll explore the basics of using pymysql, including installation, connecting to a database, executing queries, and more.

Installation

Before we get started with pymysql, we need to install it. You can do this using pip, the Python package installer. Simply open up your command prompt and type the following command:

pip install pymysql

Connecting to a Database

Now that we’ve installed pymysql, we can connect to a database. First, we need to import the library:

import pymysql

Next, we need to establish a connection to the database. This can be done using the pymysql.connect() function. Here’s an example:

connection = pymysql.connect(host='localhost', user='root', password='password', db='mydatabase')

In this example, we’re connecting to a database called “mydatabase” on the local machine. Replace “localhost”, “root”, “password”, and “mydatabase” with your own values.

Executing Queries

Once we’ve established a connection to the database, we can execute queries. This is done using the cursor() method, which returns a cursor object. Here’s an example:

cursor = connection.cursor()

Now that we have a cursor object, we can execute queries using the execute() method. Here’s an example:

cursor.execute("SELECT * FROM mytable")

In this example, we’re executing a SELECT query on a table called “mytable”. Replace “mytable” with your own table name.

Fetching Results

After executing a query, we can fetch the results using the fetchall() method. Here’s an example:

results = cursor.fetchall()

This will return a tuple containing all of the rows in the result set. You can then iterate over the tuple to access each row individually.

Inserting Data

To insert data into a table, we can use the execute() method again. Here’s an example:

cursor.execute("INSERT INTO mytable (name, age) VALUES ('John', 25)")

This will insert a new row into the “mytable” table with a name of “John” and an age of 25. Replace “name” and “age” with your own column names.

Updating Data

To update data in a table, we can use the execute() method with an UPDATE statement. Here’s an example:

cursor.execute("UPDATE mytable SET age = 26 WHERE name ='John'")

This will update the age of the row with a name of “John” to 26. Replace “age” and “name” with your own column names.

Deleting Data

To delete data from a table, we can use the execute() method with a DELETE statement. Here’s an example:

cursor.execute("DELETE FROM mytable WHERE name ='John'")

This will delete all rows from the “mytable” table where the name is “John”. Replace “name” with your own column name.

Closing the Connection

Finally, when we’re done working with the database, we need to close the connection. This can be done using the close() method. Here’s an example:

connection.close()

Conclusion

In this article, we’ve explored the basics of using pymysql to connect to a MySQL database using Python. We’ve covered installation, connecting to a database, executing queries, and more. With this knowledge, you’ll be able to start building your own Python applications that interact with MySQL databases using pymysql. Happy coding!