[7] SQL Server – Python

[1] To jest przykład użycia SQL Server z Pythonem, opartym na środowisku, w którym zainstalowano Python 3.8.  Dodatkowo zainstaluj Python DB API dla ODBC.

[root@vlsr01 ~]# dnf install python3-pyodbc

[2] Stwórz przykładowego użytkownika i bazę do testów.

[root@vlsr01 ~]# sqlcmd -S localhost -U SA
Password:

#stwórz użytkownika
1> create login cent with PASSWORD= N’TajneHaslo’;
2> go

#stwórz bazę danych
1> create database SampleDBPHP;
2> go
1> use SampleDBPHP;
2> go
Changed database context to 'SampleDBPHP’.

#stworz użytkownika BD
1> create user cent for login cent;
2> go

#powiąż użytkownika [cent] z rolą właściciela BD
1> exec sp_addrolemember 'db_owner’, 'cent’;
2> go

#stwórz [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 rows affected)

[3] Tutaj mamy przykładowe połączenie SQL z Python’em.

#potwierdź wersję ODBC Driver
[cent@vlsr01 ~]$ 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@vlsr01 ~]$ 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.8.so.1.2
UsageCount=1

[cent@vlsr01 ~]# mcedit usemssql.py
#stwórz nowy
import pyodbc

server = '127.0.0.1'
database = 'SampleDBPHP'
username = 'cent'
password = 'TajneHaslo'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; \
    SERVER='+server+'; PORT=1443; DATABASE='+database+'; UID='+username+'; PWD='+ password)
cursor = cnxn.cursor()

#pobierz z [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()

#wstaw do [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!')

#zaktualizuj w [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()

#skasuj z [SampleTable]
print ('\nDeleting user Ubuntu')
tsql = "delete from SampleTable where First_Name = ?"
with cursor.execute(tsql,'Ubuntu'):
    print ('- Successfuly Deleted!')

#zainstaluj biblioteki pip3
[cent@vlsr01 ~]$ pip3 install pyodbc

[cent@vlsr01 ~]$ python3 usemysql.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!