Run python script in mysql

CRUD Operations in Python on MySQL

Hello in this tutorial, we will understand how to perform CRUD operations to the MySQL server via python programming.

1. Introduction

• MySQL Connector Python – It is a preferred choice in the developer’s community as it is capable of executing database queries through python. It is python3 compatible and actively maintained
• by MySQL
• MySQLDB
• MySqlClient

1.1 Setting up Python

If someone needs to go through the Python installation on Windows, please watch this link. You can download the Python from this link.

1.2 Setting up MySQL Connector Python

Once the python is successfully installed on your system you can install the MySQL Connector Python using a simple pip command. You can fire the below command from the command prompt and it will successfully download the module from pypi.org and install it.

pip install mysql-connector-python

1.3 Setting up MySQL server container

To start with the tutorial, I am hoping that you have the MySQL up and running in your localhost environment. For easy setup, I have the server up and running on the docker environment. You can execute the below command to get the container running on docker in minutes and the command will also create an initial mysql database (i.e. employee ).

docker run --name mysql -e MYSQL_ROOT_PASSWORD=password123 -e MYSQL_DATABASE=employee -p 3306:3306 -d mysql

If everything goes well the container would be started successfully as shown in Fig. 1. You can use the docker ps -a command to confirm that the container is started successfully. For further information on docker basics, you can navigate to this tutorial.

2. CRUD Operations in Python on MySQL

Before going any deeper in the practical let me walk you through a simple architecture diagram where it shows that wherein the mysql connector python module fits in the picture.

• user – Identity user to work with the mysql database. Default mysql user is root
• password – Credential of the identity user
• host – Address of the database server. If running on localhost then you can either use localhost or 127.0.0.1
• port – The port number. Default mysql port is 3306
• database – Database to which you want to connect. It can be left blank

3. Code Example

Let us dive in with the programming stuff now.

3.1 Create a configuration file

Add the following code to the environment file wherein we will specify the connection and database details. You are free to change these details as per your configuration setup.

[DB] username = root password = password123 host = localhost port = 3306 database = employee

3.2 Reading the configuration

Add the following code to the python script which will read the configuration file created above and return the config object to be used later while connecting to the MySQL server. The script will import the configparser module for reading the configuration file. Remember to give the correct path where the local.env is created.

import configparser def read_db_params(): # reading the env file config = configparser.ConfigParser() config.read('config/local.env') return config

3.3 Connecting to the database

Add the following code to the python script which will connect to the mysql server with the help of the mysql.connector module.

# python mysql import mysql.connector from mysql.connector import Error from readdbconfig import * def connect(): try: # method will read the env file and return the config object params = read_db_params() # connect to database # reading the database parameters from the config object conn = mysql.connector.connect( host=params.get('DB', 'host'), database=params.get('DB', 'database'), user=params.get('DB', 'username'), password=params.get('DB', 'password'), port=params.get('DB', 'port') ) return conn except(Exception, Error) as error: print(error)

3.4 Create table

Add the following code to the python script which will create a table in the employee database. The python script will create a table if it does not exists. If it does the script will throw an error. Ensure that the database already exists.

from connecttodb import * # create table method def create_table(conn): # creating a cursor to perform a sql operation cursor = conn.cursor() # sql query query = ''' CREATE TABLE employee (id INT NOT NULL, first_name VARCHAR(255), last_name VARCHAR(100), email VARCHAR(150), gender VARCHAR(50), phone VARCHAR(100), PRIMARY KEY (id)); ''' try: # execute the command cursor.execute(query) # commit the changes conn.commit() print('Table created successfully') except(Exception, Error) as error: print(error) finally: if conn is not None: cursor.close() conn.close() print('\nConnection closed') # driver code if __name__ == '__main__': # connect to database and create table create_table(connect('employee'))

If everything goes well the following output will be shown in the IDE console. If not the exception message will be shown.

Table created successfully Connection closed

3.5 Insert data into the table

Add the following code to the python script which will insert the default employee-related data to the employee table. The python script will check if default data is present in the table or not. If present it will skip the insert operation. If not the default data will be inserted.

from connecttodb import * from helper import * # insert data def insert(conn): # creating a cursor to perform a sql operation cursor = conn.cursor() # sql query query = ''' INSERT INTO employee (id, first_name, last_name, email, gender, phone) VALUES (%s, %s, %s, %s, %s, %s); ''' try: count = get_records_count(cursor) if count > 0: print('Default data present. Skipping insert') else: data = [ (1, 'Marga', 'Cronchey', 'mcronchey0@pen.io', 'F', '314-289-7265'), (2, 'Theda', 'Mushrow', 'tmushrow1@whitehouse.gov', 'F', '804-163-9834'), (3, 'Marielle', 'Bonicelli', 'mbonicelli2@sitemeter.com', 'F', '624-922-2416'), (4, 'Locke', 'Watkinson', 'lwatkinson3@accuweather.com', 'M', '456-260-1052'), (5, 'Blakelee', 'Wilcot', 'bwilcot4@twitpic.com', 'M', '608-344-4090') ] # execute the command cursor.executemany(query, data) # commit the changes conn.commit() print('<> records inserted'.format(cursor.rowcount)) except(Exception, Error) as error: print(error) finally: if conn is not None: cursor.close() conn.close() print('\nConnection closed') # driver code if __name__ == '__main__': # connect to database and insert data into the table insert(connect('employee'))

