SQLAlchemy: Groß- und Kleinschreibung

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

Hallo Leute,

mir ist durch Zufall etwas aufgefallen. Im nachstehenden Beispiel sehen wir, dass ich mittels SQLAlchemy ORM eine Tabelle anlege und das Feld "tabelle" auf unique setze - doppelte Einträge sollten nicht mehr möglich sein. Durch meine Unachtsamkeit bin ich erst darauf aufmerksam geworden. Ich habe einmal den Buchstabe "A" und den kleinen Buchstaben "a". eingegeben. In meiner Vorstellung nahm ich an, dass die dahinterliegende Datenbank (SQLite) erkennt, dass es sich hierbei um einen Doppeleintrag handelt und ich somit von SQLAlchemy eine Exception erwarte. Fehlanzeige. SQLite hat ohne Anstand nun beide Buchstaben angenommen. Nun suche ich - bisher vergebens - wie ich auf der ORM-Ebene SQLAlchemy sagen kann, dass er mir SQLite soweit bringen soll, dass "A" und "a" Doppel sind. Eine Möglichkeit bestünde darin, vor jedem Speichern danach zu suchen. Dies wollte ich mir eigentlich ersparen.

Code: Alles auswählen

class MeineTabelle(Base):

    __tablename__ = "meine_tabelle"

    id = Column(Integer, primary_key = True, unique = True, autoincrement = True)
    tabelle = Column(String(50), nullable = False, unique = True)
Zwei Frage: (1): Hatte ich bisher die falsche Vorstellung, dass die Datenbank keinen Unterschied von "A" und "a" macht? Das Python einen Unterschied macht, ist mir bewusst, aber wir reden ja von der Datenbank. (2) Gibt es eine Möglichkeit, dies auf der ORM-Ebene zu lösen?
__deets__
User
Beiträge: 14494
Registriert: Mittwoch 14. Oktober 2015, 14:29

Wieso sollte denn eine Datenbank da keinen Unterschied machen? "Wenn Hessen in Essen essen gehen" haette dann ja nur 5 statt der notwendingen 6 eindeutigen Worte. Das waere hochgradig sinnenstellend.

Der allgemeine Begriff fuer das was du suchst ist "collation", und beschreibt die Art, wie die Datenbank zb sortiert (im schwedischen glaube ich sind zB Umlaute NACH Z, nicht wie im deutschen nach den Vokalen, etc). Auch SQLITE kann das, https://stackoverflow.com/questions/209 ... -in-sqlite

Und da ist auch deine Loesung. Ich bin mir sicher SQLAlchemy hat einen Weg, das zu kommunizieren. Entweder gibt es das schon vordefiniert, und dann sorgt der Dialekt dafuer. Oder du musst den Constraint "raw" uebergeben. Das ist zwar schade, aber gelegentlich leider unumgaenglich weil fuer bestimmte Features Datenbanken zu unterschiedlich sind. Aber ich wuerde mal nach sqlalchemy und collation schauen.
Benutzeravatar
__blackjack__
User
Beiträge: 13004
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

Datenbanken machen diesen Unterschied. Warum auch nicht? SQL macht normalerweise keinen Unterschied bei Schlüsselworten und zumindest laut Standard auch nicht bei Bezeichnern, aber bei Daten schon, denn das macht ja in der Regel schon einen Unterschied ob man etwas gross oder klein schreibt.

Statt vorher zu suchen, könntest Du auch alles in Kleinbuchstaben (oder Grossbuchstaben) wandeln vor'm Eintragen/Suchen. Das kann man sicher auch auf ORM-Ebene lösen. Augmenting Existing Types könnte da ein Ansatz sein.
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

@__blackjack__: Bist du der "echte" BlackJack oder ein anderer? Ich frage nur, weil der BlackJack, den ich hier im Forum "kenne" nicht mehr existiert.

Meine Vorstellung ging in folgende Richtung: In meinem Beispiel ist das Feld "tabelle" auf unique gesetzt, und demzufolge dient dieses Feld auch als Index, korrekt? Und gerade im Zuge der Indexierung ging ich naiv davon aus, dass absolut darauf geachtet wird, ob wirklich alles einmalig ist. Im Umkehrschluss: Angenommen, ich habe in die Datenbank "PyThOn" gespeichert, und nun möchte ich nach diesem Datensatz suchen. Wenn ich in der Datenbank "PYTHON" oder "python" eingebe, wird sie mir "PyThOn" finden und ausgeben.

