Czy kiedykolwiek musiałeś ręcznie wpisać ogromną instrukcję CREATE TABLE? Taki z dziesiątkami kolumn? Może kilkadziesiąt kolumn? Prawdopodobnie istnieją narzędzia GUI pomocne w przypadku dużych poleceń CREATE TABLE. Lub inne programy typu przeciągnij i upuść, których nie znam. A co, jeśli możesz napisać kilka linijek kodu w Pythonie i zająć się ogromną CREATE TABLE instrukcją przy znacznie mniejszym wysiłku niż wpisane ręcznie? Zainteresowany? Kontynuuj czytanie i zobacz, jak używać pand, pyodbc i MySQL…
Używany system operacyjny, oprogramowanie i baza danych:
- OpenSuse Leap 15.1
- MySQL 8.0.20
- Python 3.7.2
- pandy 1.0.4
Auto-promocja:
Jeśli podoba Ci się treść tutaj napisana, udostępnij tego bloga i swoje ulubione posty innym osobom, którym również może się to przydać lub które mogą im się spodobać. Ponieważ kawa to mój ulubiony napój, możesz mi nawet ją kupić, jeśli chcesz!
Jeśli chcesz, możesz pobrać zestaw danych „Wyniki ankiety dla programistów Stack Overflow” użyty w tym poście do własnej eksploracji.
Na początek zaimportuję pandy do sesji ipython i określę ścieżkę do docelowego pliku CSV z pobranych wyników ankiety Stack Overflow:
In [1]: import pandas as pd In [2]: csv_file = r’/home/joshua/Practice_Data/developer_survey_2019/survey_results_public.csv’
Używając funkcji pandas read_csv(), tworzę DataFrame o nazwie „data_set”. Sama ramka DataFrame udostępnia kilka atrybutów, które możemy wykorzystać w instrukcji CREATE TABLE:
In [3]: data_set = pd.read_csv(csv_file, delimiter=’,’)
Patrząc na atrybut columns, widzimy, że w zaimportowanym pliku CSV znajduje się sporo kolumn:
In [4]: data_set.columns Index([‘Respondent’, ‘MainBranch’, ‘Hobbyist’, ‘OpenSourcer’, ‘OpenSource’, ‘Employment’, ‘Country’, ‘Student’, ‘EdLevel’, ‘UndergradMajor’, ‘EduOther’, ‘OrgSize’, ‘DevType’, ‘YearsCode’, ‘Age1stCode’, ‘YearsCodePro’, ‘CareerSat’, ‘JobSat’, ‘MgrIdiot’, ‘MgrMoney’, ‘MgrWant’, ‘JobSeek’, ‘LastHireDate’, ‘LastInt’, ‘FizzBuzz’, ‘JobFactors’, ‘ResumeUpdate’, ‘CurrencySymbol’, ‘CurrencyDesc’, ‘CompTotal’, ‘CompFreq’, ‘ConvertedComp’, ‘WorkWeekHrs’, ‘WorkPlan’, ‘WorkChallenge’, ‘WorkRemote’, ‘WorkLoc’, ‘ImpSyn’, ‘CodeRev’, ‘CodeRevHrs’, ‘UnitTests’, ‘PurchaseHow’, ‘PurchaseWhat’, ‘LanguageWorkedWith’, ‘LanguageDesireNextYear’, ‘DatabaseWorkedWith’, ‘DatabaseDesireNextYear’, ‘PlatformWorkedWith’, ‘PlatformDesireNextYear’, ‘WebFrameWorkedWith’, ‘WebFrameDesireNextYear’, ‘MiscTechWorkedWith’, ‘MiscTechDesireNextYear’, ‘DevEnviron’, ‘OpSys’, ‘Containers’, ‘BlockchainOrg’, ‘BlockchainIs’, ‘BetterLife’, ‘ITperson’, ‘OffOn’, ‘SocialMedia’, ‘Extraversion’, ‘ScreenName’, ‘SOVisit1st’, ‘SOVisitFreq’, ‘SOVisitTo’, ‘SOFindAnswer’, ‘SOTimeSaved’, ‘SOHowMuchTime’, ‘SOAccount’, ‘SOPartFreq’, ‘SOJobs’, ‘EntTeams’, ‘SOComm’, ‘WelcomeChange’, ‘SONewContent’, ‘Age’, ‘Gender’, ‘Trans’, ‘Sexuality’, ‘Ethnicity’, ‘Dependents’, ‘SurveyLength’, ‘SurveyEase’], dtype=’object’) In [9]: num_cols = len(data_set.axes[1]) In [10]: print(num_cols) 85
Dokładnie 85 kolumn. Wow! To CREATE TABLE stwierdzenie będzie bombowe!
Jak wspomniałem w pierwszym akapicie, ręczne wpisywanie tak dużej instrukcji CREATE TABLE jest kłopotliwe. I niepotrzebne. Przy odrobinie sprytu biblioteki Pythona pandas i pyodbc są w stanie obsłużyć instrukcję CREATE TABLE z nawiązką.
Aby podać liczbę kolumn — w oparciu o liczbę kolumn w pliku CSV — utworzę dowolnego Pythona list ze względu na jego możliwości iterable (zobaczysz dlaczego w dalszej części posta).
In [23]: col_count = [len(l.split(“,”)) for l in data_set.columns] In [24]: print(type(col_count)) <class ‘list’> In [25]: print(len(col_count)) 85
Ostrzegam, nie jestem guru Pythona. Prawdopodobnie istnieją lepsze sposoby w języku Python, które pozwalają osiągnąć to samo. Doświadczeni czytelnicy mogą wskazać mi je w komentarzach poniżej, gdy tylko zobaczą, do czego używam list.
W podejściu, którego używam, jest kilka zawieszeń i chcę tutaj wskazać pierwszy z nich. Aby otrzymać dynamiczną — w pewnym stopniu zautomatyzowaną — instrukcję CREATE TABLE, wszystkie kolumny muszą mieć ten sam typ danych (przynajmniej początkowo). W tym konkretnym przykładzie wybrałem typ danych TEXT, ale VARCHAR również może działać. W zależności od zestawu danych możesz użyć wszystkich INTEGER lub DOUBLE, jeśli pracujesz z danymi ściśle numerycznymi.
Pamiętaj tylko, że niezależnie od tego, jaki typ danych wybierzesz, będziesz musiał albo uruchomić ALTER TABLE instrukcji w MySQL i zaimplementować prawidłowe typy danych dla potrzebnych kolumn po zakończeniu przesyłania. Możesz też przenieść wiersze danych z początkowej tabeli pomostowej do innej stałej tabeli, dokonując w tym procesie rzutowania na typ. ALTER TABLE może być drogi, ponieważ zawiera ponad 80 tys. wierszy danych. Twój przebieg może się różnić
Jak zobaczymy, siła łączenia ciągów jest kluczowa dla polecenia dynamicznego CREATE TABLE, które musimy skonstruować.
Najpierw ustalam polecenie CREATE TABLE i nazwę tabeli – „so_data” – na potrzeby tego przykładu, przechowując je w zmiennej „SQL_CREATE_TBL”:
In [41]: SQL_CREATE_TBL = “CREATE TABLE so_data(“
Następnie za pomocą pętli for dołącz nazwę każdej kolumny – poprzez atrybut pandas columns – wraz z typem danych TEXT do zmiennej „SQL_CREATE_TBL”. Dołączenie ciągu znaków następuje raz dla każdej kolumny występującej w pliku CSV (85) przy użyciu metod range() i len(). Jednocześnie metoda format() string wstawia wartość nazwy column – pochodzącą z atrybutu DataFrame columns:
In [43]: for name in range(0, len(col_count)):
…: SQL_CREATE_TBL += “{} TEXT, “.format(data_set.columns[name])
Przyjrzyjmy się zawartości zmiennej łańcuchowej „SQL_CREATE_TBL” po zakończeniu pętli for:
In [47]: SQL_CREATE_TBL Out[47]: ‘CREATE TABLE so_data(Respondent TEXT, MainBranch TEXT, Hobbyist TEXT, OpenSourcer TEXT, OpenSource TEXT, Employment TEXT, Country TEXT, Student TEXT, EdLevel TEXT, UndergradMajor TEXT, EduOther TEXT, OrgSize TEXT, DevType TEXT, YearsCode TEXT, Age1stCode TEXT, YearsCodePro TEXT, CareerSat TEXT, JobSat TEXT, MgrIdiot TEXT, MgrMoney TEXT, MgrWant TEXT, JobSeek TEXT, LastHireDate TEXT, LastInt TEXT, FizzBuzz TEXT, JobFactors TEXT, ResumeUpdate TEXT, CurrencySymbol TEXT, CurrencyDesc TEXT, CompTotal TEXT, CompFreq TEXT, ConvertedComp TEXT, WorkWeekHrs TEXT, WorkPlan TEXT, WorkChallenge TEXT, WorkRemote TEXT, WorkLoc TEXT, ImpSyn TEXT, CodeRev TEXT, CodeRevHrs TEXT, UnitTests TEXT, PurchaseHow TEXT, PurchaseWhat TEXT, LanguageWorkedWith TEXT, LanguageDesireNextYear TEXT, DatabaseWorkedWith TEXT, DatabaseDesireNextYear TEXT, PlatformWorkedWith TEXT, PlatformDesireNextYear TEXT, WebFrameWorkedWith TEXT, WebFrameDesireNextYear TEXT, MiscTechWorkedWith TEXT, MiscTechDesireNextYear TEXT, DevEnviron TEXT, OpSys TEXT, Containers TEXT, BlockchainOrg TEXT, BlockchainIs TEXT, BetterLife TEXT, ITperson TEXT, OffOn TEXT, SocialMedia TEXT, Extraversion TEXT, ScreenName TEXT, SOVisit1st TEXT, SOVisitFreq TEXT, SOVisitTo TEXT, SOFindAnswer TEXT, SOTimeSaved TEXT, SOHowMuchTime TEXT, SOAccount TEXT, SOPartFreq TEXT, SOJobs TEXT, EntTeams TEXT, SOComm TEXT, WelcomeChange TEXT, SONewContent TEXT, Age TEXT, Gender TEXT, Trans TEXT, Sexuality TEXT, Ethnicity TEXT, Dependents TEXT, SurveyLength TEXT, SurveyEase TEXT, ‘
Wszystkie nazwy kolumn posiadające typ danych TEXT są teraz częścią ciągu. Jednak jest jeszcze trochę do zrobienia. Ciąg „SQL_CREATE_TBL” ma końcowy przecinek po słowie kluczowym TEXT dla ostatniej kolumny „SurveyEase”. Jeśli pozostawisz go tak, jak jest, ten końcowy przecinek zgłosi błąd w MySQL, jeśli zostanie wykonany.
Możemy użyć metody rstring() i usunąć końcowy przecinek:
In [48]: SQL_CREATE_TBL = SQL_CREATE_TBL.rstrip(“ ,”)
Aby zakończyć instrukcję CREATE TABLE, wystarczy po prostu dodać nawias zamykający i średnik do zmiennej „SQL_CREATE_TBL”:
In [49]: SQL_CREATE_TBL += “);”
Zmienna „SQL_CREATE_TBL” jest teraz kompletna i przechowuje pełną instrukcję CREATE TABLE:
In [50]: SQL_CREATE_TBL Out[50]: ‘CREATE TABLE so_data(Respondent TEXT, MainBranch TEXT, Hobbyist TEXT, OpenSourcer TEXT, OpenSource TEXT, Employment TEXT, Country TEXT, Student TEXT, EdLevel TEXT, UndergradMajor TEXT, EduOther TEXT, OrgSize TEXT, DevType TEXT, YearsCode TEXT, Age1stCode TEXT, YearsCodePro TEXT, CareerSat TEXT, JobSat TEXT, MgrIdiot TEXT, MgrMoney TEXT, MgrWant TEXT, JobSeek TEXT, LastHireDate TEXT, LastInt TEXT, FizzBuzz TEXT, JobFactors TEXT, ResumeUpdate TEXT, CurrencySymbol TEXT, CurrencyDesc TEXT, CompTotal TEXT, CompFreq TEXT, ConvertedComp TEXT, WorkWeekHrs TEXT, WorkPlan TEXT, WorkChallenge TEXT, WorkRemote TEXT, WorkLoc TEXT, ImpSyn TEXT, CodeRev TEXT, CodeRevHrs TEXT, UnitTests TEXT, PurchaseHow TEXT, PurchaseWhat TEXT, LanguageWorkedWith TEXT, LanguageDesireNextYear TEXT, DatabaseWorkedWith TEXT, DatabaseDesireNextYear TEXT, PlatformWorkedWith TEXT, PlatformDesireNextYear TEXT, WebFrameWorkedWith TEXT, WebFrameDesireNextYear TEXT, MiscTechWorkedWith TEXT, MiscTechDesireNextYear TEXT, DevEnviron TEXT, OpSys TEXT, Containers TEXT, BlockchainOrg TEXT, BlockchainIs TEXT, BetterLife TEXT, ITperson TEXT, OffOn TEXT, SocialMedia TEXT, Extraversion TEXT, ScreenName TEXT, SOVisit1st TEXT, SOVisitFreq TEXT, SOVisitTo TEXT, SOFindAnswer TEXT, SOTimeSaved TEXT, SOHowMuchTime TEXT, SOAccount TEXT, SOPartFreq TEXT, SOJobs TEXT, EntTeams TEXT, SOComm TEXT, WelcomeChange TEXT, SONewContent TEXT, Age TEXT, Gender TEXT, Trans TEXT, Sexuality TEXT, Ethnicity TEXT, Dependents TEXT, SurveyLength TEXT, SurveyEase TEXT);’
Cała struktura kodu instrukcji dynamicznej CREATE TABLE jest pokazana poniżej:
SQL_CREATE_TBL = “CREATE TABLE so_data(“
for name in range(0, len(col_count)):
SQL_CREATE_TBL += “{} TEXT, “.format(data_set.columns[name])
SQL_CREATE_TBL = SQL_CREATE_TBL.rstrip(“ ,”)
SQL_CREATE_TBL += “);”
Z pewnością lepsze to niż ręczne wpisywanie tego cholernego CREATE TABLE oświadczenia!
Teraz musimy faktycznie wykonać polecenie CREATE TABLE w instancji MySQL, używając zmiennej „SQL_CREATE_TBL”. Istnieje wiele sterowników/złączy Pythona, za pomocą których można uzyskać dostęp do bazy danych MySQL. Najbardziej znam sterownik pyodbc, więc tego właśnie będę używał do przesyłania.
W tej samej ipython sesji importuję pyodbc, ustanawiam połączenie i obiekt kursora oraz przenoszę do bazy danych „learning” za pomocą USE learning instrukcji:
In [52]: import pyodbc
In [53]: conn = pyodbc.connect(“””DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATBASE=learning;USER=my_user;PASSWORD=my_password;”””)
In [54]: cur = conn.cursor()
In [56]: cur.execute(“USE learning”)
Out[56]: <pyodbc.Cursor at 0x7f89531615d0>
Na początek wykonajmy polecenie CREATE TABLE, używając zmiennej „SQL_CREATE_TBL”:
In [57]: try: …: cur.execute(SQL_CREATE_TBL) …: except pyodbc.DatabaseError as e: …: raise e …: cur.rollback() …: else: …: cur.commit() …:
Po ukończeniu sprawdzę, czy tabela „so_data” istnieje w bazie danych „learning”:
MySQL localhost:33060+ ssl learning SQL > SHOW TABLES IN learning LIKE ‘%so_data%’; + — — — — — — — — — — — — — — — — + | Tables_in_learning (%so_data%) | + — — — — — — — — — — — — — — — — + | so_data | + — — — — — — — — — — — — — — — — + 1 row in set (0.0027 sec)
Wykonując zapytanie do tabeli COLUMNS w bazie danych information_schema, możemy zobaczyć, że utworzono wszystkie 85 kolumn:
MySQL localhost:33060+ ssl information_schema SQL > SELECT COUNT(*) as column_count -> FROM COLUMNS -> WHERE table_schema = ‘learning’ -> AND table_name = ‘so_data’; + — — — — — — — + | column_count | + — — — — — — — + | 85 | + — — — — — — — + 1 row in set (0.0034 sec)
Dla wszystkich wartości kolumn wierszy CSV potrzebujemy sparametryzowanego zapytania do użycia w instrukcji INSERT. Ponownie możemy wykorzystać atrybut pandy columns w celu uzyskania prawidłowej liczby wartości zastępczych parametrów „?”. Następnie dołącz te symbole zastępcze do INSERT INTO zapytania:
In [59]: placeholders = “,”.join(“?” * len(data_set.columns))
In [61]: SQL_INSERT = “INSERT INTO so_data VALUES({});”.format(placeholders)
Aby kontynuować, potrzebujemy funkcjonalności CSV, więc zaimportuję również wbudowany moduł csv:
In [62]: import csv
Wreszcie doszliśmy do chwili prawdy; przesyłanie…
OSTRZEŻENIE!!!
Nie jest to w żadnym wypadku szybka operacja i zajmie trochę czasu… W pliku danych CSV znajduje się ponad 88 tys. wierszy, co wymaga wielu INSERT.
Z prób i udręk wiemy, że nie wszystko jest w naszych rękach i pod naszą kontrolą. Dla bezpieczeństwa powinniśmy pracować w ramach jakiejś formy transakcji. Aby to zrobić, ustawię autocommit na false w połączeniu pyodbc (domyślnie autocommit jest ustawione na True) i hermetyzuję cały przesyłany blok w bloku try/except/else/finally. Jeśli coś pójdzie nie tak podczas przesyłania, wszystkie INSERT, które zostały ukończone, zostaną wycofane i żadne dane nie zostaną zapisane – ani zniekształcone.
Tutaj idzie…
In [63]: try: …: conn.autocommit = False …: with open(csv_file, ‘r’) as f_csv: …: csv_reader = csv.reader(f_csv, delimiter=’,’) …: next(csv_reader) #advancing past the first row of header column names …: cur.executemany(SQL_INSERT, csv_reader) …: conn.commit() …: except pyodbc.DatabaseError as e: …: raise e …: conn.rollback() …: else: …: conn.commit() …: finally: …: conn.autocommit = True
Po zakończeniu wykonywania w tabeli MySQL „so_data” możemy zobaczyć, że całkowita liczba wierszy wynosi 88883:
MySQL localhost:33060+ ssl learning SQL > SELECT COUNT(*) FROM so_data; + — — — — — + | COUNT(*) | + — — — — — + | 88883 | + — — — — — + 1 row in set (0.2939 sec)
Przesyłanie zakończyło się pomyślnie.
O czym warto pamiętać…
- Jeżeli docelowy plik CSV nie posiada wiersza nagłówka zawierającego nazwy kolumn, kod, który zaprezentowałem w poście, nie będzie działać.
- Przesyłanie zaprezentowane tutaj nie jest szybkie ani zoptymalizowane. To jest wiele, wiele wkładek wystrzeliwujących. (Mój laptop prawdopodobnie też nie jest najlepszym środowiskiem testowym.)
- Pamiętaj, że aby ten skrypt zadziałał, typy danych wszystkich kolumn muszą być ustawione na wspólny typ, a po zakończeniu przesyłania może być konieczne
ALTERniektórych kolumn według własnych potrzeb. - Rozważ wszystkie opcje. Dynamiczna instrukcja
CREATE TABLEmoże być mało użyteczna, jeśli trzeba spędzić 3 razy więcej czasu na wykonywaniu instrukcjiALTER TABLEw celu zaimplementowania odpowiednich typów danych kolumn.
Mamy nadzieję, że znajdziesz zastosowanie dla tego typu dynamicznego polecenia CREATE TABLE. Użyłem go, tworząc prototypy kilku tabel MySQL dla konkretnego zestawu danych, który mnie interesował. Daj mi znać, co myślisz w komentarzach poniżej i dziękuję za przeczytanie!
Pyodbc CRUD
Jeśli jesteś zainteresowany używaniem sterownika pyodbc z MySQL, napisałem 4-częściową serię na temat operacji CRUD, które uznasz za cenne.
- Pyodbc SQL CRUD — Tworzenie: Przykłady z MySQL
- Pyodbc SQL CRUD — Przeczytaj: Przykłady z MySQL
- Pyodbc SQL CRUD — aktualizacja: przykłady z MySQL
- Pyodbc SQL CRUD — Usuń: Przykłady z MySQL
Podoba Ci się to, co przeczytałeś? Widzisz coś nieprawidłowego? Proszę o komentarz poniżej i dziękuję za przeczytanie!!!
Wezwanie do działania!
Dziękujemy za poświęcenie czasu na przeczytanie tego posta. Mam nadzieję, że odkryłeś coś interesującego i pouczającego. Podziel się tutaj swoimi odkryciami z kimś, kogo znasz, kto również odniesie z tego taką samą wartość.
Odwiedź stronę Portfolio-Projekty, aby zobaczyć wpis na blogu/pisma techniczne, które wykonałem dla klientów.
Wspominałem już, jak bardzo uwielbiam filiżankę kawy?!?!
Aby otrzymywać powiadomienia e-mailem (Nigdy nie spamuj) z tego bloga („Cyfrowa proza sowy”) o najnowszych postach na blogu w momencie ich publikacji, zasubskrybuj (z własnej woli), klikając przycisk „Kliknij, aby subskrybować!” na pasku bocznym strona główna! (W każdej chwili możesz zapoznać się ze stroną Polityki prywatności Digital Owl’s Prose, aby uzyskać odpowiedzi na wszelkie pytania dotyczące: aktualizacji e-maili, rejestracji, rezygnacji, formularzy kontaktowych itp.)
Koniecznie odwiedź stronę „Najlepsze z” i zapoznaj się z kolekcją moich najlepszych postów na blogu.
Josh Otwell ma pasję do nauki i rozwoju jako programista SQL i bloger. Podczas innych ulubionych zajęć siedzi z nosem w dobrej książce, artykule lub wierszu poleceń Linuksa. Podziela między innymi miłość do planszowych gier RPG, czytania powieści fantasy i spędzania czasu z żoną i dwiema córkami.
Zastrzeżenie: Przykłady przedstawione w tym poście to hipotetyczne pomysły na osiągnięcie podobnych wyników. Nie są one najlepszym rozwiązaniem. Większość, jeśli nie wszystkie, przedstawionych przykładów jest wykonywana w środowisku stacji roboczej do rozwoju osobistego/uczenia się i nie należy ich uważać za jakość produkcyjną ani za gotowe. Twoje konkretne cele i potrzeby mogą się różnić. Stosuj te praktyki, które najlepiej przynoszą korzyści Twoim potrzebom i celom. Opinie są moimi własnymi.Pierwotnie opublikowano na stronie https://joshuaotwell.com 8 lipca 2020 r.
