I am creating a databaseand inside that database, I am creating a table namedwhich contains the following fields.Here EmployeeID is the primary key and Auto Incremented.

You can use the following code to create this EmployeeMaster table.

Now switch to visual studio again and now we will see database connection insert, update, delete and select operations with Python step by step.

) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]

: The following Python code shows you how you can connect to the SQL Server database.Connect in the function which contains connection string.

Driver for connection to a particular database.

for connection to a particular database. Server: Which database server we are using.

Which database server we are using. Database : Name of the database which we will use.

: Name of the database which we will use. uid : the user ID of the database server.

: the user ID of the database server. pwd: Password for connection to the database.

Insert Data To Database The following command is used to Insert data into the database. The following command is used to Insert data into the database.

import pypyodbc connection = pypyodbc.connect( 'Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa' ) cursor = connection.cursor() SQLCommand = ( "INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES ('Sourabh','200000','9928486447','Computer Programmer')" ) cursor.execute(SQLCommand) connection.commit() connection.close() print ( "Data Successfully Inserted" ) Here,

cursor() : This is a method which returns a new cursor object using the connection.

This is a method which returns a new cursor object using the connection. execute() : Prepares and executes SQL.

: Prepares and executes SQL. commit() : Commits any pending transaction to the database.

Output

And check the database, it will contain data which you have inserted.

Using Question Mark (?)

We can also execute our query with a question mark sign. Sometimes we need to insert our data at run time and we want to form our query with the + symbol and sometimes we forget to open/close double quotes or single quotes or plus symbols. So we can also execute our query with Question Mark Sign? like the following:

SQLCommand = ( "INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)" ) And we will execute our query like the following:

Values = [Name,Salary,Mobile,Designation] cursor.execute(SQLCommand,Values) Example

import pypyodbc connection = pypyodbc.connect( 'Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa' ) cursor = connection.cursor() Name= "DJ" Salary= 50000 Mobile= "9876543210" Designation= "Computer Programmer" SQLCommand = ( "INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)" ) Values = [Name,Salary,Mobile,Designation] cursor.execute(SQLCommand,Values) connection.commit() connection.close() print ( "Data Successfully Inserted" ) Output

Inside Database

Data read from user and inserted into the database

import pypyodbc connection = pypyodbc.connect( 'Driver={SQL Server};Server=.\\sqlexpress;Database=Payroll;uid=sa;pwd=sa' ) cursor = connection.cursor() Name= input( "Please Enter Name:" ) Salary=input( "Please Enter Salary:" ) Mobile=input( "Please Enter Mobile Number:" ) Designation=input( "Please Enter Designation:" ) SQLCommand = ( "INSERT INTO EmployeeMaster(Name, Salary, Mobile, Designation) VALUES (?,?,?,?)" ) Values = [Name,Salary,Mobile,Designation] cursor.execute(SQLCommand,Values) connection.commit() connection.close() print ( "Data Successfully Inserted" ) Output

Inside Database Update In Database