@__deets__: Auf der SQL-Ebene könnte man mit dieser COLLATION lösen. Ich habe nur gehofft, dass ich nicht vom high-Level wieder zum low-Level runter muss.
__deets__
User
Beiträge: 14494
Registriert: Mittwoch 14. Oktober 2015, 14:29

Das ist nicht high-level to low-level. Du benutzt ein ORM. Das basiert auf einer SQL-Datenbank. So zu tun als ob dem nicht so waere ist nicht sinnvoll. Die Datenbank macht viele Dinge fuer dich wie zB unique constraints zu erzwingen. Die kannst du auch in deinem Python-Modell umsetzten, wenn du das willst. Tust du aber nicht. Viele andere Eigenheiten einer DB "leaken" ebenfalls durch, wie zB Transaktionen und deren commit-level, etc. Aber collations ist ploetzlich eine unueberwindbare Huerde? Das solltest du besser begruenden.

Eine der Staerken von SQLAlchemy ist es, einfache Dinge einfach, und komplizierte Dinge moeglich zu machen. Es so zu nutzen ist also nicht im Widerspruch zu sich selbst.
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

@__deets__: Also du "raw" geschrieben hast, ging ich davon aus, dass ich mich auf die nackte SQL-Sprache konzentrieren soll, also weg von SQLAlchemy ORM, daher ging vom High-Lever zu Low-Level aus.

Ich muss gleich mal nachschlagen, was "constraints" bedeuten.
__deets__
User
Beiträge: 14494
Registriert: Mittwoch 14. Oktober 2015, 14:29

Constraints sind Randbedingungen, die erfuellt werden muessen. UNIQUE ist ein constraint, PRIMARY KEY ein anderer. Und das kann je nach DB beliebig kompliziert werden (wenn die scripting erlaubt). Fuer viele Dinge hat SQLAlchemy da schon was nettes eingebaut, so das du nur "unique=True" uebergeben musst, und das entsprechende DDL wird erzeugt. Bei anderen kannst und musst du an SQLAlchemy vorbei sagen 'dies ist ein constraint den du nicht kennst, und der sieht so aus", und dann reicht SQLAlchemy das unveraendert (raw) durch. Deswegen ist dein Modell aber immer noch in SQLAlchemy deklariert. Und lediglich die Portierbarkeit leidet ein bisschen. Ob dem so ist, oder ob SA da ggf. schon eine collation-Abstraktion hat kann man der Dokumentation entnehmen. Und wenn nicht, wie man den "raw" constraint macht.
Benutzeravatar
__blackjack__
User
Beiträge: 13004
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@Sophus: Also ich fühle mich selbst sowohl echt als auch existent. Kann aber natürlich sein das ich ein Replikant bin der sich dessen nicht bewusst ist. :-D

UNIQUE zieht in der Regel einen Index nach sich. Ich weiss gerade nicht ob SQL das garantiert, aber es garantiert dann auf jeden Fall das die Werte in der Spalte eindeutig sind. Oder NULL, falls das nicht auch noch weiter eingeschränkt ist. Und natürlich sind 'PyThOn', 'PYTHON', und 'python' drei verschiedene Werte die alle in so einer UNIQUE-Spalte existieren können.
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

Dann werde ich wohl oder übel zu Plan B greifen. Meine Idee ist, dass ich, bevor ein Datensatz gespeicherte wird, alle Datensätze der UNIQUE-Spalte mittel der List Comprehension in die neue Liste lade - natürlich mit lower(). Anschließend wird der zu speichernde Datensatz ebenfalls temporär mit lower() behandelt, dann in die eben erstellte lokale Liste schauen, ob es diesen besagten Eintrag gibt, wenn nicht, dann wird gespeichert.

Man könnte auch __blackjack__s Rat berücksichtigen, indem man die zu speichernden Datensätze allesamt mit lower() behandelt und in die UNIQUE-Spalte speichert. Nur mag ich nicht bevormundent wirken, wenn der Benutzer "PyThOn" speichern will, ich aber diesen Eintrag als "python" speichere. Zumal es Markennamen, Firmennamen etc. gibt, bei denen sich die Groß- und Kleinbuchstaben in einem Wort wechseln. Man denke an iPhone, BAföG, PhD, AfD (ich distanziere mich ausdrücklich von AfD), StudIP etc.
Sirius3
User
Beiträge: 17711
Registriert: Sonntag 21. Oktober 2012, 17:20

