Importations de données de fichiers texte dans une base données.

[saint.112]

Bonjour à toutes et à tous


J’ai créé une discussion Aide pour le choix d'une suite SGBD dont le titre dit tout. Grâce aux conseils amicaux et judicieux que j’ai reçus j’ai pu faire mon choix : pour le SGBD ce sera PostgreSQL et pour l’IDE j’ai une short list. J’ai aussi posé assez schématiquement un des problèmes qui va se poser à moi : j’ai déjà des données que j’avais accumulées dans une base créée avec FileMaker Pro et que j’avais exportées dans des fichiers TSV quand j’ai cessé de mettre à jour ce soft.

En quoi consistent ces fichiers :

• Ce sont des fichiers texte tabulés (TSV).
• Chaque table de la base est contenue dans un fichier dédié.
• Ils contiennent les titres des colonnes, les clés primaires et étrangères et les data.

Quel est le problème ?

1. Postgres interdit d’assigner des données dans une colonne auto-incrémentée. C’est normal, je ferais pareil à sa place. Il est donc impossible d’importer les clés primaires dans les colonnes correspondantes.
2. Les tables filles auront donc des clés étrangères qui ne correspondent plus aux clés primaires de leurs tables mères. Les relations donc tombent à l’eau.
3. Puisque chaque enregistrement des tables mères aura une nouvelle clé primaire il faut donc trouver un moyen d’assigner celle-ci aux clés étrangères des enregistrements liés de ses tables filles.

J’ai retenu une méthode que je soumets à votre sagacité :

1. Les tables ne comporteront pas de colonne pour importer les clés primaires depuis le fichier texte. Les clés primaires seront assignées par le SGBD.
2. Les clés étrangères ne seront pas importées dans les tables liées puisqu’elles ne sont plus valides.
3. Lors du développement de ma base, pour chaque table possédant une relation de type mère ou fille, je vais créer ce que j’appelle une table annexe qui sera dédiée à l’import des clés et de rien d’autre. Elles auront les colonnes suivantes :
   • Une clé primaire comme toute bonne table qui se respecte.
   • La clé étrangère correspondant à la clé primaire de sa table mère assignée par le SGBD au moment de la création de l’enregistrement.
   • La clé primaire de l’enregistrement importé (peut-être pas nécessaire mais utile quand même).
   • Pour les tables filles liées à une autre table mère, les clés étrangères de l’enregistrement importé.
4. Il ne restera plus qu’à assigner aux clés étrangères les clés primaires qui vont bien.

Pour bien illustrer mon propos je vous présente une partie de mon schéma. Il s’agit des tables Artist et Opus (les œuvres). Elles ont une relation de n à n via une table intermédiaire Creating avec laquelle elles ont une relation de 0 à n. Dans ces définitions j’ai omis les data proprement dites pour alléger. Il n’y a donc que les clés.


Un petit mot d’abord sur mes conventions de nommage :

1. J’écris tout en anglais, de façon explicite et sensible à la casse.
2. Une table représentant une entité porte le nom complet de celle-ci.
3. Une table de liaison entre plusieurs tables (comment appelle-t-on ce genre de table ?) a en général pour nom un verbe au participe présent (pour éviter le mot clé Create par exemple) et parce que il s’agit en général d’une action.
4. Les clés primaires ont le préfixe PK_ suivi de <nom de la table>
5. Les clés étrangères ont le préfixe FK_ suivi de <nom de la table>_2_<nom de la clé primaire référencée>.
6. Les index ont le préfixe Ind_ suivi de <nom de la colonne>

Voilà déjà les tables principales

   
CREATE TABLE “Artist” (
/* This table records all Artists: composers, librettists, musicians, etc. It does not contain their Name because they may have several of them. They are stored in the secondary table Artist_Name.*/
“PK_Artist” longint PRIMARY KEY SERIAL UNIQUE CREATE INDEX Ind_PK_Artist,
);


CREATE TABLE “Opus” (
/*This table records musical works*/
“PK_Opus” longint PRIMARY KEY SERIAL UNIQUE CREATE INDEX “Ind_PK_Opus”, 
);


CREATE TABLE “Creating” (
/*Relation table between Artist and Opus.*/
“PK_Creating” longint PRIMARY KEY SERIAL UNIQUE CREATE INDEX “Ind_PK_Creating”, 
“FK_Creating_2_PK_Opus” longint REFERENCES “Opus” (“PK_Opus”) CREATE INDEX “Ind_FK_Creating_2_PK_Opus”,
“FK_Creating_2_PK_Artist” longint REFERENCES “Artist” (“PK_Artist”) CREATE INDEX “Ind_FK_Creating_2_PK_Artist”,
);
    
Et voilà les tables annexes :


