Interaction Python & SQL Server

SQL Server est le système de gestion de bases de données relationnelles de Microsoft. Nous verrons connecter se connecter à une base de donnée SQL Server et effectuer les opérations CRUD.

Prerequisites

SQL Server
ODBC Driver for SQL Server
Pyodbc

1. Install SQL Server

Téléchargemnt
Installation
Démarrage

2. Install ODBC Driver for SQL Server

Téléchargemnt
Installation
Démarrage

3. Install Pyodbc

$ pip install -q pyodbc

4. Create a database

Create a new database called algojungle.

Packages

# !pip install -q pyodbc
# !pip install -q pandas

import pyodbc
import pandas as pd

Paths & Config

HOST = 'localhost'
DATABASE = 'algojungle'

Connection to database

conn = pyodbc.connect('Driver={SQL Server};'
                      f'Server={HOST};'
                      f'Database={DATABASE};'
                      'Trusted_Connection=yes;')

# Create a cursor
cur = conn.cursor()

Interaction with database

0. Create a table

sql1 = "DROP TABLE IF EXISTS customers;";

sql2 = """
CREATE TABLE customers
(
    DepartmentID INTEGER PRIMARY KEY NOT NULL,
    Name VARCHAR(100),
    GroupName VARCHAR(100)
);
"""

cur.execute(sql1)
cur.execute(sql2)

conn.commit()

1. CREATE : Insert data

sql3 = """
INSERT INTO customers (DepartmentID, Name, GroupName) 
VALUES (1, 'Engineering', 'Research and Development'),
(2, 'Tool Design', 'Research and Development'),
(3, 'Sales', 'Sales and Marketing'),
(4, 'Marketing', 'Sales and Marketing'),
(5, 'Purchasing', 'Inventory Management'),
(6, 'Research and Development', 'Research and Development'),
(7, 'Production', 'Manufacturing'),
(8, 'Production Control', 'Manufacturing'),
(9, 'Human Resources', 'Executive General and Administration'),
(10, 'Finance', 'Executive General and Administration'),
(11, 'Information Services', 'Executive General and Administration'),
(12, 'Document Control', 'Quality Assurance'),
(13, 'Quality Assurance', 'Quality Assurance'),
(14, 'Facilities and Maintenance', 'Executive General and Administration'),
(15, 'Shipping and Receiving', 'Inventory Management'),
(16, 'Executive', 'Executive General and Administration');
"""

conn.execute(sql3)

conn.commit()

2. READ : Read data

sql4 = "SELECT * FROM customers;"

cur.execute(sql4)

results = pd.DataFrame([tuple(row) for row in cur.fetchall()], 
                       columns=[desc[0] for desc in cur.description])
results

	DepartmentID	Name	GroupName
0	1	Engineering	Research and Development
1	2	Tool Design	Research and Development
2	3	Sales	Sales and Marketing
3	4	Marketing	Sales and Marketing
4	5	Purchasing	Inventory Management
5	6	Research and Development	Research and Development
6	7	Production	Manufacturing
7	8	Production Control	Manufacturing
8	9	Human Resources	Executive General and Administration
9	10	Finance	Executive General and Administration
10	11	Information Services	Executive General and Administration
11	12	Document Control	Quality Assurance
12	13	Quality Assurance	Quality Assurance
13	14	Facilities and Maintenance	Executive General and Administration
14	15	Shipping and Receiving	Inventory Management
15	16	Executive	Executive General and Administration

3. UPDATE : Update table

sql5 = """
UPDATE customers 
SET Name = 'Joseph Koami Konka' 
WHERE DepartmentID = 1;
"""

cur.execute(sql5)

conn.commit()

4. DELETE : Delete data

sql6 = """
DELETE FROM customers 
WHERE DepartmentID = 1;
"""

cur.execute(sql6)

conn.commit()

Close connection

# close the communication with the SQL Server
cur.close()
conn.close()
print('Database connection closed.')

Database connection closed.

References

SQL Server: https://www.postgresql.org/download/
Pyodbc: https://www.psycopg.org/docs/install.html