Du willst es also extra kompliziert Programmieren, indem Du gar nicht die Datenbank benutzt, statt Dich ein bißchen über COLLATE zu informieren.
Obwohl ich das Problem noch nie hatte, hat es nur 5 Minuten Recherche gebraucht, bis ich die passenden SQLAlchemy-Aufruf hatte, zumal __deets__ ja schon die passenden Stichworte geliefert hat. Funktioniert wunderbar.
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

@Sirius3: Die StackOverFlow-Seite, die __deets__ vorgestellt hat, enthält nur die DDL, also:

Code: Alles auswählen

CREATE TABLE users (
  email TEXT PRIMARY KEY,
  password TEXT NOT NULL CHECK(password<>''),
  UNIQUE (email COLLATE NOCASE)
)
.

Ein COLLATE habe ich auf ORM-Ebene nicht gefunden.
Wenn ich versuche, auf ORM die COLLATION anzuwenden, wie hier:

Code: Alles auswählen

class MeineTabelle(Base):

    __tablename__ = "meine_tabelle"

    id = Column(Integer, primary_key = True, unique = True, autoincrement = True)
    tabelle = Column(String(50), collation='utf8_bin', nullable = False, unique = True)
Bekomme ich folgende Meldung:
File "build\bdist.win32\egg\sqlalchemy\sql\schema.py", line 1257, in __init__
sqlalchemy.exc.ArgumentError: Unknown arguments passed to Column: ['collation']
Zuletzt geändert von Sophus am Montag 4. Juni 2018, 19:03, insgesamt 1-mal geändert.
__deets__
User
Beiträge: 14494
Registriert: Mittwoch 14. Oktober 2015, 14:29

Du beschreitest hier den denkbar unguenstigsten Weg. Durch deine Entscheidung kannst du zB bei suchen innerhalb der Datenbank nicht mehr so ohne weiteres mit Indizes arbeiten, weil du entweder erst gar nicht dort suchen kannst (PyThoN finden mit python als Eingabe geht dann nicht), oder gar Dinge wie "select lower(name) from ..." machen musst. Das geht zwar, verhindert aber die Nutzung eines Index.

Ich wuerde dir da sehr deutlich abraten, das von Hand zu machen.
__deets__
User
Beiträge: 14494
Registriert: Mittwoch 14. Oktober 2015, 14:29

Sophus hat geschrieben: Montag 4. Juni 2018, 18:54 Ein COLLATE habe ich auf ORM-Ebene nicht gefunden.
https://stackoverflow.com/questions/152 ... properties
Sirius3
User
Beiträge: 17711
Registriert: Sonntag 21. Oktober 2012, 17:20

@__deets__: es gibt sogar eine stackoverflow-Antwort, die collation und NOCASE für SQLite3 beschreibt.
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

Ich habe mal meine eine Tabelle testweise modifiziert:

Code: Alles auswählen

class FilmProductionCompany(Base):

    __tablename__ = "film_production_company"

    id = Column(Integer, primary_key = True, unique = True, autoincrement = True)
    production_company = Column(String(100, collation='utf8_bin'), nullable = False, unique = True)