CREATE TABLE “Artist_Annex” (
/*Annex table to Artist for managing the primary key during import.*/
“PK_Artist_Annex” longint PRIMARY KEY UNIQUE SERIAL CREATE INDEX “Ind_PK_Artist_Annex”, 
“FK_Artist_Annex_2_PK_Artist” longint REFERENCES “Artist” (“PK_Artist”) CREATE INDEX “Ind_FK_Artist_Annex_2_PK_Artist”,
“Imported_PK_Artist” longint UNIQUE CREATE INDEX “Ind_Imported_PK_Artist”,
);

CREATE TABLE “Opus_Annex” (
/*Annex table to Opus for managing the primary key during import.*/
“PK_Opus_Annex” longint PRIMARY KEY SERIAL CREATE INDEX “Ind_PK_Opus_Annex”, 
“FK_Opus_Annex_2_PK_Opus” longint REFERENCES “Opus” (“PK_Opus”) CREATE INDEX “Ind_FK_Opus_Name_2_PK_Opus”,
“Imported_PK_Opus” longint UNIQUE CREATE INDEX “Ind_Imported_PK_Opus,
);


CREATE TABLE “Creating_Annex” (
/*Annex table to Creating for managing the primary and foreign keys during import.*/
“PK_Creating_Annex” longint PRIMARY KEY SERIAL CREATE INDEX “Ind_PK_Creating_Annex”, 
“FK_Creating_Annex_2_PK_Creating“ REFERENCES “Creating“ (“PK_Creating“) CREATE INDEX “Ind_FK_Creating_Name_2_PK_Creating”,
“Imported_PK_Creating“ longint UNIQUE CREATE INDEX “Ind_Imported_PK_Creating” ,
“Imported_FK_Creating_2_PK_Opus” REFERENCES “Opus_Annex” (“Imported_PK_Opus”) CREATE INDEX “Ind_FK_Imported_PK_Opus”,
“Imported_FK_Creating_2_PK_Artist” REFERENCES “Artist_Annex” (“Imported_PK_Artist”) CREATE INDEX “Ind_Imported_FK_Creating_2_PK_Artist”,
);

J’ai deux premières questions :
Est-il licite de créer les deux dernières relations chacune référençant une colonne qui n’est pas une clé primaire, à savoir Imported_PK_Opus et Imported_PK_Artist ?
Si oui la procédure pour rétablir la relation est simplissime puisqu’il s’agit de récupérer les valeurs contenues dans FK_Opus_Annex_2_PK_Opus et dans FK_Artist_Annex_2_PK_Artist qui sont égales respectivement à PK_Opus et à PK_Artist (à savoir celles qui nous intéressent) et de les insérer respectivement dans FK_Creating_2_PK_Opus et FK_Creating_2_PK_Artist.

Il faut naturellement commencer par importer les données. Les fonctions d’importation que j’ai vues, notamment dans jetbrains, consistent à importer en masse toutes les données d’un fichier CSV ou TSV dans une table de colonne à colonne. Il faut sans doute créer une procédure qui va importer d’abord les tables mères puis les filles en procédant ligne par ligne. C’est à programmer.

Par exemple pour les table Artist et Creating.

1. Pour une ligne donnée du fichier texte à importer il faut créer un enregistrement dans la table Artist et un lié dans Artist_Annex.
2. Importer uniquement les data de cette ligne dans l’enregistrement de la table Artist.
3. Importer dans celui la table Artist_Annex uniquement les clés primaires.
4. Une fois toutes les importations effectuées, il faut récupérer la valeur de PK_Artist pour l’insérer dans FK_Creating_2_PK_Artist. Elle se trouve dans FK_Artist_Annex_2_PK_Artist.
5. Une fois l’importation terminée et les clés étrangères ventilées où elles vont bien, tous les enregistrements de toutes les tables annexes, devenus inutiles, sont supprimés. Tout cela prendra donc une place négligeable sur le disque.

Toutes les remarques et les corrections d’erreurs de votre part sont les bienvenues.

Merci d'avance de vos lumières.

Nico
-----

[saint.112]

Je m'aperçois que le système m'a flanqué le souk dans la définition de la table Creating. De plus j'avais oublié plusieurs fois de mentionner le type de donnée. La voilà :

CREATE TABLE “Creating_Annex” (
/*Annex table to Creating for managing the primary key during import.*/
“PK_Creating_Annex” longint PRIMARY KEY SERIAL CREATE INDEX “Ind_PK_Creating_Annex”, 
“FK_Creating_Annex_2_PK_Creating“ longint REFERENCES “Creating“ (“PK_Creating“) CREATE INDEX “Ind_FK_Creating_Name_2_PK_Creating”,
“Imported_PK_Creating“ longint UNIQUE CREATE INDEX “Ind_Imported_PK_Creating” ,
“Imported_FK_Creating_2_PK_Opus” longint REFERENCES “Opus_Annex” (“Imported_PK_Opus”) CREATE INDEX “Ind_FK_Imported_PK_Opus”,
“Imported_FK_Creating_2_PK_Artist” longint REFERENCES “Artist_Annex” (“Imported_PK_Artist”) CREATE INDEX “Ind_Imported_FK_Creating_2_PK_Artist”,
);

