In this tutorial, you’ll see the steps to update records in SQL Server using Python.
To start, here is a template that you may use to update records in SQL Server:
UPDATE table_name SET column_1 = value_1, column_2 = value_2, ... WHERE condition
In the next section, you’ll see how to use this template in practice.
Steps to Update Records in SQL Server using Python
Step 1: Create a Database and Table
If you haven’t already done so, create a database and table in SQL Server.
For demonstration purposes, let’s assume that the:
- Server name is: RON\SQLEXPRESS
- Database name is: test_database
- Table name is: products
- The ‘products’ table contains the following data:
product_id | product_name | price |
1 | Laptop | 1100 |
2 | Printer | 200 |
3 | Keyboard | 80 |
4 | Monitor | 450 |
5 | Tablet | 300 |
Step 2: Connect Python to SQL Server
You can use the following template to connect Python to SQL Server:
import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=server_name;' 'Database=database_name;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute('SELECT * FROM table_name') for i in cursor: print(i)
You may refer to the following guide that explains the full steps to connect Python to SQL Server using the pyodbc package.
In the context of our example, here is the code to connect Python to SQL Server:
import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute('SELECT * FROM products') for i in cursor: print(i)
Run the code in Python (adjusted to your server, database and table information), and you’ll see the following result:
(1, 'Laptop', 1100)
(2, 'Printer', 200)
(3, 'Keyboard', 80)
(4, 'Monitor', 450)
(5, 'Tablet', 300)
Step 3: Update the Records in SQL Server using Python
After you connected Python and SQL Server, you’ll be able to update the records in SQL Server using Python.
Here is the template that you may apply in Python to update the records:
UPDATE table_name SET column_1 = value_1, column_2 = value_2, ... WHERE condition
Let’s say that your goal is to update one of the records in SQL Server.
More specifically, let’s say that you’d like to update the price of the ‘Tablet’ product (where the product_id is 5) from 300 to 350. Therefore, the updated record should look like this:
product_id | product_name | price |
5 | Tablet | 350 |
In that case, you may apply the code below in order to update the record in SQL Server using Python:
import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute(''' UPDATE products SET price = 350 WHERE product_id = 5 ''') conn.commit()
Don’t forget to add conn.commit() at the bottom of the code to ensure that the changes would get implemented.
Step 4: Check that the record was updated
For this final step, you may want to check that the record was updated in SQL Server. To do so, simply run the following query in SQL Sever:
SELECT * FROM products
You’ll now see the updated record:
product_id | product_name | price |
1 | Laptop | 1100 |
2 | Printer | 200 |
3 | Keyboard | 80 |
4 | Monitor | 450 |
5 | Tablet | 350 |
You may also want to check the following guides to learn how to: