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 &gt;= 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#