Mais il continue à mettre des espaces où il ne faudrait pas

Nico

[Ikhar84]

Utilises la bouton code, ou insères ton code entre les balises CODE :

[code]
Mon code SQL
bien indenté
Et pas modifié par l'éditeur du fofo ...
[/code]

[vgondr98]

La solution moderne pour ce genre de problématique, c'est d'utiliser un langage de programmation backend (comme Python, Node.js ou Java) combiné à un ORM pour gérer l'importation. En tout cas, c'est comme cela que je gère mes imports en base de données (aussi à base de fichier TSV).
Avec cette approche, plutôt que de manipuler des tables et des clés brutes, tu raisonnes en objets. Tu peux créer une classe Artist qui possède une relation (une liste d'objets) avec une classe Opus.

Au moment de parser ton fichier TSV, tu n'as plus à te soucier de recréer des tables annexes ou de réaligner manuellement des ID en SQL.
Le flux ressemble à ça :

1) Ton script lit la ligne de ton fichier TSV.
2) Tu instancies ton objet Artist (par exemple : Composer X) et ton objet Opus (par exemple : Symphonie N°5).
3) Tu lies les deux objets en mémoire dans ton code.
4) Quand tu sauvegardes ton Artist, l'ORM est assez intelligent pour insérer l'artiste dans la table Artist et récupérer la clé primaire générée par Postgres, insérer l'oeuvre dans la table Opus et récupérer sa clé, créer automatiquement la ligne correspondante dans la table Creating avec les deux bonnes clés étrangères.

L'énorme avantage, c'est que toute la tuyauterie et la logique des clés sont masquées et gérées de manière totalement sécurisée par le langage de programmation. Tu n'as pas besoin de créer la moindre table temporaire dans Postgres, ni de coder des requêtes de mise à jour complexes qui risquent de casser tes relations.
Bien sûr, cela fait qu'il faut apprendre un langage de programmation mais une fois le code écrit, il est stable et tu peux le réutiliser autant que tu veux.

[A voir en vidéo sur Futura]

[polo974]

Bonjour
...
Quel est le problème ?

1. Postgres interdit d’assigner des données dans une colonne auto-incrémentée. C’est normal, je ferais pareil à sa place. Il est donc impossible d’importer les clés primaires dans les colonnes correspondantes.

mais si, on peut le faire, même si c'est casse gueule (copie complète de la console avec les messages en retour)...

test=> CREATE TABLE Opus (
/*This table records musical works*/
PK_Opus bigint PRIMARY KEY,
name text
);
CREATE TABLE
test=> ALTER TABLE Opus ALTER COLUMN PK_Opus ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME PK_Opus_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);
ALTER TABLE
test=> insert into Opus (name) values ('pas_moi');
INSERT 0 1
test=> insert into Opus (name,PK_Opus) values ('l''autre',-5);
INSERT 0 1
test=> select * from Opus;
 pk_opus |  name   
---------+---------
       1 | pas_moi
      -5 | l'autre
(2 lignes)

...

CREATE TABLE “Opus” (
/*This table records musical works*/

“PK_Opus” longint PRIMARY KEY SERIAL UNIQUE CREATE INDEX “Ind_PK_Opus”,

);

...

Nico

(le longint ne passe pas sur mon postresql)
le SERIAL est un ancien concept par rapport au IDENTITY
le UNIQUE est inutile vu que c'est déjà une PRIMARY KEY
le "by default" de "GENERATED BY DEFAULT AS IDENTITY" permet justement de forcer des clés, mais attention au risque de conflits...


mais c'est toujours une assez mauvaise idée...

[saint.112]

mais si, on peut le faire, même si c'est casse gueule

Et je me l'interdis moi-même, donc le problème ne se pose plus.

[saint.112]

La solution moderne pour ce genre de problématique, c'est d'utiliser un langage de programmation backend (comme Python, Node.js ou Java) combiné à un ORM pour gérer l'importation.

Ça m'avait pas échappé. Le problème du moment est la stratégie à adopter.

Avec cette approche, plutôt que de manipuler des tables et des clés brutes, tu raisonnes en objets. Tu peux créer une classe Artist qui possède une relation (une liste d'objets) avec une classe Opus.

Et concrètement tu implémentes ça comment ?

Au moment de parser ton fichier TSV, tu n'as plus à te soucier de recréer des tables annexes ou de réaligner manuellement des ID en SQL.

Dans le cas présent, je n'ai pas un fichier TSV, j'en ai trois, un par table. Je pourrais les fusionner mais bonjour le souk. Ils ne contiennent pas le même nombre de colonnes, elles n'ont pas les mêmes noms, etc.
Les fichiers Artist et Opus contiennent les attributs propres aux entités en question mais strictement rien qui les relie entre elles. Je te rappelle qu'on est dans le modèle relationnel. Une relation de n à n se fait dans une table dédiée qui est seule à même de créer la relation entre les deux.

Le flux ressemble à ça :
1) Ton script lit la ligne de ton fichier TSV.

