MySQL - cursor.execute(String-Objekt) - wie?

[SebastianS]

Hallo zusammen,
ich bin a) neu hier, b) neu in python, c) neu in SQL... bitte steinigt mich nicht
Ich habe bisher den python-kurs.eu durchgearbeitet und programming mit mosh durchgenudelt.
Ich komme mehr aus der SPS-Ecke (AWL, SCL) habe im Studium mal c und c++ gehabt.
Ich arbeite mit MySQL8.0 (das aktuell aktuellste halt) und den Python-Dingsbums Anaconda bzw. nutze dort Spyder als Entwicklungsumgebung. Also Python 3.8.

Nun zu meinem Problem, bzw. andersrum, was schon alles geht:
Ich habe in der MySQL-Workbench eine lokale Datenbank angelegt mit einer Tabelle.
Habe da auch mal einen Datensatz "per Hand" angelegt.

Diesen Datensatz kann ich in Python abfragen und ausgeben. (import mysql.connector ...)
ich kann ebenfalls einen Datensatz in die Tabelle einfügen (INSERT INTO ... VALUES(...)

Das ganze läuft ja, wie im Titel beschrieben über den cursor.execute("...") ab. Der Übergabeparameter ist ja SQL-Syntax in """...""" (Geht in bestimmten Fällen auch mit weniger Anführungszeichen, aber egal).

Jetzt mein Problem: Ich baue mir diesen Parameter-String dynamisch auf (nennen wir ihn mal 'sql_statment')
Gebe ich das ganze in der Konsole per print(sql_statment) aus und kopiere die Ausgabe, füge diese in das .execute() ein funktionierts.
Versuche ich aber .execute(sql_statment) auszuführen, kommen vielfälltige Fehlermeldungen, die ich nicht in der Lage bin so zu interpretieren, dass ich da weiterkommen würde.
googelt man das Anwendungsbeispiele ist das eben aber genau so gemacht, es wird eine String-Objekt gefüllt und dann wird an das .execute() das Objekt übergeben und nicht direkt der Inhalt des Strings.

SQL_Statment = "INSERT INTO testdata VALUES("+ Act_SQL_Data[0] + ',' + Act_SQL_Data[1] + ',' + Act_SQL_Data[2]..." <-- gekürzt, geht bis [1]
SQL_Statment = '"'+'"' + '"' + SQL_Statment + '"' +'"'+'"' <-- schon beliebig variiert
print(SQL_Statment)
>>"""INSERT INTO testdata VALUES("2020-9-16-12-36-6","M9704549","","X1","","",5,0,1.17517754480893,5,"48 16 ")""" <-- aus gründen hier ein wenig zensiert
dbcursor.execute("""INSERT INTO testdata VALUES("2020-9-16-12-36-6","M9704549","","X1","","",5,0,1.17517754480893,5,"48 16 ")"""2 <-- Geht ohne Probleme
dbcursor.execute(SQL_Statment) <--




File "C:\Users\\Anaconda3\lib\site-packages\mysql\connector\connection_cext.py", line 487, in cmd_query
self._cmysql.query(query,

MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"""INSERT INTO testdata VALUES("2020-9-16-12-36-6","","","X1"' at line 1


During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File "C:\Users\\.spyder-py3\temp.py", line 135, in <module>
dbcursor.execute(SQL_Statment)

File "C:\Users\\Anaconda3\lib\site-packages\mysql\connector\cursor_cext.py", line 264, in execute
result = self._cnx.cmd_query(stmt, raw=self._raw,

File "C:\Users\\Anaconda3\lib\site-packages\mysql\connector\connection_cext.py", line 491, in cmd_query
raise errors.get_mysql_exception(exc.errno, msg=exc.msg,

ProgrammingError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"""INSERT INTO testdata VALUES("2020-9-16-12-36-6","","","X1"' at line 1

[Jankie]

Du musst (glaube ich) zuerst die Spaltennamen angeben, dann die Values, also:

Code: Alles auswählen

INSERT INTO table_name (column_1, column_2, column_3) VALUES (?,?,?)

Beim SQL_Statment sind die + bei den Values komisch, weiß nicht ob das so sein sollte.

[__blackjack__]

@SebastianS: Werte bastelt man nicht selbst per Zeichenkettenoperationen in eine SQL-Anweisung! Im besten Fall ist das unperformant und fehleranfällig, im schlechtesten Fall eine simpel auszunutzende, fette Sicherheitslücke. Für Werte schreibt man Platzhalter ins SQL und übergibt die Werte selbst als zweites Argument bei `execute()`.

Die Platzhalter-Syntax variiert leider ein bisschen von Datenbankanbindung zu Datenbankanbindung. Jankie zeigt "?", was Standard-SQL für unbenannte Platzhalter ist, aber `mysql.connector` verwendet da glaube ich eine andere Variante ("%s").

Mit ein Grund warum ich an relationale Datenbanken so gut wie immer mit SQLAlchemy ran gehe, denn das abstrahiert auch diesen Unterschied weg.

Edit: Nachtrag: Namen werden in Python klein_mit_unterstrichen geschrieben. Ausnahmen sind Konstanten (KOMPLETT_GROSS) und Klassen (MixedCase).

[Sirius3]

Variablennamen schreibt man generell klein, also act_sql_data; falls act eine Abkürzung für actual sein soll, dann meinst Du wohl current, und benutze nie Abkürzungen, das führt nur zur Verwirrung.
Wie schon mehrfach geschrieben, handelt man sich nur Probleme ein, wenn man Werte in SQL-Statements hineinstückelt. Konkret sind hier die Anführungszeichen die falschen.
Das Datumsformat sieht gelinde gesagt komisch aus. Normalerweise nimmt man etwas ISO-mäßiges, also 2020-09-16T12:36:06, aber eigentlich nimmt man gar keine Strings als Datum, sondern verwendet in Python datetime-Instanzen und für die Datenbank das entsprechende "timestamp with timezone".

[SebastianS]

@Jankie: du hast insofern recht, sofern man nicht alle Spalten einer Tabelle füllt, dann muss ich nicht noch jede Spalte einzeln angeben.
Beweis: gebe ich den String explizit (falls dass das richtige Wort ist) an, geht es ja.

@__blackjack__: Du hast natürlich auch vollkommen recht, das ist richtig dirty code. Auch hast du recht, dass sauberer Pythoncode keinen fixen String hat, der irgendwie zusammengebastelt wird, dann mit den String-Formatierung arbeitet. Ich finde auch gut, dass du ein Tool gefunden hast mit dem du super klar kommst, weil das automatisiert unsaubere Schnittstellendefinition handeln kann.
In Python oder den meißten Code-Sprachen ist es Konvention, eine Empfehlung, gutes Programmieren die Syntax zum Beispiel wie von dir beschrieben zu formatieren um die Lesbarkeit zu gewährleisten. Ist mir durchaus bewusst. Bin mir auch bewusst das ich mich nicht durchgängig dran halte, ganz toll ist dann auch, wenn man sich sachen aus verschiedenen Tuturials zusammenkopiert, und dort unterschiedliche Formatierungen des Codes gehand habt werden. Verzeih deswegen bitte, wenn ich mich da momentan beim Einarbeiten noch nicht 1000% an sowas halte. Ja klar... eigentlich ist das a und o erst mal eine saubere Syntax bevor man nur ein logisches Problem programmtechnisch angehen soll. Ich habe versagt. Vielen Dank für deine höffliche Art und Weise.

@Sirius3: Auch dir danke für deine gut gemeinten Tipps zur Formatierung des Codes.
Anführungszeichen sind die falschen - wie wäre es dann richtig? - Ich bin sehr gespannt, weil a) Geht ja b) wie sonst weil c) hab schon viele Varianten durchprobiert (manchmal kommt die Erkenntnis beim Eigenstudium durch try&error).
Datumsformat - wirst lachen, ich forme das datetime-Format von Python mühsam um in das, was m.M. nach SQL erwartet. Auch hier wieder, hab ich mir ergoogelt, hat funktioniert, benutze ich weiter, danke für den Hinweis, dass SQL da wohl so variable ist verschiedenste Formate für datetime korrekt interpretieren zu können, dann spar ich mir das zukünftig. Der Teils mit den Strings... der SQL-connector erwartet halt, so wie ich das bisher überall gesehen hab.
z.B. https://www.python-kurs.eu/sql_python.php:
sql_command = """
CREATE TABLE employee (
staff_number INTEGER PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(30),
gender CHAR(1),
joining DATE,
birth_date DATE);"""

cursor.execute(sql_command)


Ok, hier wird jetzt kein INSERT INTO ausgeführt sondern ein CREATE TABLE, ist trotzdem ein String von vorn bis hinten.

@All: Und so bescheiden wie der Code sein mag, danke für die Tipps, ich verstehe nach wie vor nicht, und keine eurer Beitrage konnte mir das beantworten, warum mein Spaghetty-Dirty-Anfänger-Code nicht funktioniert.
Die Frage, nach wie vor, warum kann ich einen String explizit in execute() reintippe und geht und implizit über ein String-Objekt nicht wenn beide den gleichen Inhalt haben, sofern die print()-Funktion nicht noch irgendwas decodiert. Das nervige ist, in allen Tutorials zu diesem Thema, die ich mir bisher ergoogelt haben (ne Hand voll), wird ein String-Objekt immer direkt "per Hand" ausgefüllt und dann eben so übergeben. Nie wird der String zusammengeschustert (weil wohl unsaubere Art), was dann von einem Objekt verwiesen wird.
Wenn jetzt jemand fundiert sagt, jaaaa das kann Python nicht... weiiiiill sobald man eine String-Operation ausführt ist der String nochmal spziell irgendwas, und dass kann der SQL-connector nicht mehr verarbeiten, dann ok.
Als Newbie, aus dem SPS-Bereich, wo es solche Spässchen wie String-Formatierungsfunktionen einfach gar nicht gibt, was ganz toll ist wenn jemand dann anfängt damit Web-Service-Calls zu machen, wo man sich eben den Text so pervers zusammenbasteln muss, ja da tut man sich jetzt auf diese Quick&Dirty-Weise einfach leichter.
Ich verspreche, ich werde mich auch noch mit der String-Formatierung auseinandersetzten und den SQL-Befehl dann so zu füllen. Aber eben aus Interesse, was läuft schief.. und wehe es kommt jetzt wieder eine mit "ihh der Code ist nicht hübsch".... Schon mal nen Handwerker arbeiten sehen und dann gesagt "das ist aber nicht hübsch"... dann bekommts einen Vortrag was "hübsch" kostet.

Zur Groß-Klein-Schreibung, CamelCase etc... also wenn man nur mit dem Windows-Editor arbeitet absolutes Muss für die Lessbarkeit (für dritte). In einer Programmierumgebung, die Variablen, Strings, Befehle farbig highlightet... da ist die Konvention eigentlich obsolet/outdatet (da mach ich jetzt ein Fass auf, oder? )

[Sirius3]

So, wie du es machst, ist es nur fehlerhaft. Da gibt es kein um den heißen Brei reden. Wie es mit Platzhaltern richtig geht, steht in der Dokumentation von mySQLconnector. Python-kurs.eu ist da wie bei so vielen anderen Dingen nicht gerade die beste Quelle.
Wenn du wirklich wissen willst, was an deinem Code nicht funktioniert, dann postet bitte ein vollständiges Beispiel inklusive Beispieldaten das den Unterschied zeigt. Ich kann nämlich durch deine Zensiererei nicht erkennen, was der wirkliche Unterschied ist.

[__blackjack__]

@SebastianS: Das Problem mit der SQL-Zeichenkette zusammenstückeln ist kein kosmetisches. Das ist ein *Fehler* das zu machen. Du handelst Dir da neben der Fehleranfälligkeit und dem Sicherheitsproblem einfach nur unnötige Arbeit ein. Zum Beispiel das Formatieren eines Datums/Zeitstempels wie die DB das haben will kann sich je nach System- und DB-Einstellungen ändern. Wenn man den Datenbankadapter da ein `datetime`-Objekt einsetzen lässt, dann macht der das automatisch richtig. Ebenso bei Zeichenketten und Anführungsstrichen. Und das auch egal was in der Zeichenkette selbst enthalten ist, ob nun regulär, aus versehen, oder gar mit böser Absicht.

Und nur zur Sicherheit: Wir reden hier bei den Platzhaltern für Werte nicht von Pythons Zeichenkettenformatierung! Das was die von Dir verlinkte Seite das mit ``format()`` macht genau so falsch wie ``+``, denn das ist ja auch Werte selbst in SQL rein formatieren.

`mysql.connector` hat eine Dokumentation. Mit Beispielen und Tutorial. Da wird das richtig gemacht.

[SebastianS]

from datetime import datetime
import mysql.connector

testdb = mysql.connector.connect(
host="localhost",
user="root",
password="pw",
database="testdb"
)
dbcursor = testdb.cursor()

datum = datetime.today()

datum_string = str(datum)

sql_statment = '"' + '"' + '"' + "INSERT INTO tabelle VALUS (" + "'" + datum_string + "'" + ")" + '"' + '"' + '"'

print(sql_statment) # <--> """INSERT INTO tabelle VALUS ('2020-10-07 19:04:48.931364')"""

dbcursor.execute("""INSERT INTO tabelle VALUES ('2020-10-07 18:56:13.810338')""") #<-- geht
testdb.commit()

dbcursor.execute(sql_statment) #<-- geht nicht
testdb.commit()



@__BlackJack__: Werde ich mir anschauen, versprochen. Aber trotzdem, einfaches Beispiel, Tabelle mit nur einer Spalte, hab mal wieder DATETIME verwendet, woran scheiterts? So sehr ich diesen *Fehler* mache, was daran funktioniert nicht?

[__blackjack__]

@SebastianS: Das geht nicht weil eine Zeichenkette die ein Python-Zeichenkettenliteral ist, nicht den gleichen Inhalt hat wie dieses Zeichenkettenliteral:

Code: Alles auswählen

In [15]: print(sql_statement)                                                   
"""INSERT INTO tabelle VALUES ('2020-10-07 20:28:46.640394')"""

In [16]: sql_statement == """INSERT INTO tabelle VALUES ('2020-10-07 20:28:46.64
    ...: 0394')"""                                                              
Out[16]: False

Du vermischt hier zwei Ebenen. Der *Inhalt* Deiner Zeichenkette fängt mit drei " an. Alleine da verschluckt sich die Datenbank dann schon, weil das kein gültiges SQL ist. Wenn Du das dann so in Python-Quelltext eingibst, dann sind die drei " für den *Python-Compiler* das Zeichen, dass da eine literale Zeichenkette anfängt. Der Wert der Zeichenkette enthält aber natürlich diese Begrenzer nicht. Der Wert der Zeichenkette fängt mit INSERT an.

[Sirius3]

Die drei Anführungszeichen kennzeichnen einen literalen String und sind nicht Teil des Strings:

Code: Alles auswählen

sql_statemnt1 = """INSERT INTO tabelle VALUES ('2020-10-07 18:56:13.810338')"""
sql_statment2 = '"' + '"' + '"' + "INSERT INTO tabelle VALUS (" + "'" + datum_string + "'" + ")" + '"' + '"' + '"'
print(sql_statment1)
print(sql_statment2)
dbcursor.execute(sql_statment1)
dbcursor.execute(sql_statment2)

Richtig ist:

Code: Alles auswählen

dbcursor.execute("INSERT INTO tabelle VALUES (%s)", [datum])

[SebastianS]

Danke! Gerade mit einem 2. Python-Laien, aber immerhin Informatiker herausgearbeitet. Jetzt habt ihr exakt die Antwort auf meine Frage geliefert! Ich werde die 150 Zeilen Code drumrum entsprechend euren Hinweisen umgestalten, die sich auf Code-Lesbarkeit und der Nutzung von nativen Datenformaten, anstatt mühsamer, fehleranfälliger Stringgeschubse befassen.