SQL Server 2019 : Use with Python2021/05/30 |
This is an example to use SQL Server with Python.
|
|
[1] | This is based on the environment Python 3.6 has been installed. Additionally, Install Python DB API for ODBC. |
[root@dlp ~]# dnf -y install python3-pyodbc
|
[2] | Create a sample User and Database for Test. |
[root@dlp ~]# sqlcmd -S localhost -U SA Password: # create login user 1> create login cent with PASSWORD= N'password'; 2> go # create [SampleDB] 1> create database SampleDB; 2> go 1> use SampleDB; 2> go Changed database context to 'SampleDB'. # create DB user 1> create user cent for login cent; 2> go # assign DB owner role to [cent] 1> exec sp_addrolemember 'db_owner', 'cent'; 2> go # create [SampleTable] 1> create table SampleTable ( 2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50) ); 3> insert into SampleTable ( 4> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux' ); 5> go |
[3] | There are some basic usage to connect to SQL Server with Python. |
# confirm ODBC Driver version [cent@dlp ~]$ odbcinst -j unixODBC 2.3.7 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/cent/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8[cent@dlp ~]$ cat /etc/odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1 UsageCount=1
[cent@dlp ~]$
vi use_mssql.py import pyodbc server = '127.0.0.1' database = 'SampleDB' username = 'cent' password = 'password' cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; \ SERVER='+server+'; PORT=1443; DATABASE='+database+'; UID='+username+'; PWD='+ password) cursor = cnxn.cursor() # Select from SampleTable print ('\nReading data from SampleTable') tsql = "select * from SampleTable;" with cursor.execute(tsql): row = cursor.fetchone() while row: print (str(row[0]) + " " + str(row[1]) + " " + str(row[2])) row = cursor.fetchone() # Insert from SampleTable print ('\nInserting a new row into SampleTable') tsql = "insert into SampleTable (First_Name, Last_Name) values (?,?);" with cursor.execute(tsql,'Ubuntu','Linux'): print ('- Successfuly Inserted!') # Update from SampleTable print ('\nUpdating Last_Name for Redhat') tsql = "update SampleTable set Last_Name = ? where First_Name = ?" with cursor.execute(tsql,'Ootpa','Redhat'): print ('- Successfuly Updated!') tsql = "select * from SampleTable;" with cursor.execute(tsql): row = cursor.fetchone() while row: print (str(row[0]) + " " + str(row[1]) + " " + str(row[2])) row = cursor.fetchone() # Delete from SampleTable print ('\nDeleting user Ubuntu') tsql = "delete from SampleTable where First_Name = ?" with cursor.execute(tsql,'Ubuntu'): print ('- Successfuly Deleted!') python3 use_mssql.py Reading data from SampleTable 1 CentOS Linux 2 RedHat Linux 3 Fedora Linux Inserting a new row into SampleTable - Successfuly Inserted! Updating Last_Name for Redhat - Successfuly Updated! 1 CentOS Linux 2 RedHat Ootpa 3 Fedora Linux 4 Ubuntu Linux Deleting user Ubuntu - Successfuly Deleted! |
Sponsored Link |