Tu sembles supposer qu'il y aurait dans un fichier TSV une ligne par relation Artist/Opus, autrement dit que les deux auraient une relation 1 à 1. Auquel cas tu n'y es pas du tout. Tu n'as pas bien lu ce que j'ai écrit : il y a en l'occurrence trois fichiers.
Le fichier Artist contient environ 900 lignes, Opus 1300, Creating 1500 et seul celui-ci contient les relations entre les deux premiers.
C'est parce que la plupart des musiciens référencés ne sont ni auteurs ni compositeurs mais exécutants. Certains compositeurs ont des centaines d'œuvres. Beaucoup d'œuvres ont plusieurs auteurs et compositeurs. La relation est donc de n à n. Sinon il n'y aurait pas lieu de se fatiguer à mettre une table intermédiaire de relation et le problème serait réglé très simplement. Pourquoi tu crois que je m'échine à trouver une solution compliquée pour résoudre un problème qui te parait si simple ? Il me semble tout de même avoir expliqué en détail l'architecture de cette partie de ma base qui est parfaitement triviale en base de données relationnelle.
Et tu n'as rien vu : il y a tout ce qui concerne les interprétations avec quatre tables Artist, Opus, Performance, Ensemble, qui sont reliées entre elles par la table de relation Performing. Et là une interprétation est exécuté par 0 à n artistes et 0 à n ensembles, un artiste peut être chef des autres et/ou de 1 à n ensembles, etc. Les relations vont dans tous les sens.

[vgondr98]

Et concrètement tu implémentes ça comment ?

Comme ça :

pip3 install sqlalchemy <-- installation de l'ORM

Creation fichier demo artists.tsv
old_artist_id   artist_name
101     Mozart
102     Beethoven
103     Verdi

Creation fichier opuses.tsv
old_opus_id     opus_title
501     Requiem
502     Symphonie N°40
503     Symphonie N°5

Creation fichier creating.tsv
old_artist_id   old_opus_id
101     501
101     502
102     503

poc_import.py <-- basé sur sqllite pour faire la demo.

from sqlalchemy import create_engine, Column, Integer, String, Table, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

# 1. Configuration de la base SQLite locale
engine = create_engine('sqlite:///music_vault.db', echo=True)
Base = declarative_base()

# 2. La table de liaison physique "Creating"
creating_table = Table(
    'Creating', Base.metadata,
    Column('FK_Creating_2_PK_Artist', Integer, ForeignKey('Artist.PK_Artist'), primary_key=True),
    Column('FK_Creating_2_PK_Opus', Integer, ForeignKey('Opus.PK_Opus'), primary_key=True)
)

# 3. Les Modèles de données
class Artist(Base):
    __tablename__ = 'Artist'
    pk_artist = Column('PK_Artist', Integer, primary_key=True, autoincrement=True)
    name = Column('Name', String, nullable=False)
    # Relation N-N transparente vers Opus
    opuses = relationship('Opus', secondary=creating_table, back_populates='artists')

class Opus(Base):
    __tablename__ = 'Opus'
    pk_opus = Column('PK_Opus', Integer, primary_key=True, autoincrement=True)
    title = Column('Title', String, nullable=False)
    artists = relationship('Artist', secondary=creating_table, back_populates='opuses')

# Génération automatique du schéma d'usine
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

if __name__ == '__main__':
    tsv_file = 'artists.tsv'

    artists_memory_cache = {}
    opuses_memory_cache = {}

    # Étape 1 : Artistes
    with open('artists.tsv', 'r', encoding='utf-8') as f:
        next(f)
        for line in f:
            old_id, name = line.strip().split('\t')
            artist_obj = Artist(name=name)
            session.add(artist_obj)
            artists_memory_cache[old_id] = artist_obj

    # Étape 2 : Œuvres
    with open('opuses.tsv', 'r', encoding='utf-8') as f:
        next(f)
        for line in f:
            old_id, title = line.strip().split('\t')
            opus_obj = Opus(title=title)
            session.add(opus_obj)
            opuses_memory_cache[old_id] = opus_obj

    # Étape 3 : Relations
    with open('creating.tsv', 'r', encoding='utf-8') as f:
        next(f)
        for line in f:
            old_art_id, old_op_id = line.strip().split('\t')
            artist = artists_memory_cache.get(old_art_id)
            opus = opuses_memory_cache.get(old_op_id)
            if artist and opus:
                artist.opuses.append(opus)

    session.commit()
    session.close()
    print("\n[SUCCESS] Importation terminée. Regarde tes logs SQL au-dessus !")

