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