Apa itu crud di python?
In this article, we will do CRUD operation in the database using python language in the jupyter notebook. The python language becomes versatile in numerous filed all over the globe. To automate the whole project pipeline, we need also to get a connection between python and the… Posted :4 Years Ago Views :22800 This practical demonstration will help you to understand how to implement CRUD (CREATE, READ, UPDATE and DELETE) operations functionality in Python with SQL database. For any application which is associated with a database, we perform some operations for inserting the record [C= Create], reading the records [R=Read], updating the existing record with new value [U=Update] or deleting the record from the database [D=Delete]. So, These four operations are essentials when talking about any application where we have to use a database. Day 1: Set and test python environment in Visual Studio 2017 Day 2: Getting started with first python project in Visual Studio 2017 Today, we will try to implement this CRUD operations functionality with a simple Python application. We will use SQL Server for this demo and see how we make connectivity with the SQL server. We will follow step by step practical demonstration and cover each step in detail. The steps will be as follows.
As above defined the objective for this article. So, without wasting much time, let move to actual implementation. Create a Python project in Visual Studio Open Visual Studio 2017 and go to File > New > Project. It will open the New Project window, now just select the Python from the installed section in the left panel and from the middle section, just click to "Python Application" and provide the suitable name "PythonCRUD". You can also change the location for this application, if you wish and click to OK. It will take some time and your project will be ready. You can also read this article for step by step information on how to create first python application in Visual Studio. Create a database and a table in SQL As we have a python project ready and we are ready to implement CRUD operations but before that let create the database and a table in SQL Server. So that we will perform CRUD operations with a live database. Here, we will create a simple database as "Test" and will create a simple table as "Employee" as follows. Following is the SQL script for creating database and table. You can directly execute it. Once it will execute successfully, you can find a Test database along with the Employee table in SQL Server.
Create a config file for the database Now let add a configuration file in this project where we will define all the properties and their values. These properties further will use while creating the connection with SQL database. So, open the Solution Explorer and right-click on Project and choose Add > New Item. It will open the "Add New Item" window similar to as follows. From this window, we have to select "Empty Python File" and provide the valuable name as "config.py" and click to Add. After clicking to Add, it will add a new python file in the project as config.py. Now, let's modify the "config.py" and add the required properties and it's values which are required for SQL database connection as follows. Here we need, driver name, server name, database name, and credentials as user name and password. These properties is inside a object, so that we can use it later.
Install Python Package as "Pypyodbc" Next, we will install a python package which is required for making the database connection and executing the command on the database. It is Pypyodbc which is pure python package for database operations. As per PyPi.Org: Pypyodbc is One pure Python script, runs on CPython / IronPython / PyPy , Version 2.4 / 2.5 / 2.6 / 2.7 / 3.2 / 3.3, Win / Linux , 32 / 64 bit. For more..https://pypi.org/project/pypyodbc/ For installing a new package in a project environment, right click to python environment and choose "Install Python Package...". It will open Python Environments windows where you can find all install environments and just below it, we have a search box from where we can search any specific package and install it. So, just search pypyodbc as shown in below image and click to "Install pypyodbc (1.3.5)". Once you will install the pypyodbc, you can see the successfully installed message in the Output window and find that package in python environmnent as well. Create a connection file Any application which works with database and performs database operations, it has a mechanism to create and manage the connection with the database. Here in this application, we will do the same and create a database connection file, which generates the connection with a defined configuration in a configuration file. Let create a new python file as "db_connection.py" and create a method in it. This method (getConnection()) will return the database connection based on database configuration defined in config.py. First let import the pypyodbc, so that we can use it. For accessing the configuration properties defined in config.py, first we have to import that file using "import filename" and then can access the object directly.
Now, let decorate the main function from where execution will start. As you can see the below code, this is the PythonCRUD.py file which created automatically when we created the project in Visual Studio with the similar name of the project. Here, first of all, we are importing all required modules as pypyodbc for database connection and operations with SQL Server, db_connection for getting the connection based on configuration provided above. And four other files as read, create, update and delete for performing CRUD operations respectively. The first screen will give user to input for his/her choice among available options as C=Create, R=Read, U=Update and D=Delete.
Create New Record As we have done lots of things as Project Creation, Database and Table Creation, Creating the Configuration File for the database, Creating the Connection File etc. Now, let move and create a new record from python file and save it into the database. So, first create a new empty python file as "create.py" as same we have created in above step. In this file, create a class as "Create" and a method as "func_CreateData(self)", this method will be responsible for adding a new record in the database. Here we are doing the following things.
Let run the project and you will get the windows similar to below. Here you will get the options for your choice [Code will be available in the last of this article]. So, first choose your option as C. It means, you are going to create a new record. It will ask to enter the name and age respectively and data will save with appropriate message that "Data Saved Successfully". Read Data Let create one more file as "read.py" for reading the data and print it. Here we will also do the same operation as we have done in creating.
While running the project, just choose R as an option for reading the data and press the enter. You will get the output as follows. Update Existing Record Now, it's time to update the existing record. So, for that first let's create one more file as "update.py" and make the code as follows. Here we have to first get the record which needs to be updated and then we will provide the new values for that record and update it.
This time while running the project just choose U as an option for updating the record. First, it will ask to enter the employee id which is going to be updated and show the data for that employee. Next onwards this, it will ask to enter updated data for name and age for that employee and update it. Once it will update successfully, it will show the confirmation message. Delete Date Here we will try to delete the existing record. So, let create one more file for delete operation as "delete.py". Here we will first ask a user to enter the employee id which needs to be deleted and based on that it shows the data for that employee. Next message will ask to confirmation to you that you are willing to delete or not. We have two options as "Y" and "N". Just press Y, if you would to delete the record.
Run the project and choose the last option as D for deleting the record and provide the employee id which needs to be deleted. After providing the employee id, first, it will show the record data and ask for confirmation. If you provide Y as confirmation then it will delete the record and show the confirmation message that data has deleted successfully. Conclusion So, today we have learned how to perform CRUD operations in python with SQL database. I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends. Thanks PythonCRUDSQL |