Executer ce code avec python3 poc_import.py.

Cela créé le fichier music_vault.db

Fichier query.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from poc_import import Artist, Opus, declarative_base  # On réutilise tes classes

engine = create_engine('sqlite:///music_vault.db')
Session = sessionmaker(bind=engine)
session = Session()

print("--- LISTE DES ARTISTES ET LEURS ŒUVRES ---")

# 1. Tu récupères simplement tous les artistes de la base
artists = session.query(Artist).all()

for artist in artists:
    print(f"\nArtiste : {artist.name}")

    # 2. LA MAGIE : Tu accèdes aux œuvres directement via la propriété .opuses
    # SQLAlchemy exécute le JOIN en douce pour toi
    if artist.opuses:
        for opus in artist.opuses:
            print(f"  - Œuvre : {opus.title}")
    else:
        print("  - Aucune œuvre enregistrée")

session.close()

[SUCCESS] Importation terminée. Regarde tes logs SQL au-dessus !
--- LISTE DES ARTISTES ET LEURS ŒUVRES ---

Artiste : Mozart
- Œuvre : Requiem
- Œuvre : Symphonie N°40

Artiste : Beethoven
- Œuvre : Symphonie N°5

Artiste : Verdi
- Aucune œuvre enregistrée

[saint.112]

Corrige-moi si je me trompe, je ne suis pas sûr de bien comprendre le code mais il me semble bien qui tu importes les clés primaires des fichiers TSV directement dans les clés primaires des tables SQLite.
Si c'est le cas je n'aurais pas besoin de me casser la tête. Mais je me trompe peut-être.

[vgondr98]

Dans le code, quand on définit les tables, on écrit :
pk_artist = Column(..., primary_key=True, autoincrement=True)

Cela signifie que les anciens ID de tes fichiers TSV (101, 102, 501...) ne sont jamais insérés dans les colonnes des clés primaires de ta nouvelle base.
Le script laisse SQLite générer de toutes nouvelles clés propres, séquentielles, en partant de 1, 2, 3...

Comment le script fait le lien ?
C'est là que l'ORM et les dictionnaires Python entrent en jeu. Regarde cette étape dans le script : artists_memory_cache[old_id] = artist_obj

Quand Python lit ton fichier artists.tsv, il crée l'artiste en mémoire et le stocke temporairement dans un dictionnaire (une table de correspondance) où la clé est l'ancien ID (ex: 101) et la valeur est l'objet complet (Mozart).

Au moment de lire le troisième fichier (creating.tsv), le script voit passer la ligne : 101 501
Il demande au dictionnaire : "Quel est l'objet qui avait l'ancien ID 101 ?" -> Réponse : C'est l'objet Mozart.
Il demande pour le 501 -> Réponse : C'est l'objet Requiem.

Il fait le lien entre les deux en mémoire : artist.opuses.append(opus)