If everything goes well the following output will be shown in the IDE console.

5 records inserted Connection closed

3.6 Get all data from the table

Add the following code to the python script which will get all the employee-related data from the employee table. The python script will check if any existing data is present in the table or not. If present it will print the data on the console. If not the following message – No data present in db will be printed on the console.

from connecttodb import * from helper import * # get all records def get_all(conn): # creating a cursor to perform a sql operation cursor = conn.cursor() # sql query query = '''SELECT * FROM employee;''' try: count = get_records_count(cursor) if count == 0: print('No data present in db') else: # execute the command cursor.execute(query) records = cursor.fetchall() print('EMPLOYEE INFORMATION') print('-------------------------------------') for record in records: full_name = record[1] + " " + record[2] print('Id = <>, Name = <>, Email = <>, Gender = <>, Phone = <>'.format(record[0], full_name, record[3], record[4], record[5])) except(Exception, Error) as error: print(error) finally: if conn is not None: cursor.close() conn.close() print('\nConnection closed') # driver code if __name__ == '__main__': # connect to database and get all data get_all(connect('employee'))

If everything goes well the following output will be shown in the IDE console.

EMPLOYEE INFORMATION ------------------------------------- Id = 1, Name = Marga Cronchey, Email = mcronchey0@pen.io, Gender = F, Phone = 314-289-7265 Id = 2, Name = Theda Mushrow, Email = tmushrow1@whitehouse.gov, Gender = F, Phone = 804-163-9834 Id = 3, Name = Marielle Bonicelli, Email = mbonicelli2@sitemeter.com, Gender = F, Phone = 624-922-2416 Id = 4, Name = Locke Watkinson, Email = lwatkinson3@accuweather.com, Gender = M, Phone = 456-260-1052 Id = 5, Name = Blakelee Wilcot, Email = bwilcot4@twitpic.com, Gender = M, Phone = 608-344-4090 Connection closed

3.7 Update a record in the table

Add the following code to the python script which will update an employee record into the table. The python script will check if the employee is present in the database. If present the record will be updated. If not it will print the following message – Employee not found will be printed on the console.

from connecttodb import * from helper import * # update a record def update(conn, eid): # creating a cursor to perform a sql operation cursor = conn.cursor() # sql query query = '''UPDATE employee SET gender = %s WHERE try: record = get_by_id(cursor, eid) if record is None: print('Employee not found'.format(eid)) else: # execute the command cursor.execute(query, ['F', eid]) # commit the changes conn.commit() print('Employee updated successfully'.format(eid)) except(Exception, Error) as error: print(error) finally: if conn is not None: cursor.close() conn.close() print('\nConnection closed') # driver code if __name__ == '__main__': # connect to database and update a record update(connect('employee'), 5)

If everything goes well the following output will be shown in the IDE console. You can run the getall.py python script to fetch the records.

Employee updated successfully Connection closed

3.8 Delete a record in the table

Add the following code to the python script which will delete an employee record into the table. The python script will check if the employee is present in the database. If present the record will be deleted. If not it will print the following message – Employee not found will be printed on the console.

from connecttodb import * from helper import * # delete a record def delete(conn, eid): # creating a cursor to perform a sql operation cursor = conn.cursor() # sql query query = '''DELETE FROM employee WHERE try: record = get_by_id(cursor, eid) if record is None: print('Employee not found'.format(eid)) else: # execute the command cursor.execute(query, [eid]) # commit the changes conn.commit() print('Employee deleted successfully'.format(eid)) except(Exception, Error) as error: print(error) finally: if conn is not None: cursor.close() conn.close() print('\nConnection closed') # driver code if __name__ == '__main__': # connect to database and delete a record delete(connect('employee'), 5)

If everything goes well the following output will be shown in the IDE console. You can run the getall.py python script to fetch the records.

Employee deleted successfully Connection closed

Similarly, we can create other scripts like deleteall.py to delete all records from the employee database and many others. That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!

4. Summary

• Introduction to mysql connector python module
• Sample programs to perform CRUD operations

You can download the source code of this tutorial from the Downloads section.

5. Download the Project

This was a python programming tutorial to connect to the MySQL server with the help of the MySQL connector python module and perform CRUD operations.

Download
You can download the full source code of this example here: CRUD Operations in Python on MySQL

Источник