txt->df->MS Server DB Fehler: column does not allow nulls

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
MrBrown
User
Beiträge: 3
Registriert: Freitag 23. April 2021, 09:48

Freitag 23. April 2021, 10:30

Hallo Zusammen , ich arbeite seit einigen Tagen mit Python , bin also noch ein blutiger Anfänger - letztlich soll durch pm2 mit einem Python Skript, eine Datenpunpe erzeugt werden :-) - erhoffe mir eine kleine Hilfestellung zu folgender Fehlermeldung.

Fehlermeldung:
" File "c:/testpumpe/Funktionen für MS DB und Auslesen/s1_s2_auf_db.py", line 38, in <module>
cursor.execute("INSERT INTO XXX.dbo.[RAW-TEST-1] (s2, s3, s4, s5, s6, s7, s8, s9, s10, s11) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (s1, s2, s3, s4, s5, s6, s7, s8, s9, s10))
pyodbc.IntegrityError: ('23000', "[23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'id', table
'XXX.dbo.RAW-TEST-1'; column does not allow nulls. INSERT fails. (515) (SQLExecDirectW); [23000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)")"

Habe schon verschiedene Insert Befehle ausprobiert, bisher leider ohne Erfolg. Irgendwie mag die DB meine variablen nicht aufnehmen. s2 sollte auch kein ID feld sein.

Vielen Dank an jeden der sich Zeit für eine konstruktive Antwort/Kritik nimmt.

typische Zeile der *txt
3005584465;2855422-13;122264665226;0730;29.01.21;21:13:02;31.36;okay;;

Code:
import numpy as np
import pandas as pd
import pyodbc

server = 'xxx'
database = 'xxx'
username = 'xxx'
password = 'xxx'
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

df = pd.read_csv("back_full_mp29012021.txt",
sep=";",
header=None
)

df.columns = ["Auftragsnummer", "Materialnummer", "Seriennummer", "Prüfplatz","Datum","Uhrzeit","Prüfzeit","Status","Fehler","Messung"]
print (df)

#z - Zeile
z = 0
for row in df.itertuples():

s1=str(df['Auftragsnummer'][z])
s2=str(df['Materialnummer'][z])
s3=str(df['Seriennummer'][z])
s4=str(df['Prüfplatz'][z] )
s5=str(df['Datum'][z] )
s6=str(df['Uhrzeit'][z])
s7=str(df['Prüfzeit'][z])
s8=str(df['Status'][z])
s9=str(df['Fehler'][z])
s10=str(df['Messung'][z])

cursor = cnxn.cursor()
#Rest to_do _ ggf noch Fehlermeldungen abschneiden wegen String mit 50 Zeichen?
#cursor.execute("INSERT INTO xxx.dbo.[RAW-TEST-1] (s2, s3, s4, s5, s6, s7, s8, s9, s10, s11) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (s1, s2, s3, s4, s5, s6, s7, s8, s9, s10))

cursor.execute("INSERT INTO xxx.dbo.[RAW-TEST-1] (s2, s3, s4, s5, s6, s7, s8, s9, s10, s11) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (s1, s2, s3, s4, s5, s6, s7, s8, s9, s10))

#cursor.execute("""INSERT INTO xxx.dbo.[RAW-TEST-1] (s2, s3, s4, s5, s6, s7, s8, s9, s10, s11)
#VALUES ({},{},{},{},{},{},{},{},{},{})".format(s1, s2, s3, s4, s5, s6, s7, s8, s9, s10)""")
#cursor.execute("INSERT INTO xxx.dbo.[RAW-TEST-1] (s3,s4,s5,s6,s7,s8,s9,s10,s11,s12) values(?,?,?,?,?,?,?,?,?,?)", s1, s2, s3, s4, s5, s6, s7, s8, s9, s10)

#cursor.execute("""INSERT INTO xxx.dbo.[RAW-TEST-1] (s2, s3, s4, s5, s6, s7, s8, s9, s10, s11)
#VALUES ("{s1}", "{s2}", "{s3}", "{s4}","{s5}","{s6}","{s7}","{s8}","{s9}","{s10}");""")

cnxn.commit()
cursor.close()



z=z+1 # Mit Z arbeiten für Prüfung auf neue Zahlen => in *.txt speichern und zu Beginn Zeilen zählen! Bei erolgreichem einfügen....
Sirius3
User
Beiträge: 14415
Registriert: Sonntag 21. Oktober 2012, 17:20

Freitag 23. April 2021, 11:18

Wenn Du SQLAlchemy benutzt, kann Pandas direkt in SQL-Tabellen schreiben.

Statt Strings mit + zusammenzustückeln, benutzt man Formatstrings. Bei ODBC gibt es glaube ich auch noch eine Möglichkeit, die Parameter richtig zu übergeben, da müßtest Du nochmal danach suchen.
Benutze keine kryptischen Abkürzungen, warum cnxn statt connection und woher kommt das x?
Beim lesen aus CSV gibt man die Spaltenbeschriftung gleich direkt an.

Es ist unsinnig, itertuples zu benutzen, wenn man dann per Index auf die Daten zugreift. Also der Zugriff über einen Index ist unsinn.
Man darf auch nicht alle Spalten in Strings umwandeln, denn dann umgeht man ja die Typprüfung der Datenbankanbindung.
Für das Eintragen aller Datensätze würde man einen Cursor verwenden und nicht für jeden Eintrag einen neuen.
Die Spaltennamen sind sehr schlecht gewählt. Warum heißen die s2 bis s11? Und warum nennst Du dann Deine Parameter s1 bis s10?
Der Tabellenname ist auch nicht besser.

Code: Alles auswählen

from contextlib import closing
import pandas as pd
import pyodbc 

server = 'xxx' 
database = 'xxx'
username = 'xxx' 
password = 'xxx' 
connection = pyodbc.connect(f'DRIVER={SQL Server};SERVER={server};DATABASE={database};UID={username};PWD={password}')

df = pd.read_csv("back_full_mp29012021.txt", sep=";", names=["Auftragsnummer", "Materialnummer", "Seriennummer", "Prüfplatz","Datum","Uhrzeit","Prüfzeit","Status","Fehler","Messung"])
print(df) 

with closing(connection.cursor()) as cursor:
    for row in df.itertuples():
        cursor.execute("INSERT INTO xxx.dbo.[RAW-TEST-1] (s2, s3, s4, s5, s6, s7, s8, s9, s10, s11) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (
            row.Auftragsnummer,
            row.Materialnummer,
            row.Seriennummer,
            row.Prüfplatz,
            row.Datum,
            row.Uhrzeit,
            row.Prüfzeit,
            row.Status,
            row.Fehler,
            row.Messung,
        ))
        
connection.commit()
Und wenn Du irgendwo die Fehlermeldung bekommst, dass Du versuchst NULLen in die Tabelle einzutragen, dann wohl deshalb, weil Du nicht alle Spalten angegeben hast, die fehlenden also mit NULL gefüllt werden.
MrBrown
User
Beiträge: 3
Registriert: Freitag 23. April 2021, 09:48

Mittwoch 28. April 2021, 10:01

Hey - danke für die Antwort - hat mir sehr geholfen ein gutes Stück voran zu kommen.

Wenn Du SQLAlchemy benutzt, kann Pandas direkt in SQL-Tabellen schreiben.
SQLAlchemy habe ich betrachtet, bin aber bisher an den Einstellungen der Engine gescheitert

Statt Strings mit + zusammenzustückeln, benutzt man Formatstrings. Bei ODBC gibt es glaube ich auch noch eine Möglichkeit, die Parameter richtig zu übergeben, da müßtest Du nochmal danach suchen.
Steht jetzt an

Benutze keine kryptischen Abkürzungen, warum cnxn statt connection und woher kommt das x?
Beim lesen aus CSV gibt man die Spaltenbeschriftung gleich direkt an.

Check

Es ist unsinnig, itertuples zu benutzen, wenn man dann per Index auf die Daten zugreift. Also der Zugriff über einen Index ist unsinn.
Stimme zu, dann brauch ich auch keine Dataframes nutzen - zählt wohl zu einem Irrweg im Rahmen von learning by doing

Man darf auch nicht alle Spalten in Strings umwandeln, denn dann umgeht man ja die Typprüfung der Datenbankanbindung.
Das muss ich noch testen

Für das Eintragen aller Datensätze würde man einen Cursor verwenden und nicht für jeden Eintrag einen neuen.
Danke hier war ich mir sehr unsicher

Die Spaltennamen sind sehr schlecht gewählt. Warum heißen die s2 bis s11? Und warum nennst Du dann Deine Parameter s1 bis s10?
Das war den Versuchen geschuldet, dem ID-Feld auszuweichen (was aber nicht ging, da ich es setzen muss) - lösen konnte ich das Problem durch einen korregierten Insert

cursor.execute("INSERT INTO xxx.[RAW-TEST-1] ( id, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

und

df.index = [x for x in range(1, len(df.values)+1)]
df.index.name = 'id'

Der Tabellenname ist auch nicht besser.
sehe ich auch so, hat mir direkt zu Beginn Schwierigkeiten bereitet, habe aber nur lese und schreibrechte für vorhandene Tabellen


______________next___________
Werde später nochmal den aktuellen Code vorzeigen, möchte aber erst noch ein paar Sachen machen, beschäftige mich jetzt mit Headern, habe gestern einen Vergleich der DB Daten mit der *txt Quelle erstell und erzeuge daraus dann einen neuen df mit den neuen Inhalten

x=len(df1)
y=len(df2)
df_new=df2.iloc[x:y,:]

Bis bald - wollt nicht noch länger mit einer Antwort warten - auch wenn jetzt noch keine konkrete Frage vorhanden ist.

Grüße
Mr.Brown
MrBrown
User
Beiträge: 3
Registriert: Freitag 23. April 2021, 09:48

Mittwoch 28. April 2021, 14:53

Funktioniert wie gewollt, 2 offene Kritikpunkte sind vorhanden, dass ich nicht mit Formatstrings arbeite und dass alle Werte bis auf die ID zu Strings gewandelt werden.

Fragen die ich mir momentan stelle:

- ist das Dateien kopieren, um andere Schreibprozesse in den Quelledaten nicht zu blockieren die beste Lösung?
- Kann man unbesorgt, den Dataframe aus der DB laden für den Vergleich auf neue Inhalte ? Wie wird eine DB Last ermittelt ?

sorry für a,b,c,d,e :-) - hier sind für sich sprechende Variablennamen zu finden.

Code: Alles auswählen

import pandas as pd
import pyodbc 
import numpy as np
import shutil
import os
from pathlib import Path
from contextlib import closing

# Zielverzeichnisse und Daten
# zuletzt in die DB geschrieben Daten und Zielverzeichnis
a="xxx/kopie_prozessdaten.txt"
#Quelle für neue Inhalte
b="xxx/back_full_mp29012021.txt"
#Zielverzeichnis für Kopie der Datei mit neuen Inhalten für Vergleich im nächsten Durchlauf
c="xxx/kopie_prozessdaten_2.txt"
#Prozessdaten - Verzeichnis
d='xxx/quelle'

#Path ist hier eine FKT - nachfolgend eine Variable, ggf VAr namen ändern sonst irreführend

def header(msg):
    print ('-' * 50)
    print ('[' +msg+ ' ]')

def db_eintrag(df):
    header("fkt aufruf")
    print (df)

def in_DB_schreiben (df):
    ##### pyodbc  - DB Verbindung aufbauen ######
    server = 'xxx' 
    database = 'xxx'
    username = 'xxx' 
    password = 'xxx' 
    connection = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

    #################   Typänderungern  #################################
    df = df.astype(np.str)


    with closing(connection.cursor()) as cursor:
        for row in df.itertuples():
            cursor.execute("INSERT INTO xxx.[RAW-TEST-1] ( id, s1, s2, s3, s4, s5, s6, s7, s8, s9, s10) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (
   	        row.Index,
                row.Auftragsnummer,
                row.Materialnummer,
                row.Seriennummer,
                row.Prüfplatz,
                row.Datum,
                row.Uhrzeit,
                row.Prüfzeit,
                row.Status,
                row.Fehler,
                row.Messung,
            ))
        
    connection.commit()

def extrahiere_neue_inhalte(a,b,c,d):
    #fname = Path("C:/testpumpe/Funktionen für MS DB und Auslesen/final_test/kopie_prozessdaten.txt") 
    fname = Path(a) 

    # Wenn kopie_prozessdaten vorhanden, auf neuen Inhalt prüfen
    if fname.is_file():
        # Prozessdaten Netzlaufwerk zbsp.
        path = d 
        # Quelldatei 
        source = b
        # Ziellverzeichnisse
        destination = a
        destination2 = c
        #Inhalt ins Ziel kopieren (netz->lokae txt)
        dest = shutil.copy(source, destination) 
        # df1  - alte Dataframe aus vorherigem Durchlauf (if fname.is.file())   
        df1 = pd.read_csv(c, sep=";", names=["Auftragsnummer", "Materialnummer", "Seriennummer", "Prüfplatz","Datum","Uhrzeit","Prüfzeit","Status","Fehler","Messung"])
        df1.index = [x for x in range(1, len(df1.values)+1)]
        df1.index.name = 'id'
        df1 = df1.astype(np.str)
        #df2 - neuen Inhalte aus Netzlaufwerk
        df2 = pd.read_csv(a, sep=";", names=["Auftragsnummer", "Materialnummer", "Seriennummer", "Prüfplatz","Datum","Uhrzeit","Prüfzeit","Status","Fehler","Messung"])
        df2.index = [x for x in range(1, len(df2.values)+1)]
        df2.index.name = 'id'
        df2 = df2.astype(np.str)
   
        x=len(df1)
        y=len(df2)

        if x == y:
            #keine neuen Inhalte
            header ("nix gewesen ausser spesen")
        else:
            
            header ("else2 hüpf - hier neuer df für DB")
            #neue Inhalte als Dataframe ansteuern und df erzeugen - nur auf Basis des Index
            df_new=df2.iloc[x:y,:]
            #in DB schieben
            db_eintrag (df_new)
            in_DB_schreiben(df_new)
            #Datei für Ablage und späteren Vergleich erzeugen - inaktiv atm weil sonst Inhalt gleich nach einmaligem ausführen...
            #dest = shutil.copy(destination, destination2) 
    
        #else - Wenn keine Datei vorhanden, erster Durchlauf - Datei komplett einfügen
    else:
        print("bisher kein Durchlauf, Daten komplett einfügen")
       
  
extrahiere_neue_inhalte (a,b,c,d)
Antworten