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.

Get Data using Pandas

Retreive data from Postgres using Pandas prepared by Joseph Konka

Packages

# !pip install -q psycopg2-binary
# !pip install -q pandas

from getpass import getpass

import psycopg2
import pandas as pd

Paths & Config

HOST = 'localhost'
DATABASE = 'algojungle'
USER = 'postgres'

Connection to database

PASSWORD = getpass("Enter Password: ")

conn = psycopg2.connect(host=HOST, 
                        database=DATABASE, 
                        user=USER, 
                        password=PASSWORD)

Enter Password:  ········

Interaction with database using Pandas

Read table

sql = "SELECT * FROM customers;"

results = pd.read_sql(sql, conn)
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

Filter table

sql = "SELECT * FROM customers WHERE departmentid >= 10;"

results = pd.read_sql(sql, conn)
results

	departmentid	name	groupname
0	10	Finance	Executive General and Administration
1	11	Information Services	Executive General and Administration
2	12	Document Control	Quality Assurance
3	13	Quality Assurance	Quality Assurance
4	14	Facilities and Maintenance	Executive General and Administration
5	15	Shipping and Receiving	Inventory Management
6	16	Executive	Executive General and Administration

Order table

sql = "SELECT * FROM customers WHERE name like '%ance' ORDER BY name;"

results = pd.read_sql(sql, conn)
results

	departmentid	name	groupname
0	14	Facilities and Maintenance	Executive General and Administration
1	10	Finance	Executive General and Administration
2	13	Quality Assurance	Quality Assurance

Exportation

df.to_sql(name="fct_sales", con=conn, schema="public", if_exists='append', index=False, chunksize=100, method='multi')

Close connection

# close the communication with the PostgreSQL
conn.close()
print('Database connection closed.')

Database connection closed.

References

• Postgres: https://www.postgresql.org/download/
• Pysycopg Docs: https://www.psycopg.org/docs/install.html

Let's get in touch