In this article, you’ll see how to create a table in SQL Server using Python. An example is also included for demonstration purposes.
Steps to Create a Table in SQL Server using Python
Step 1: Install the Pyodbc package
If you haven’t already done so, install the Pyodbc package in Python using this command (under Windows):
pip install pyodbc
You may refer to this guide for the instructions to install a package in Python using PIP.
Step 2: Connect Python to SQL Server
Next, you’ll need to connect Python to SQL Server.
You may use this template to perform the connection (if needed, you can check the following guide for the full steps 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()
Step 3: Create the table in SQL Server using Python
Now you should be able to create your table in SQL Server using Python.
For example, I created a new table, where the:
- Server name is: RON\SQLEXPRESS
- Database name is: TestDB
- New table name is: People
- New People table would contain the following columns and data types:
Column Name | Data Type |
Name | nvarchar(50) |
Age | int |
City | nvarchar(50) |
For our example, here is the complete code that I used to create the table in SQL Server using Python (you’ll need to adjust the code to reflect your server and database names):
import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=TestDB;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute(''' CREATE TABLE People ( Name nvarchar(50), Age int, City nvarchar(50) ) ''') conn.commit()
Run the code in Python (adjusted to your connection string information).
Then, click on the refresh button in SQL Server, and you’ll see the new People table (with a dbo schema):
You can then run a simple SELECT query in SQL Server to display the table:
SELECT * FROM TestDB.dbo.People
You’ll notice that the table is currently empty:
Let’s say that you want to add the following values into the dbo.People table:
Name | Age | City |
Jade | 20 | London |
Mary | 47 | Boston |
Jon | 35 | Paris |
You can then apply the code below to insert the values into the table (you may check the following guide for the complete steps to insert values into SQL Server table):
import pyodbc conn = pyodbc.connect('Driver={SQL Server};' 'Server=RON\SQLEXPRESS;' 'Database=TestDB;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute(''' INSERT INTO TestDB.dbo.People (Name, Age, City) VALUES ('Jade',20,'London'), ('Mary',47,'Boston'), ('Jon',35,'Paris') ''') conn.commit()
Run the code in Python (adjusted to your connection string) to insert the values into the table.
Then, rerun the following query in SQL Server:
SELECT * FROM TestDB.dbo.People
You’ll now see the values in the table: