0

im a begginer in python and i want to connect to an hfsql server in python to add a line in a table, but i've 2 composite keys in this table and dont know how to manage it, when im trying to ignore it, the error says the number of columns is wrong. here is my code:

import pypyodbc

dsn = "DRIVER={HFSQL};DSN=HFSQL;ANA=;REP=;Server Name=XXXXX;Server Port=XXXX;Database=XXXX;UID=XXXX;PWDXX=;Encryption="

try:
    connexion = pypyodbc.connect(dsn)

    # Effectuer des opérations sur la base de données
    print("Connexion à la base de données établie avec succès.")

    # Effectuer des opérations sur la base de données
    cursor = connexion.cursor()

    Num_enr = 637
    STCLEUNIK = 664
    LOCLEUNIK = 0
    RBCLEUNIK = 0
    COARTI = '207476 Ø10'
    DESA1 = 'FRAISE BOULE'
    DESA2 = 'ETAGE 11 COLONNE 5'
    COFA = 'FRAISE'
    ENTSO = 'S'
    QTE = 1
    MAGASIN = 1
    MAGORIGINE = 0
    CORESE = 0
    DAT = '13072023'
    HEURE = '854'
    CODEEMP = 'ANTOI'
    NAF = 1
    GACLEUNIK = 0
    NACLEUNIK = 0
    NAFORIGINE = 0
    STORIGINE = 0
    IMPUTABLE = 'O'
    MONTANT = 124.12
    DIVERS = 'Sortie par article'
    LIBRENUME1 = 0
    LIBRENUME2 = 0
    DIM1 = 0
    DIM2 = 0
    OBS = ''
    TYPEALEA = 0
    SPECIFIQUE = ''
    OPE_PARTICULIERE = 0
    I1CLEUNIK = 0
    ABCLEUNIK = 0
    SVCLEUNIK = 0
    DMCLEUNIK = 0
    NAFCOARTI = f"{NAF},{COARTI},{ENTSO}"
    COARTIDATEHEURE = f"{COARTI},{DAT},{HEURE}"

    # Créer la requête SQL avec les valeurs
    sql = """INSERT INTO STOCK (STCLEUNIK, LOCLEUNIK, RBCLEUNIK, COARTI, DESA1, DESA2, COFA, ENTSO, QTE,
            MAGASIN, MAGORIGINE, CORESE, DAT, HEURE, CODEEMP, NAF, GACLEUNIK, NACLEUNIK, NAFORIGINE, STORIGINE,
            IMPUTABLE, MONTANT, DIVERS, LIBRENUME1, LIBRENUME2, DIM1, DIM2, OBS, TYPEALEA, SPECIFIQUE,
            OPE_PARTICULIERE, I1CLEUNIK, ABCLEUNIK, SVCLEUNIK, DMCLEUNIK)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""

    # Exécuter la requête avec les valeurs
    cursor.execute(sql, (STCLEUNIK, LOCLEUNIK, RBCLEUNIK, COARTI, DESA1, DESA2, COFA, ENTSO, QTE,
                         MAGASIN, MAGORIGINE, CORESE, DAT, HEURE, CODEEMP, NAF, GACLEUNIK, NACLEUNIK,
                         NAFORIGINE, STORIGINE, IMPUTABLE, MONTANT, DIVERS, LIBRENUME1, LIBRENUME2, DIM1,
                         DIM2, OBS, TYPEALEA, SPECIFIQUE, OPE_PARTICULIERE, I1CLEUNIK, ABCLEUNIK,
                         SVCLEUNIK, DMCLEUNIK))



    connexion.commit()

I tried ignoring the composite keys and got another error.

Peter234
  • 1,052
  • 7
  • 24
Antoine
  • 1

1 Answers1

0

What is the output of sql(error in console)? Do you have any data in table? Maybe you try to insert data what already exists in table? When you have composite key, the data must be unique. You can try to reconfigure your keys and set the parameter IGNORE_DUP_KEY=TRUE (this helped me in MSSQL).

Ilya
  • 5
  • 5
  • Here is the output, yes i have data in the table but what i add is unique. and i cant reconfigure keys... i just need to add rows: Database connection established successfully. Error connecting to database: ('HY09', "[HY09] What happened?\r\nError in SQL code for query . Could not initialize query.\ r\nOperation prohibited on item : this is a composite key.\r\nError detected:\r\nSVCLEUNIK, DMCLEUNIK, >>>>NAFCOARTI<<<<, COARTIDATETIME)\r \n\r\nError Code: 28000002\r\nLevel: Fatal Error\r\n\r\nModule: 'wd250sql64.dll (01F250064f - – Antoine Jul 17 '23 at 12:03