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.
SQL
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
SQL Uses
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
CRUD
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
CRUD Using Sql Command
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
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
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
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
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
Python Prerequisites:
- pyodbc
CRUD Using Python
use SQL Server in Python application establish connection to database
create database table to do CRUD operations
insert data into the database table
Update user details in table
Delete user data from table
error handling is crucial working with databases close SQL Server connection when done
Conclusion
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
Top comments (0)