SQL CRUD OPERATION USING PYTHON
Published: 2024-03-05 06:09:15

Hey Reader,

My name is Seenevasaraj, and I am working as Software Developer at Luxoft India. The various project at Luxoft I am implementing automation in all viable way. Through this article I desired to provide an explanation about how we can perform CRUD operation in SQL using python module.

**<u>SQL</u>**
Structured Query Language (SQL)-standardized programming language used to manage relational databases&perform various operations on data in them,created in the 1970s SQL is regularly used database administrators and developers writing data integration scripts,  data analysts looking to set up analytical querie

**<u>SQL Uses</u>**

modifying database table &index structures;
adding updating&deleting rows of data.Retrieving subsets of information from relational database management systems (RDBMSes)  information can used for transaction processing analytics applications and other applications that require communicating with a relational database

SQL queries and other operations take form of commands written as statements and aggregated into programs that enable users to add modify or retrieve data from databasetables

table is most basic unit of database and consists of rows columns of data,single table holds records and each record is stored in row of table,tables are most used type of database objects or structures hold or reference data in relational database

- logical representations ofdata assembled from onORmore database table
- Indexes are in tables help speed up database lookup function
- Reports consist data retrieved from oneORmore tables usually subset ofdata is selected based on search criteria
- Each column present in table corresponds to category of data eg:customer name or address while each row contains data value for intersecting column

Relational databases- relational because they are composed of tables relate to each other,eg. SQL database used for customer service can have one table for customer names and addresses and other tables hold information about specific purchases product codes and customer contacts,table used to track customer contacts usually uses unique customer identifier called key or primary key to reference customer record in separate table used to store customer data such as name and contact information

SQL is very picky language when it comes to syntax

**<u>CRUD</u>** 
SQL-compatible databases used for their reliability and transactional support, SQL can prove difficult for average non-technical user especially when complex filters sorting or data merging are required,to combat this organizations build internal tools facilitate CRUD operations to interact underlying database

- Creating data 
- Retrieving  data
- Updating  data
- Deleting  data

**<u>CRUD Using Sql Command</u>**
For CRUD operations, we will create table with four columns of varying data types, this is not a CRUD operation (even though it’s called a CREATE operation)this is just provisioning table to query from

We can use CREATE TABLE statement to create table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u5byg2381mgzayl17de0.png)


**INSERT(Create)**
INSERT operation used to add records, INSERT statements can pick and choose which columns to fill when adding row alternatively, developers can provide values for all fields


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fyho6gzyfeki0ughsz4w.png)

SELECT(READ)
SELECT statement used to retrieve records from database,while SELECT statement will return all data from rows by default we can specify which rows and/or columns we need

Select all columns and rows-use * operator to select all columns and rows from database table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ha43jaht11hopnt2ymr8.png)

**UPDATE**
UPDATE statement is used to update existing records in database 

UPDATE statement developer update oneORmore columns in specific row(s) by coupling with where clause, Predictably where specifies which rows need to updated

We will use following syntax to update specific column(s) based on the condition

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xr75bjbw9a26wimgrkod.png)

**DELETE**
DELETE statement used to delete records from database table

like UPDATE when using DELETE statement we can specify one or more row(s) to deleted by using where clause,where clause is critical—otherwise on some SQL databases we will delete all of our data
delete specific row from database table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b3csp7aydz2p81hnrngk.png)

Python Prerequisites:

- pyodbc

**<u>CRUD Using Python</u>**
use SQL Server in Python application establish connection to database

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q9ojh2tgwgzzhu0lalwn.png)

create database table to do CRUD operations

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hrldpf78b9p2v4rdtfmm.png)

insert data  into the database table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/91xv565aaylyo2p4aqwa.png)

Update  user details in table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kilndegm2i2gggm2v12o.png)

Delete user data from table

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sdlxt2al3l4ttsq12t9b.png)

error handling is crucial working with databases close SQL Server connection when done

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q48iwsxbl2epj8blvgr2.png)

**<u>Conclusion</u>**
I have explained basics of performing CRUD operations using SQL Server Python module to do create table, insert data, query data, update records and delete records