Databases are crucial for storing and managing data. In this article, you’ll learn to create and interact with MySQL database in Python.<\/p>\n\n\n\n
PyMySQL<\/strong><\/a> is a MySQL client library written in Python that allows you to create and interact with MySQL databases.<\/p>\n\n\n\n This is a third-party library, therefore you must install it on your system. To install it using Note: You must have a MySQL server installed in your system.<\/strong><\/p>\n\n\n\n To begin, import the PyMySQL<\/strong> library into your project’s environment to handle database operations.<\/p>\n\n\n\n PyMySQL includes a In this step, you will need access to your MySQL server’s username and password.<\/p>\n\n\n\n In the above code, the The To interact with the MySQL database, you must first create a cursor object for it using the This step involves running a MySQL query to create a database on the MySQL server using the cursor ( The Finally, disconnect the database connection and cursor object with the When you run the code, nothing will appear on the console, but your database has been created on the server. You can check in the MySQL Workbench.<\/p>\n\n\n\n To display all of the databases on the server using Python, add the following code to the script.<\/p>\n\n\n\n Now, when you rerun the code, you’ll see all the databases residing on the server are displayed on the console.<\/p>\n\n\n\n You can see your newly created database ( You may simply interact with this newly generated MySQL database by adding tables and columns and performing CRUD operations.<\/p>\n\n\n\n You have established a MySQL database called Create a new file in your project directory and place the following code within it.<\/p>\n\n\n\n This time, the database name ( The The The changes are saved to the database using The Inside the function, an SQL query is defined to insert data in the After adding the entries, the function commits the changes to the database using When you run the function, the data will be added to the database.<\/p>\n\n\n\n The You’ll get all the entries inserted into the database when you run the code.<\/p>\n\n\n\n The The The changes are then saved to the database using You can see that the Charizard’s The When you run the code, you’ll see that the entire record of the id equal to 2 has been deleted.<\/p>\n\n\n\n You may work with MySQL databases in Python by using MySQL client libraries, and in this article, you’ve learned how to create and communicate with MySQL databases using the PyMySQL library.<\/p>\n\n\n\n First, you learned to create a MySQL database using the PyMySQL library in Python.<\/p>\n\n\n\n You interacted and performed the following operations after the database was established:<\/p>\n\n\n\n There are various libraries available for building a MySQL database in Python, and the process of creating and communicating with the database is nearly identical to that described in this article.<\/p>\n\n\n\n \ud83c\udfc6Other articles you might be interested in if you liked this one<\/strong><\/p>\n\n\n\n \u2705Create and integrate MySQL database in Flask app in Python<\/a>.<\/p>\n\n\n\n \u2705Create and connect SQLite database with Flask app<\/a>.<\/p>\n\n\n\n \u2705How to use Flask’s Blueprint to structure your Flask app much better<\/a>?<\/p>\n\n\n\n \u2705What are sessions and how to create a session in Flask<\/a>?<\/p>\n\n\n\n \u2705Create a WebSocket server and client in Python<\/a>.<\/p>\n\n\n\npip<\/code>, run one of the following commands in your terminal.<\/p>\n\n\n\npip install pymysql\n\n--------------------- OR ---------------------\n\npython -m pip install pymysql<\/pre><\/div>\n\n\n\n
Creating MySQL Database<\/h2>\n\n\n\n
# Importing the required lib\nimport pymysql<\/pre><\/div>\n\n\n\n
connect()<\/code> function that accepts the necessary arguments, such as host<\/strong>, username<\/strong>, password<\/strong>, database name<\/strong>, and so on, to establish a connection with the database server.<\/p>\n\n\n\n# Initialize connection with server\nmysql_db = pymysql.connect(\n host=\"localhost\",\n user=\"root\",\n password=\"********\"\n)<\/pre><\/div>\n\n\n\n
host<\/code> is where your MySQL server is hosted; in this case, it is hosted on a local machine, therefore the value \"localhost\"<\/code> is provided.<\/p>\n\n\n\nuser<\/code> is your MySQL server’s username, which is \"root\"<\/code> by default, and the password<\/code> is the one you specified when you first set up the server.<\/p>\n\n\n\ncursor()<\/code> function.<\/p>\n\n\n\n# Database cursor\ncursor = mysql_db.cursor()<\/pre><\/div>\n\n\n\n
mysql_db.cursor()<\/code>) object.<\/p>\n\n\n\n# SQL query to create database\ncursor.execute(\"CREATE DATABASE IF NOT EXISTS pokemon_db\")\ncursor.execute(\"SHOW DATABASES\")<\/pre><\/div>\n\n\n\n
cursor.execute()<\/code> executes the SQL query. The first query says “Create a database named pokemon_db if it doesn’t exist already” and the second query says “Show all the databases reside on the server”.<\/p>\n\n\n\nclose()<\/code> function.<\/p>\n\n\n\n# Closing the database cursor and connection\ncursor.close()\nmysql_db.close()<\/pre><\/div>\n\n\n\n
# Displaying databases\nfor databases in cursor:\n print(databases)<\/pre><\/div>\n\n\n\n
('books_db',)\n('information_schema',)\n('mysql',)\n('performance_schema',)\n('pokemon_db',)\n('sys',)<\/pre><\/div>\n\n\n\npokemon_db<\/code>) is being displayed.<\/p>\n\n\n\nInteracting with Database<\/h2>\n\n\n\n
Creating a Database Table<\/h3>\n\n\n\n
pokemon_db<\/code>. Now you must create a table with some fields to store data related to the fields.<\/p>\n\n\n\n# Importing PyMySQL and cursors\nimport pymysql.cursors\n\n# Initialize connection with database\nmysql_db = pymysql.connect(\n host=\"localhost\",\n user=\"root\",\n password=\"********\",\n database=\"pokemon_db\",\n cursorclass=pymysql.cursors.DictCursor\n)\n\n# Database cursor\ncursor = mysql_db.cursor()\n\n# Function to create a table\ndef create_db_table():\n cursor.execute('''\n CREATE TABLE IF NOT EXISTS pokemon (\n id INT AUTO_INCREMENT PRIMARY KEY,\n name VARCHAR(500) NOT NULL UNIQUE,\n cp INT(50) NOT NULL,\n hp INT(50) NOT NULL\n )\n ''')\n mysql_db.commit()\n\nif __name__ == \"__main__\":\n create_db_table()\n\ncursor.close()<\/pre><\/div>\n\n\n\npokemon_db<\/code>) is supplied in the connect()<\/code> function. This implies that a connection will be established to the pokemon_db<\/code> database.<\/p>\n\n\n\ncursorclass<\/code> is set to cursors.DictCursor<\/code>, a cursor that returns results in a dictionary format.<\/p>\n\n\n\ncreate_db_table()<\/code> function creates a table named \"pokemon\"<\/code> containing the following fields:<\/p>\n\n\n\n\n
id<\/code>: This field assigns a serial number for each entry made in the database automatically due to \"AUTO_INCREMENT\"<\/code>.<\/li>\n\n\n\nname<\/code>: stores the Pokemon name.<\/li>\n\n\n\ncp<\/code>: stores the combat power of the Pokemon.<\/li>\n\n\n\nhp<\/code>: stores the high power of the Pokemon.<\/li>\n<\/ul>\n\n\n\nmysql_db.commit()<\/code>. After running the code, the table will be created with the specified fields.<\/p>\n\n\n\nAdding Data to the Database<\/h3>\n\n\n\n
...\n\n# Adding data to the database\ndef add_entry():\n # SQL query\n query = '''\n INSERT INTO `pokemon` (`name`, `cp`, `hp`) \n VALUES (%s, %s, %s)\n '''\n \n # Adding three entries\n cursor.execute(query, ('Charizard', 120, 200))\n cursor.execute(query, ('Pikachu', 60, 100))\n cursor.execute(query, ('Squirtle', 78, 102))\n \n # Committing the changes\n mysql_db.commit()\n\nif __name__ == \"__main__\":\n # create_db_table()\n add_entry()\n\ncursor.close()<\/pre><\/div>\n\n\n\nadd_entry()<\/code> function is defined and added to the code from the previous section.<\/p>\n\n\n\npokemon<\/code> table to the corresponding fields. Next, the function executes the SQL query multiple times, each time with different values for the Pokemon’s name, combat power (cp<\/code>), and high power (hp<\/code>).<\/p>\n\n\n\nmysql_db.commit()<\/code>.<\/p>\n\n\n\nReading Data from the Database<\/h3>\n\n\n\n
...\n\n# Reading data from the database\ndef read_entry():\n # SQL query\n query = '''\n SELECT `name`, `cp`, `hp` FROM `pokemon`;\n '''\n cursor.execute(query)\n\n # Fetching data from the database\n for data in cursor.fetchall():\n print(\n data['name'],\n data['cp'],\n data['hp']\n )\n\n\nif __name__ == \"__main__\":\n # create_db_table()\n # add_entry()\n read_entry()\n\ncursor.close()<\/pre><\/div>\n\n\n\n
read_entry()<\/code> function executes an SQL query that selects all values from the table pokemon<\/code>. The data is then fetched using the cursor.fetchall()<\/code> function.<\/p>\n\n\n\nCharizard 120 200\nPikachu 60 100\nSquirtle 78 102<\/pre><\/div>\n\n\n\n
Updating Data in the Database<\/h3>\n\n\n\n
...\n\n# Function to update an entry\ndef update_entry():\n query = '''\n UPDATE `pokemon`\n SET `cp` = %s\n WHERE `id` = %s\n '''\n\n # Executing SQL query with values\n cursor.execute(query, (140, 2))\n\n # Committing the changes\n mysql_db.commit()\n\n\nif __name__ == \"__main__\":\n # create_db_table()\n # add_entry()\n update_entry()\n read_entry()\n\ncursor.close()<\/pre><\/div>\n\n\n\n
update_entry()<\/code> function is defined, and within it, an SQL query is written to update the pokemon<\/code> table by setting the value for the cp<\/code> field for the supplied id<\/code>.<\/p>\n\n\n\ncursor.execute()<\/code> function executes the query that updates the cp<\/code> of the Pokemon to 140 whose id<\/code> is equal to 2.<\/p>\n\n\n\nmysql_db.commit()<\/code>. When you run the code, you’ll see the change in the value.<\/p>\n\n\n\nCharizard 140 200\nPikachu 60 100\nSquirtle 78 102<\/pre><\/div>\n\n\n\n
cp<\/code> has been updated, and it is now 140 because it has an id<\/code> of 2, which may differ in your situation.<\/p>\n\n\n\nDeleting Data from the Database<\/h3>\n\n\n\n
# Function to delete the entry\ndef delete_entry():\n query = '''\n DELETE FROM `pokemon`\n WHERE `id` = %s\n '''\n\n # Executing SQL query for deletion\n cursor.execute(query, 2)\n \n # Committing the changes\n mysql_db.commit()\n\n\nif __name__ == \"__main__\":\n # create_db_table()\n # add_entry()\n # update_entry()\n delete_entry()\n read_entry()\n\n\ncursor.close()<\/pre><\/div>\n\n\n\n
delete_entry()<\/code> function executes an SQL query to remove the entire record from the pokemon<\/code> table with the supplied id<\/code>.<\/p>\n\n\n\nPikachu 60 100\nSquirtle 78 102<\/pre><\/div>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
\n
\n\n\n\n