Das Ergebnis seht ihr dann im TraceBack:
File "D:\Dan\Python\Xarphus\xarphus\frm_db_login.py", line 133, in log_in_db
admin_database=self.admin_database) as engine:
File "D:\Dan\Python\Xarphus\xarphus\core\managed_engine.py", line 54, in __init__
self.check_adminsitrate_database()
File "D:\Dan\Python\Xarphus\xarphus\core\managed_engine.py", line 137, in check_adminsitrate_database
self.data_base_exists()
File "D:\Dan\Python\Xarphus\xarphus\core\managed_engine.py", line 158, in data_base_exists
self.create_data_base()
File "D:\Dan\Python\Xarphus\xarphus\core\managed_engine.py", line 100, in create_data_base
self.create_tables(connection_obj=conn_obj)
File "D:\Dan\Python\Xarphus\xarphus\core\managed_engine.py", line 80, in create_tables
Base.metadata.create_all(connection_obj)
File "build\bdist.win32\egg\sqlalchemy\sql\schema.py", line 3934, in create_all
tables=tables)
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 1538, in _run_visitor
**kwargs).traverse_single(element)
File "build\bdist.win32\egg\sqlalchemy\sql\visitors.py", line 121, in traverse_single
return meth(obj, **kw)
File "build\bdist.win32\egg\sqlalchemy\sql\ddl.py", line 733, in visit_metadata
_is_metadata_operation=True)
File "build\bdist.win32\egg\sqlalchemy\sql\visitors.py", line 121, in traverse_single
return meth(obj, **kw)
File "build\bdist.win32\egg\sqlalchemy\sql\ddl.py", line 767, in visit_table
include_foreign_key_constraints=include_foreign_key_constraints
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 945, in execute
return meth(self, multiparams, params)
File "build\bdist.win32\egg\sqlalchemy\sql\ddl.py", line 68, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 1002, in _execute_ddl
compiled
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 1189, in _execute_context
context)
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 1402, in _handle_dbapi_exception
exc_info
File "build\bdist.win32\egg\sqlalchemy\util\compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 1182, in _execute_context
context)
File "build\bdist.win32\egg\sqlalchemy\engine\default.py", line 470, in do_execute
cursor.execute(statement, parameters)
OperationalError: (sqlite3.OperationalError) no such collation sequence: utf8_bin [SQL: u'\nCREATE TABLE film_production_company (\n\tid INTEGER NOT NULL, \n\tproduction_company VARCHAR(100) COLLATE "utf8_bin" NOT NULL, \n\tPRIMARY KEY (id), \n\tUNIQUE (id), \n\tUNIQUE (production_company)\n)\n\n']
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

Ok, ich musste feststellen, dass gerade im Hinblick auf SQLite keine benutzerdefinierte Sortierung unterstützt wird. Und utf8_unicode_ci gilt eben als solches.

Aber hiermit klappt es:

Code: Alles auswählen

class FilmProductionCompany(Base):

    __tablename__ = "film_production_company"

    id = Column(Integer, primary_key = True, unique = True, autoincrement = True)
    production_company = Column(String(100, collation='NOCASE'), nullable = False, unique = True)
Ich hoffe auch, dass dies alles auf UTF8 bleibt, da ich es sonst, wie gewohnt, immer davon ausgehe, man müsse es der Datenbank explizit mitteilen, in welchem Format hier sortiert werden solle.

UPDATE
Bei Umlaute ist alles wieder beim Alten. Ich kann Ä und ä abspeichern. Bei A und a macht die Datenbank ihre erwünschte Arbeit.
Benutzeravatar
__blackjack__
User
Beiträge: 13004
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

Ist `production_company` tatsächlich UNIQUE? Keine Chance das in verschiedenen Ländern oder in verschiedenen Zeiträumen unterschiedliche Produktionsfirmen mit dem gleichen Namen existieren können? Oder welche bei denen sich der Name nur durch Gross-/Kleinschreibung unterscheidet?
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

@__blackjack__: Ja, production_company ist tatsächlich UNIQUE. Hintergrund: In meinem Programm werden einmalig Stammdaten angelegt, darunter fällt auch diese Sparte. Es ist also beabsichtigt. Ich möchte Redundanz vermeiden - daher dieser Weg.

Zurück zum Problem: Ich fand auf der Seite SQLite Collating Sequences folgende Anmerkung:
NOCASE - It is almost same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed.
Auf gut Deutsch: Sonderzeichen und Umlaute werden nicht berücksichtigt.
__deets__
User
Beiträge: 14494
Registriert: Mittwoch 14. Oktober 2015, 14:29

Das koennte unter Umstaenden mit der entsprechenden Normalform loesen. https://en.wikipedia.org/wiki/Unicode_e ... characters

Dadurch steht da dann wirklich ASCII-A, und danach ein UTF-8-Codepoint der das zu einem Umlaut kombiniert, und der nicht angefasst wird.
Benutzeravatar
Sophus
User
Beiträge: 1109
Registriert: Freitag 25. April 2014, 12:46
Wohnort: Osnabrück

@__deets__: Wie bitte?
Antworten