Au moment de session.commit(), l'ORM insère les artistes dans la table Artist. SQLite génère les nouveaux ID (ex: Mozart devient l'ID 1) puis il insère les oeuvres dans la table Opus. SQLite génère les nouveaux ID (ex: Le Requiem devient l'ID 1).
L'ORM, qui a tout en mémoire (RAM) sait que l'objet Mozart et l'objet Requiem sont liés. Il va donc chercher leurs nouveaux ID tout juste générés et insère proprement le couple (1,1) dans la table de liaison Creating.

[vgondr98]

sqlite> SELECT * FROM "Artist";
1|Mozart
2|Beethoven
3|Verdi

sqlite> SELECT * FROM "Opus";
1|Requiem
2|Symphonie N°40
3|Symphonie N°5

sqlite> SELECT * FROM "Creating";
2|3
1|1
1|2

Si tu change par :

# 3. Les Modèles de données
class Artist(Base):
    __tablename__ = 'Artist'
    pk_artist = Column('PK_Artist', Integer, primary_key=True, autoincrement=False)
    name = Column('Name', String, nullable=False)
    # Relation N-N transparente vers Opus
    opuses = relationship('Opus', secondary=creating_table, back_populates='artists')

class Opus(Base):
    __tablename__ = 'Opus'
    pk_opus = Column('PK_Opus', Integer, primary_key=True, autoincrement=False)
    title = Column('Title', String, nullable=False)
    artists = relationship('Artist', secondary=creating_table, back_populates='opuses')

with open('artists.tsv', 'r', encoding='utf-8') as f:
        next(f)
        for line in f:
            old_id, name = line.strip().split('\t')
            # ICI : On force explicitement la clé primaire avec l'ID du fichier
            artist_obj = Artist(pk_artist=int(old_id), name=name) 
            session.add(artist_obj)
            artists_memory_cache[old_id] = artist_obj

    # Étape 2 : Œuvres
    with open('opuses.tsv', 'r', encoding='utf-8') as f:
        next(f)
        for line in f:
            old_id, title = line.strip().split('\t')
            # ICI AUSSI
            opus_obj = Opus(pk_opus=int(old_id), title=title)
            session.add(opus_obj)
            opuses_memory_cache[old_id] = opus_obj

Alors dans ce cas, les anciennes clefs sont gardés.

SELECT * FROM "Artist";
101|Mozart
102|Beethoven
103|Verdi

sqlite> SELECT * FROM "Opus";
501|Requiem
502|Symphonie N°40
503|Symphonie N°5

sqlite> SELECT * FROM "Creating";
102|503
101|501
101|502

Tu as donc 2 stratégies pour tes clefs :
Stratégie 1 (Génération d'ID neufs) : C'est propre, c'est l'idéal si on veut "nettoyer" l'historique et repartir sur une base standardisée. Mais c'est un flux unique. Si tu injecte de nouveaux fichiers plus tard, il faut reconstruire la logique de correspondance.

Stratégie 2 (Conservation des ID d'origine) : C'est la méthode "idempotente" et évolutive. Si demain tu retrouve un vieux fichier d'associations FileMaker ou un TSV oublié au fond d'un répertoire, tu peut le pousser en base à la volée. L'intégrité référentielle est conservée car la clé historique sert de pivot universel.

[saint.112]

Je ne connais pas du tout SQLite. Si je comprends bien il a une syntaxe assez différente du SQL normal. Du coup je dois dire que je ne comprends pas du tout comment tout ça fonctionne. Je vais utiliser PostgreSQL qui lui est du SQL tout pur.

[polo974]

avec postgresql
(il faut la lib python psycopg2)
et la lib csv pour éviter de se palucher le tsv à la main

 Cliquez pour afficher

from sqlalchemy import create_engine, Column, Integer, String, Table, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
import csv

# 1. Configuration de la base postgresql
engine = create_engine("postgresql+psycopg2://paul:paul@localhost/test", future=True, echo=True)
Base = declarative_base()

# 2. La table de liaison physique "Creating"
creating_table = Table(
    'Creating', Base.metadata,
    Column('FK_Creating_2_PK_Artist', Integer, ForeignKey('Artist.PK_Artist'), primary_key=True),
    Column('FK_Creating_2_PK_Opus', Integer, ForeignKey('Opus.PK_Opus'), primary_key=True)
)

# 3. Les Modèles de données
class Artist(Base):
    __tablename__ = 'Artist'
    pk_artist = Column('PK_Artist', Integer, primary_key=True, autoincrement=True)
    name = Column('Name', String, nullable=False, unique=True)
    # Relation N-N transparente vers Opus
    opuses = relationship('Opus', secondary=creating_table, back_populates='artists')

class Opus(Base):
    __tablename__ = 'Opus'
    pk_opus = Column('PK_Opus', Integer, primary_key=True, autoincrement=True)
    title = Column('Title', String, nullable=False)
    artists = relationship('Artist', secondary=creating_table, back_populates='opuses')

# Génération automatique du schéma d'usine
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

if __name__ == '__main__':

    artists_memory_cache = {}
    opuses_memory_cache = {}

    # Étape 1 : Artistes
    with open('artists.tsv') as f:
        reader = csv.DictReader(f, delimiter='\t')
        for line in reader:
            old_id, name = line['old_artist_id'], line['artist_name']
            artist_obj = Artist(name=name)
            session.add(artist_obj)
            artists_memory_cache[old_id] = artist_obj

    # Étape 2 : Œuvres
    with open('opuses.tsv') as f:
        reader = csv.DictReader(f, delimiter='\t')
        for line in reader:
            old_id, title = line['old_opus_id'], line['opus_title']
            opus_obj = Opus(title=title)
            session.add(opus_obj)
            opuses_memory_cache[old_id] = opus_obj

    # Étape 3 : Relations
    with open('creating.tsv') as f:
        reader = csv.DictReader(f, delimiter='\t')
        for line in reader:
            old_art_id, old_op_id = line['old_artist_id'], line['old_opus_id']
            artist = artists_memory_cache.get(old_art_id)
            opus = opuses_memory_cache.get(old_op_id)
            if artist and opus:
                artist.opuses.append(opus)

    session.commit()
    session.close()
    print("\n[SUCCESS] Importation terminée. Regarde tes logs SQL au-dessus !")

j'ai ajouté une contrainte unique sur le nom de l'artiste.
future=True, c'est parce que j'ai sqlalchemy 1.4

les paramètre de la base sont bien sûr à adapter

[vgondr98]

C'est juste que j'avais la flemme d'installer PostgreSQL pour faire la démo. aLchemy fait une abstraction sur plein de SGBD, il faut juste changer quelque ligne comme dit polo974.

[saint.112]

Je dois dire que je n'arrive pas du tout à lire ton code. Je ne connais pas ce langage.
Ce que je retiens c’est qu’il est en effet préférable d’utiliser la mémoire plutôt que des tables. Je pencherais d’ailleurs pour des variables tableaux plutôt que des fichiers.

Je verrais donc le programme ainsi :

1. Créer deux variables-tableaux (array) Array_Artist et Array_Opus de deux colonnes et d’autant de lignes que les fichiers à importer. La première colonne contiendra la nouvelle clé primaire de chaque enregistrement. La deuxième contiendra la clé primaire importée.
2. Créer aussi Array_Creating de six colonnes.
3. Importer les seules data des fichiers Artist.tsv et Opus.tsv dans les tables correspondantes.
   Lors de la création de chaque enregistrement, insérer dans le tableau correspondant les valeurs de la nouvelle clé primaire et de l’ancienne.
4. Importer les data de Creating.tsv dans la table Creating. Cette table ne contient pas que les clés étrangères, il y a aussi des data comme le mode de création de l’artiste (auteur, compositeur), les circonstances, les motifs, dates, etc.
   Au moment de la création de chaque enregistrement, insérer dans Array_Creating dans les deux colonnes suivantes les valeurs de la nouvelle clé primaire et de l’ancienne et également les valeurs des clés étrangères des liens avec Artist et Opus.
5. Par référence au tableau Array_Artist et Array_Opus insérer les nouvelles valeurs des clés étrangères dans les deux dernières colonnes de Array_Creating.
6. Insérer dans la table Creating les valeurs des nouvelles clés étrangères.

Ça me parait finalement plus souple et plus léger que mon système de tables.

[polo974]

C'est juste que j'avais la flemme d'installer PostgreSQL pour faire la démo. aLchemy fait une abstraction sur plein de SGBD, il faut juste changer quelque ligne comme dit polo974.

Une seule ligne suffit ! celle qui crée l' "engine"...

Il existe plusieurs façons d'accéder à postgresql, j'ai choisi psycopg2 (déjà installé et connu...)

les autres modifs, c'est pour utiliser la lib csv pour le fun (et un peu plus de sécurité (le strip() trop dangereux)).

je découvre sqlalchemy et j'ai toujours un postgresql qui traîne ("c'est la base" comme dirait l'autre (en fait, c'est ce qu'on utilisait au taf, et c'est la seule que j'administre les doigts dans le nez)). donc j'ai voulu voir comment ça s'enquillait. plutôt bien en fait...

[polo974]

croisement avec 512...

python, c'est simple:

• une ligne qui termine avec un ":" annonce un bloc
• le bloc est indenté (au moins) un "cran" plus loin (donc l'indentation est hyper importante)
• et puis c'est tout (à part le reste)...

utiliser des dictionnaires (l'équivalent des map en c++) est plus efficace que les tableaux pour faire le taf.

[vgondr98]

1) Les importations d'outils

from sqlalchemy import create_engine, Column, Integer, String, Table, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
import csv

On va chercher dans la boîte à outils SQLAlchemy de quoi fabriquer le moteur de base de données, des colonnes, des types de données (Texte, Entier), et le système de relations. On prend aussi l’outil csv pour pouvoir lire les fichiers TSV.

2) Configuration de la base de données

engine = create_engine("postgresql+psycopg2://paul:paul@localhost/test", future=True, echo=True)

On crée la connexion à la base de données PostgreSQL de Paul. L'option echo=True demande au script d'afficher à l'écran tout le SQL qu'il génère en arrière-plan pour qu'on voie ce qu'il fait.

Base = declarative_base()

On crée une page blanche (le moule de base) sur laquelle on va créer nos tables.

3) La table de liaison physique "Creating"

creating_table = Table(
    'Creating', Base.metadata,
    Column('FK_Creating_2_PK_Artist', Integer, ForeignKey('Artist.PK_Artist'), primary_key=True),
    Column('FK_Creating_2_PK_Opus', Integer, ForeignKey('Opus.PK_Opus'), primary_key=True)
)

On définit la table de liaison Creating dans Postgres. Elle contient deux colonnes : une clé qui pointe vers l'Artiste, et une clé qui pointe vers l'Œuvre.

4. Les Modèles (Les structures de données)

class Artist(Base):
    __tablename__ = 'Artist'
    pk_artist = Column('PK_Artist', Integer, primary_key=True, autoincrement=True)
    name = Column('Name', String, nullable=False, unique=True)
    opuses = relationship('Opus', secondary=creating_table, back_populates='artists')

On définit ce qu'est un Artiste : il a un nouvel ID automatique généré par Postgres (autoincrement=True) et un Nom. On lui ajoute une ligne magique : la liste de ses opuses (œuvres), gérée automatiquement à travers la table de liaison définie juste au-dessus.

class Opus(Base):
    __tablename__ = 'Opus'
    pk_opus = Column('PK_Opus', Integer, primary_key=True, autoincrement=True)
    title = Column('Title', String, nullable=False)
    artists = relationship('Artist', secondary=creating_table, back_populates='opuses')

On fait exactement la même chose pour l'Opus (l'œuvre) : il a son propre nouvel ID automatique, un Titre, et la liste magique des artists qui l'ont créée.


Initialisation de la session

Base.metadata.create_all(engine)

Ordre à Postgres : s'il te plaît, regarde les structures au-dessus, et si les tables Artist, Opus et Creating n'existent pas encore dans la base, crée-les physiquement maintenant.
//Creation de la session

Session = sessionmaker(bind=engine)
session = Session()

On ouvre un bloc-notes (une session de travail). Tout ce qu'on va faire va s'écrire sur ce bloc-notes en mémoire avant d'être envoyé pour de bon à la base.

Le script principal (L'exécution)

if __name__ == '__main__':

Si on exécute le fichier pour le lancer avec la commande python3 monscrpit.py, alors on exécute les étapes suivantes

artists_memory_cache = {}
opuses_memory_cache = {}

On crée deux variables de tableau/dictionnaire vides en RAM. Elles vont servir de "caches" pour stocker la correspondance entre les anciens ID de FileMaker et les nouveaux objets créés. »

Étape 1 : Importation des Artistes

with open('artists.tsv') as f:
        reader = csv.DictReader(f, delimiter='\t')

On ouvre le fichier artists.tsv et on dit au lecteur que les colonnes sont séparées par des tabulations.

for line in reader:
            old_id, name = line['old_artist_id'], line['artist_name']

Pour chaque ligne du fichier, on extrait l'ancienne clé FileMaker (ex: 101) et le nom de l'artiste (ex: Chopin).

artist_obj = Artist(name=name)
            session.add(artist_obj)

On fabrique un objet Artiste tout neuf en mémoire avec ce nom, et on le pose sur notre bloc-notes de session. C'est la première interaction avec la donnée du tsv et la session postgré (voir Creation de la session plus haut).

artists_memory_cache[old_id] = artist_obj

Étape clé : On remplit notre tableau de correspondance en RAM. On dit : l'ancien ID 101 correspond à cet objet Artiste (qui aura le nouvel ID 1 une fois enregistré).

Étape 2 : Importation des Œuvres

with open('opuses.tsv') as f:
        reader = csv.DictReader(f, delimiter='\t')
        for line in reader:
            old_id, title = line['old_opus_id'], line['opus_title']
            opus_obj = Opus(title=title)
            session.add(opus_obj)
            opuses_memory_cache[old_id] = opus_obj

On fait exactement la même chose pour le fichier des œuvres : on lit la ligne, on extrait l'ancien ID (ex: 501) et le titre, on crée l'objet en mémoire, on le met sur le bloc-notes, et on note dans notre tableau de correspondance que l'ancien 501 est lié à cet objet.

Étape 3 : Association des relations (Le N-N)

with open('creating.tsv') as f:
        reader = csv.DictReader(f, delimiter='\t')

On ouvre le troisième fichier, celui qui contient uniquement les liaisons historiques.

for line in reader:
            old_art_id, old_op_id = line['old_artist_id'], line['old_opus_id']

Pour chaque ligne, on lit le couple d'anciens ID (ex: l'artiste 101 a créé l'œuvre 501).

artist = artists_memory_cache.get(old_art_id)
opus = opuses_memory_cache.get(old_op_id)

C'est la magie de la RAM : On va regarder dans nos deux tableaux de correspondance. On demande : "Donne-moi l'artiste qui avait l'ID 101 et l'œuvre qui avait l'ID 501". On récupère instantanément les deux objets vivants.

if artist and opus:
                artist.opuses.append(opus)

Si on a bien trouvé l'artiste et l'œuvre, on fait le lien en pur Python : on ajoute l'œuvre dans la liste de l'artiste. L'ORM comprend tout de suite qu'il devra créer une ligne dans la table Creating.

Le final : Envoi à la base de données

session.commit()

Le grand saut : On valide le bloc-notes. SQLAlchemy prend toutes les informations en mémoire, calcule les nouveaux ID générés par Postgres, remplace les anciens ID par les nouveaux dans les liaisons, et envoie toutes les requêtes d'un coup à PostgreSQL de manière ultra-rapide et sécurisée.

session.close()
print("\n[SUCCESS] Importation terminée. Regarde tes logs SQL au-dessus !")

On ferme la session, la RAM se vide proprement, et on affiche le message de succès à l'écran.