Dans le cadre du cours de Base de Données Avancées, avec mon duo Hamad Tria, nous avons réalisé un projet de modélisation et implémentation d’une base de données INSEE en PostgreSQL. Nous avons utilisé les données de l’INSEE pour modéliser une base de données relationnelle en PostgreSQL. Nous avons utilisé le langage python pour extraire les données de l’INSEE et les insérer dans la base de données PostgreSQL, à l’aide de la librairie psycopg2.
Repository du projet :
Les données de l’INSEE sont structurées en plusieurs fichiers csv, avec des données sur plusieurs années. Elles comportes quelques problèmes de structure pour les bases de données relationnelles.
En effet, les données de l’INSEE sont structurées en plusieurs fichiers csv, avec des données sur plusieurs années. Chaque année de données est représentée par une colonne dans le fichier csv, ce qui rend difficile l’insertion des données dans une base de données relationnelle. Il était donc important de modéliser une base de données relationnelle qui permettrait de stocker les données de l’INSEE de manière efficace, sans avoir à modifier la structure de la base de données à chaque nouvelle année de données.
Ainsi, nous avons modélisé une base de données relationnelle en PostgreSQL qui permet de stocker les données de l’INSEE de manière efficace, sans avoir à modifier la structure de la base de données à chaque nouvelle année de données.
Voici un schéma de la base de données :
classDiagram
direction BT
class chef_lieu_departement {
char id_chef_lieu
varchar id_departement
}
class chef_lieu_region {
char id_chef_lieu
int id_region
}
class commune {
varchar nom_commune
double superf
varchar id_departement
char id_commune
}
class departement {
varchar nom_departement
int id_region
varchar id_departement
}
class region {
varchar nom_region
int id_region
}
class statistiques_mariages_age {
int nb_mariages
int nb_mariages_premier
int annee
varchar typmar3
int id_region
varchar id_departement
varchar grage
}
class statistiques_mariages_etat_matrimonial {
int nbmaries
int annee
varchar typmar
int id_region
varchar id_departement
char sexe
char etamat
}
class statistiques_mariages_mensuel {
int nbmar
int annee
varchar typmar2
int id_region
varchar id_departement
char mmar
}
class statistiques_mariages_origine {
int nb_mariages_nationalite
int nb_mariages_pays_naissance
int annee
varchar typmar2
int id_region
varchar id_departement
varchar code
}
class statistiques_population {
int annee2
double valeur
varchar codgeo
int annee
varchar type_statistique
}
chef_lieu_departement --> commune : id_chef_lieu
chef_lieu_departement --> commune : id_commune
chef_lieu_departement --> departement : id_departement
chef_lieu_region --> commune : id_chef_lieu
chef_lieu_region --> commune : id_commune
chef_lieu_region --> region : id_region
commune --> departement : id_departement
departement --> region : id_region
statistiques_mariages_age --> departement : id_departement
statistiques_mariages_age --> region : id_region
statistiques_mariages_etat_matrimonial --> departement : id_departement
statistiques_mariages_etat_matrimonial --> region : id_region
statistiques_mariages_mensuel --> departement : id_departement
statistiques_mariages_mensuel --> region : id_region
statistiques_mariages_origine --> departement : id_departement
statistiques_mariages_origine --> region : id_region
statistiques_population --> commune : codgeo
statistiques_population --> commune : id_commune
Pour se connecter à la base de données PostgreSQL avec Python, nous avons utilisé la librairie psycopg2. Voici un exemple de code Python qui permet de se connecter à la base de données PostgreSQL :
1
2
3
4
5
6
7
8
9
def connect():
config = configparser.ConfigParser()
config.read('config.ini')
db_params = config['postgresql']
try:
conn = psycopg2.connect(**db_params)
return conn
except Exception as e:
exit("Connexion impossible à la base de données: " + str(e))
Dans ce code, nous utilisons la librairie configparser pour lire les paramètres de connexion à la base de données PostgreSQL à partir d’un fichier de configuration config.ini. Nous utilisons ensuite la méthode connect de psycopg2 pour se connecter à la base de données PostgreSQL. Si la connexion à la base de données échoue, le programme affiche un message d’erreur et s’arrête.
Voici un exemple de fichier de configuration config.ini :
1
2
3
4
5
[postgresql]
host=localhost
dbname=database_name
user=username
password=3za2*HuuZD678%dDZx
Dans ce fichier de configuration, nous spécifions les paramètres de connexion à la base de données PostgreSQL, tels que l’hôte, le nom de la base de données, l’utilisateur et le mot de passe. Ces paramètres sont lus par le script Python pour se connecter à la base de données PostgreSQL.
Attention : Il est important de ne pas stocker les mots de passe en clair dans les fichiers de configuration. Il est recommandé d’utiliser des méthodes de chiffrement pour stocker les mots de passe de manière sécurisée, ici nous avons utilisé un mot de passe en clair à titre d’exemple.
Pour l’extraction et l’insertion des données, nous avons utilisé le langage python et les libraries pandas et psycopg2. Nous avons créé un script python qui permet d’extraire les données des fichiers csv de l’INSEE et de les insérer dans la base de données PostgreSQL.
Pour chaque type de données, nous avons créé une fonction python qui permet d’extraire les données du fichier csv et de les insérer dans la base de données PostgreSQL, pour une intervalle d’années donnée. Cela permet d’automatiser l’insertion des données dans la base de données PostgreSQL, sans avoir à modifier le script python à chaque nouvelle année de données.
Pour chaque table, nous créons un dataframe pandas dont la structure correspond à celle de la table dans la base de données PostgreSQL. Nous insérons ensuite les données du dataframe dans la base de données PostgreSQL à l’aide de la librairie psycopg2. Nous avons utilisé la méthode COPY de PostgreSQL pour insérer les données dans la base de données, ce qui permet d’insérer les données de manière efficace, sans avoir à insérer chaque ligne de données individuellement.
Voici la fonction python qui permet d’insérer un dataframe dans une table de la base de données PostgreSQL :
1
2
3
4
5
6
7
8
9
10
11
12
def insert_dataframe_into_table(df, table_name, columns):
conn = connect()
cur = conn.cursor()
buffer = io.StringIO()
df.to_csv(buffer, index=False, header=False)
buffer.seek(0)
copy_query = f"COPY {table_name} ({', '.join(columns)}) FROM STDIN WITH (FORMAT CSV);"
cur.copy_expert(copy_query, buffer)
conn.commit()
print(f"Données insérées dans la table '{table_name}' avec succès")
cur.close()
conn.close()
Dans cette fonction, nous utilisons la méthode to_csv de pandas pour écrire les données du dataframe dans un buffer. Nous utilisons ensuite la méthode copy_expert de psycopg2 pour insérer les données du buffer dans la table de la base de données PostgreSQL. Nous spécifions le nom de la table et les colonnes de la table dans la requête COPY, ce qui permet d’insérer les données dans la table de manière efficace.
Une fois les données insérées dans la base de données PostgreSQL, nous pouvons effectuer des requêtes SQL pour analyser les données et générer des rapports à partir des données de l’INSEE.
Par exemple, on peut calculer la densité de la population (nombre d’habitants au km²) en 2020 par région
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
r.id_region,
r.nom_region,
COALESCE(SUM(sp.valeur), 0) AS population_totale,
COALESCE(SUM(c.superf), 0) AS superficie_totale,
CASE
WHEN SUM(c.superf) > 0 THEN ROUND((SUM(sp.valeur) / SUM(c.superf))::numeric, 2)
ELSE 0.00
END AS habitants_par_km2
FROM
region r
JOIN
departement d ON r.id_region = d.id_region
JOIN
commune c ON d.id_departement = c.id_departement
JOIN
statistiques_population sp ON c.id_commune = sp.codgeo AND sp.type_statistique = 'Population' AND sp.annee = 2020
GROUP BY
r.id_region, r.nom_region
ORDER BY
habitants_par_km2 DESC;
| id_region | nom_region | population_totale | superficie_totale | habitants_par_km2 |
|---|---|---|---|---|
| 11 | Île-de-France | 12223827 | 11921.42 | 1025.37 |
| 32 | Hauts-de-France | 5961696 | 31313.93 | 190.38 |
| 93 | Provence-Alpes-Côte d’Azur | 5093759 | 30905.69 | 164.82 |
| 52 | Pays de la Loire | 3334814 | 25570.94 | 130.41 |
| 53 | Bretagne | 3237294 | 25414.73 | 127.38 |
| 28 | Normandie | 2867525 | 23611.02 | 121.45 |
| 84 | Auvergne-Rhône-Alpes | 7748092 | 65814.38 | 117.73 |
| 44 | Grand Est | 5455836 | 56067.50 | 97.31 |
| 76 | Occitanie | 5930813 | 69620.11 | 85.19 |
| 75 | Nouvelle-Aquitaine | 5859440 | 80242.53 | 73.02 |
| 24 | Centre-Val de Loire | 2517920 | 37940.16 | 66.37 |
| 27 | Bourgogne-Franche-Comté | 2751104 | 46633.62 | 58.99 |
| 94 | Corse | 343701 | 8679.79 | 39.60 |
Cette requête SQL permet de calculer la densité de la population (nombre d’habitants au km²) en 2020 par région en France. On peut voir que l’Île-de-France est la région la plus densément peuplée, avec une densité de 1025.37 habitants par km², tandis que la Corse est la région la moins densément peuplée, avec une densité de 39.60 habitants par km².
Pour automatiser le calcul de population par département et par région, nous avons créé une procédure stockée en PostgreSQL qui permet de calculer la population totale par département et par région, en fonction de l’année donnée.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
ALTER TABLE departement ADD COLUMN population_totale INT;
ALTER TABLE region ADD COLUMN population_totale INT;
CREATE OR REPLACE PROCEDURE calculer_population()
LANGUAGE plpgsql
AS $$
BEGIN
-- Calculer la population des départements
UPDATE departement d
SET population_totale = sub.population
FROM (
SELECT c.id_departement, SUM(sp.valeur) AS population
FROM commune c
JOIN statistiques_population sp ON c.id_commune = sp.codgeo
WHERE sp.type_statistique = 'Population' AND sp.annee = 2020
GROUP BY c.id_departement
) AS sub
WHERE d.id_departement = sub.id_departement;
-- Calculer la population des régions
UPDATE region r
SET population_totale = sub.population
FROM (
SELECT d.id_region, SUM(d.population_totale) AS population
FROM departement d
GROUP BY d.id_region
) AS sub
WHERE r.id_region = sub.id_region;
END;
$$;
CALL calculer_population();
Cette procédure stockée permet de calculer la population totale par département et par région en fonction de l’année donnée. On peut ensuite appeler la procédure stockée avec CALL pour calculer la population totale par département et par région en fonction de l’année donnée.
Nous avns créé des triggers pour empêcher les tables communes, départements et régions d’être modifiées directement. Les triggers permettent de vérifier si les données insérées, mises à jour ou supprimées respectent les contraintes de la base de données.
Exemple de trigger pour empêcher les modifications sur la table region :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE FUNCTION bloque_actions_region()
RETURNS trigger AS $$
BEGIN
IF TG_OP = 'DELETE' OR TG_OP = 'INSERT' THEN
RAISE EXCEPTION 'Insertion ou suppression non autorisée dans la table region';
ELSIF TG_OP = 'UPDATE' THEN
IF OLD.id_region IS DISTINCT FROM NEW.id_region OR OLD.nom_region IS DISTINCT FROM NEW.nom_region THEN
RAISE EXCEPTION 'Modification de la table region non autorisée sur les colonnes id_region ou nom_region';
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_bloquer_actions_region
BEFORE INSERT OR UPDATE OR DELETE ON region
FOR EACH ROW EXECUTE FUNCTION bloque_actions_region();
Ce trigger empêche les insertions, mises à jour et suppressions dans la table region. Il vérifie si les données insérées, mises à jour ou supprimées respectent les contraintes de la base de données. Si les données ne respectent pas les contraintes, le trigger lève une exception et empêche l’action d’être effectuée.
Nous avons également créé un trigger pour mettre à jour automatiquement la population totale des régions et des départements lorsqu’une commune est insérée, mise à jour ou supprimée.
1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE FUNCTION maj_population()
RETURNS trigger AS $$
BEGIN
CALL calculer_population();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_maj_population
AFTER INSERT OR UPDATE ON statistiques_population
FOR EACH ROW EXECUTE FUNCTION maj_population();
Ce trigger appelle la procédure stockée calculer_population pour mettre à jour automatiquement la population totale des régions et des départements lorsqu’une commune est insérée, mise à jour ou supprimée. Cela permet de maintenir les données de la base de données à jour et de garantir la cohérence des données.
Pour l’analyse des index et des plans d’exécution, nous avons utilisé l’outil EXPLAIN ANALYSE de PostgreSQL pour analyser les performances des requêtes SQL. Nous évaluons l’impact des index sur les performances sur une requête SQL permettant de trouver quelle région est la plus peuplée en 2020 avec la table statistiques_population.
valeur de la table statistiques_populationPour améliorer les performances des requêtes SQL, nous avons créé des index sur les colonnes les plus utilisées dans les requêtes. Les index permettent d’accélérer les recherches dans la base de données en créant des structures de données supplémentaires qui permettent de retrouver rapidement les données recherchées.
1
2
3
4
5
6
7
8
9
10
CREATE INDEX pop_idx ON statistiques_population (valeur) WHERE type_statistique = 'Population';
EXPLAIN ANALYSE SELECT r.id_region, r.nom_region, SUM(sp.valeur) AS population_totale
FROM region r
JOIN departement d ON r.id_region = d.id_region
JOIN commune c ON d.id_departement = c.id_departement
JOIN statistiques_population sp ON c.id_commune = sp.codgeo
WHERE sp.annee = 2020 AND sp.type_statistique = 'Population'
GROUP BY r.id_region, r.nom_region
ORDER BY population_totale DESC
LIMIT 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Limit (cost=28771.37..28771.37 rows=1 width=82) (actual time=272.268..279.262 rows=1 loops=1)
-> Sort (cost=28771.37..28773.29 rows=770 width=82) (actual time=272.267..279.259 rows=1 loops=1)
Sort Key: (sum(sp.valeur)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=28572.44..28767.52 rows=770 width=82) (actual time=272.231..279.247 rows=13 loops=1)
Group Key: r.id_region
-> Gather Merge (cost=28572.44..28752.12 rows=1540 width=82) (actual time=272.219..279.225 rows=37 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=27572.42..27574.34 rows=770 width=82) (actual time=263.371..263.383 rows=12 loops=3)
Sort Key: r.id_region
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=27527.80..27535.50 rows=770 width=82) (actual time=263.308..263.329 rows=12 loops=3)
Group Key: r.id_region
Batches: 1 Memory Usage: 49kB
Worker 0: Batches: 1 Memory Usage: 49kB
Worker 1: Batches: 1 Memory Usage: 49kB
-> Hash Join (cost=4920.08..27480.38 rows=9484 width=82) (actual time=90.899..248.393 rows=11415 loops=3)
Hash Cond: (d.id_region = r.id_region)
-> Hash Join (cost=4892.75..27428.03 rows=9484 width=12) (actual time=90.765..238.417 rows=11415 loops=3)
Hash Cond: ((c.id_departement)::text = (d.id_departement)::text)
-> Hash Join (cost=4886.43..27396.27 rows=9484 width=11) (actual time=90.585..227.100 rows=11415 loops=3)
Hash Cond: ((sp.codgeo)::bpchar = c.id_commune)
-> Parallel Bitmap Heap Scan on statistiques_population sp (cost=3818.06..26303.00 rows=9484 width=14) (actual time=30.992..141.551 rows=11415 loops=3)
Recheck Cond: ((type_statistique)::text = 'Population'::text)
Filter: (annee = 2020)
Rows Removed by Filter: 68492
Heap Blocks: exact=1905
-> Bitmap Index Scan on pop_idx (cost=0.00..3812.37 rows=237590 width=0) (actual time=25.049..25.050 rows=239722 loops=1)
-> Hash (cost=632.61..632.61 rows=34861 width=9) (actual time=59.169..59.170 rows=34861 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 1908kB
-> Seq Scan on commune c (cost=0.00..632.61 rows=34861 width=9) (actual time=0.076..33.666 rows=34861 loops=3)
-> Hash (cost=3.92..3.92 rows=192 width=7) (actual time=0.114..0.115 rows=96 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on departement d (cost=0.00..3.92 rows=192 width=7) (actual time=0.047..0.076 rows=96 loops=3)
-> Hash (cost=17.70..17.70 rows=770 width=74) (actual time=0.103..0.104 rows=13 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on region r (cost=0.00..17.70 rows=770 width=74) (actual time=0.085..0.088 rows=13 loops=3)
Planning Time: 1.259 ms
Execution Time: 280.319 ms
L’index sur la colonne valeur de la table statistiques_population a permis d’accélérer la requête en réduisant le temps d’exécution de la requête d’environ 100 ms. Cela montre l’importance des index pour améliorer les performances des requêtes SQL.
annee, type_statistique et valeur de la table statistiques_populationPour améliorer les performances des requêtes SQL, nous avons créé un index composite sur les colonnes annee, type_statistique et valeur de la table statistiques_population. Cet index permet d’accélérer les recherches dans la table statistiques_population en créant une structure de données supplémentaire qui permet de retrouver rapidement les données recherchées.
1
2
3
4
5
6
7
8
9
10
CREATE INDEX idx_population_composite ON statistiques_population (annee, type_statistique, valeur) WHERE type_statistique = 'Population';
EXPLAIN ANALYSE SELECT r.id_region, r.nom_region, SUM(sp.valeur) AS population_totale
FROM region r
JOIN departement d ON r.id_region = d.id_region
JOIN commune c ON d.id_departement = c.id_departement
JOIN statistiques_population sp ON c.id_commune = sp.codgeo
WHERE sp.annee = 2020 AND sp.type_statistique = 'Population'
GROUP BY r.id_region, r.nom_region
ORDER BY population_totale DESC
LIMIT 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Limit (cost=24300.14..24300.14 rows=1 width=82) (actual time=153.402..153.408 rows=1 loops=1)
-> Sort (cost=24300.14..24302.06 rows=770 width=82) (actual time=153.401..153.405 rows=1 loops=1)
Sort Key: (sum(sp.valeur)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> HashAggregate (cost=24288.59..24296.29 rows=770 width=82) (actual time=153.376..153.388 rows=13 loops=1)
Group Key: r.id_region
Batches: 1 Memory Usage: 49kB
-> Hash Join (cost=1734.84..24174.78 rows=22761 width=82) (actual time=30.762..137.295 rows=34246 loops=1)
Hash Cond: (d.id_region = r.id_region)
-> Hash Join (cost=1707.51..24087.37 rows=22761 width=12) (actual time=30.733..104.846 rows=34246 loops=1)
Hash Cond: ((c.id_departement)::text = (d.id_departement)::text)
-> Hash Join (cost=1701.19..24019.98 rows=22761 width=11) (actual time=30.679..86.422 rows=34246 loops=1)
Hash Cond: ((sp.codgeo)::bpchar = c.id_commune)
-> Bitmap Heap Scan on statistiques_population sp (cost=632.82..22891.85 rows=22761 width=14) (actual time=9.557..35.077 rows=34246 loops=1)
Recheck Cond: ((annee = 2020) AND ((type_statistique)::text = 'Population'::text))
Heap Blocks: exact=6408
-> Bitmap Index Scan on idx_population_composite (cost=0.00..627.13 rows=22761 width=0) (actual time=8.415..8.415 rows=34246 loops=1)
Index Cond: (annee = 2020)
-> Hash (cost=632.61..632.61 rows=34861 width=9) (actual time=21.090..21.090 rows=34861 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 1908kB
-> Seq Scan on commune c (cost=0.00..632.61 rows=34861 width=9) (actual time=0.008..7.359 rows=34861 loops=1)
-> Hash (cost=3.92..3.92 rows=192 width=7) (actual time=0.043..0.043 rows=96 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on departement d (cost=0.00..3.92 rows=192 width=7) (actual time=0.005..0.023 rows=96 loops=1)
-> Hash (cost=17.70..17.70 rows=770 width=74) (actual time=0.018..0.019 rows=13 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on region r (cost=0.00..17.70 rows=770 width=74) (actual time=0.010..0.012 rows=13 loops=1)
Planning Time: 2.180 ms
Execution Time: 156.935 ms
L’index composite sur les colonnes annee, type_statistique et valeur de la table statistiques_population a permis d’accélérer la requête en réduisant le temps d’exécution de la requête d’environ 120 ms comparé à la requête utilisant l’index précédent. Cet index est donc optimisé pour les requêtes qui filtrent sur les colonnes annee, type_statistique et valeur de la table statistiques_population, ce qui est le cas pour la requête analysée.
Pour garantir la cohérence des données et la fiabilité des transactions, nous avons utilisé les transactions et l’isolation des transactions en PostgreSQL. Les transactions permettent de regrouper plusieurs opérations SQL en une seule unité logique qui est exécutée de manière atomique, c’est-à-dire que toutes les opérations de la transaction sont exécutées ou aucune n’est exécutée. L’isolation des transactions permet de contrôler la visibilité des données entre les transactions concurrentes pour garantir la cohérence des données.
Nous allons illustrer l’utilisation des transactions avec un niveau d’isolation ISOLATION_LEVEL_READ_COMMITTED en PostgreSQL.
En utilisant deux threads Python pour simuler des transactions concurrentes, nous allons insérer des données dans la table statistiques_population et vérifier la cohérence des données.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
def add_population_entry(delay):
conn = connect()
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)
cur = conn.cursor()
try:
cur.execute("INSERT INTO statistiques_population (codgeo, annee, type_statistique, valeur) VALUES ('33063', 2024, 'TEST', 150000);")
print("Transaction A: Insertion réalisée, en attente de commit...")
time.sleep(delay)
conn.commit()
print("Transaction A: Population ajoutée et commitée.")
except Exception as e:
print(f"Transaction A: Erreur lors de l'ajout de population, {e}")
conn.rollback()
finally:
cur.close()
conn.close()
def read_population_entry(delay):
time.sleep(delay) # Attend que la transaction A commence
conn = connect()
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)
cur = conn.cursor()
try:
cur.execute("SELECT valeur FROM statistiques_population WHERE codgeo = '33063' AND annee = 2024 AND type_statistique = 'TEST';")
result = cur.fetchone()
if result:
print(f"Transaction B: Population vue par B: {result[0]}")
else:
print("Transaction B: Aucune population trouvée.")
except Exception as e:
print(f"Transaction B: Erreur lors de la lecture de population, {e}")
finally:
cur.close()
conn.close()
# Lancer les transactions en utilisant des threads
thread_a = threading.Thread(target=add_population_entry, args=(10,))
thread_b = threading.Thread(target=read_population_entry, args=(5,))
thread_a.start()
thread_b.start()
thread_a.join()
thread_b.join()
1
2
3
Transaction A: Insertion réalisée, en attente de commit...
Transaction B: Aucune population trouvée.
Transaction A: Population ajoutée et commitée.
Dans cet exemple, la transaction A insère une population de 150000 habitants pour la commune 33063 en 2024, puis commit la transaction. La transaction B lit la population de la commune 33063 en 2024 après un délai de 5 secondes. Comme la transaction A n’a pas encore commité, la transaction B ne voit pas la population insérée par la transaction A. Après que la transaction A ait commité, la transaction B ne voit la population insérée par la transaction A.
Cela montre l’isolation des transactions en PostgreSQL, qui permet de contrôler la visibilité des données entre les transactions concurrentes pour garantir la cohérence des données.
Dans ce projet, nous avons modélisé et implémenté une base de données INSEE en PostgreSQL en utilisant les données de l’INSEE. Nous avons utilisé le langage python pour extraire les données de l’INSEE et les insérer dans la base de données PostgreSQL, à l’aide de la librairie psycopg2. Nous avons utilisé des procédures stockées, des triggers, des index et des transactions pour garantir la cohérence des données et améliorer les performances des requêtes SQL. Ce projet nous a permis de mettre en pratique nos connaissances en base de données relationnelles et en PostgreSQL, ainsi que d’approfondir nos compétences en modélisation de bases de données et en SQL.