Ați fost nevoit vreodată să scrieți manual o declarație masivă CREATE TABLE? Una cu zeci de coloane? Poate câteva zeci de coloane? Există probabil câteva instrumente GUI care să ajute cu comenzile mari CREATE TABLE. Sau alte tipuri de software drag-n-drop cu care nu sunt familiarizat. Ce se întâmplă dacă ai putea scrie câteva rânduri de cod Python și ai avea grijă de o declarație CREATE TABLE uriașă cu mult mai puțin efort decât tastarea manual? Interesat? Continuați să citiți și vedeți cum folosiți panda, pyodbc și MySQL...

Sistem de operare, software și DB utilizate:

  • OpenSuse Leap 15.1
  • MySQL 8.0.20
  • Python 3.7.2
  • panda 1.0.4

Promotie personala:

Dacă vă place conținutul scris aici, prin toate mijloacele, împărtășiți acest blog și postările dvs. preferate altora cărora le-ar putea beneficia sau le place. „Deoarece cafeaua este băutura mea preferată, poți chiar să-mi cumperi una dacă vrei!”

Dacă doriți, puteți descărca setul de date „Rezultatele sondajului pentru dezvoltatori Stack Overflow” folosit în această postare pentru propria dvs. explorare.

Pentru a începe, voi importa panda într-o sesiune ipython și voi specifica calea către CSV țintă din descărcarea rezultatelor sondajului Stack Overflow:

In [1]: import pandas as pd
In [2]: csv_file = r’/home/joshua/Practice_Data/developer_survey_2019/survey_results_public.csv’

Folosind funcția pandas read_csv(), creez un DataFrame numit „data_set”. DataFrame în sine oferă mai multe atribute pe care le putem utiliza pentru instrucțiunea CREATE TABLE:

In [3]: data_set = pd.read_csv(csv_file, delimiter=’,’)

Privind atributul columns, putem vedea că există destul de multe coloane în CSV importat:

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

85 de coloane mai exact. Wow! Acea afirmație CREATE TABLE va fi o groază!

După cum am menționat în paragraful de deschidere, introducerea manuală a unei declarații atât de mari CREATE TABLE este greoaie. Și, inutil. Cu un puțin de înțelepciune, bibliotecile panda și pyodbc Python sunt mai mult decât capabile să gestioneze instrucțiunea CREATE TABLE.

Pentru a furniza numărul de coloane - pe baza numărului de coloane din fișierul CSV - voi crea un python arbitrar list pentru capabilitățile sale iterable (veți vedea de ce mai târziu în postare).

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

Fii avertizat, nu sunt un guru al Python-ului. Există probabil mai bune, căi pitonice care realizează același lucru. Cititorii pricepuți nu sunt liberi să mi le sublinieze în comentariile de mai jos, după ce vedeți pentru ce folosesc list.

Există câteva încălcări în abordarea pe care o folosesc și vreau să o subliniez pe prima aici. Pentru a avea o instrucțiune dinamică — automatizată într-o anumită măsură — CREATE TABLE, toate coloanele trebuie să aibă același tip de date (cel puțin inițial). Pentru acest exemplu special, am ales tipul de date TEXT, dar ar putea funcționa și un VARCHAR. În funcție de setul de date, puteți folosi toate INTEGER sau DOUBLE dacă lucrați cu date strict numerice.

Rețineți că, indiferent de tipul de date pe care îl decideți, va trebui fie să rulați ALTER TABLE declarații în MySQL și să implementați tipurile de date corecte pentru coloanele necesare odată ce încărcarea se încheie. Sau, mutați rândurile de date dintr-un tabel inițial de etalon într-un alt tabel permanent, transcriindu-le în acel proces. ALTER TABLE ar putea fi costisitoare, deoarece există peste 80.000 de rânduri de date. Kilometrajul dvs. poate varia

După cum vom vedea, puterea concatenării șirurilor este crucială pentru comanda dinamică CREATE TABLE pe care trebuie să o construim.

Mai întâi, stabilesc comanda CREATE TABLE și numele tabelului – „so_data” – pentru acest exemplu, stochându-l într-o variabilă „SQL_CREATE_TBL”:

In [41]: SQL_CREATE_TBL = “CREATE TABLE so_data(“

Apoi, folosind o buclă for, adăugați fiecare nume de coloană – prin atributul pandas columns – împreună cu tipul de date TEXT la variabila „SQL_CREATE_TBL”. Adăugarea șirului apare o dată pentru fiecare dintre numărul de coloane prezente în fișierul CSV (85), prin utilizarea metodelor range() și len(). Simultan, metoda șirului format() inserează valoarea numelui column - derivată din atributul DataFrame columns:

In [43]: for name in range(0, len(col_count)):
 …: SQL_CREATE_TBL += “{} TEXT, “.format(data_set.columns[name])

Să vedem conținutul variabilei șir „SQL_CREATE_TBL” odată ce bucla for se încheie:

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, ‘

Toate numele coloanelor care au tipul de date TEXT fac acum parte din șir. Cu toate acestea, există câteva curățare de făcut. Șirul „SQL_CREATE_TBL” are o virgulă după cuvântul cheie TEXT pentru ultima coloană, „SurveyEase”. Dacă este lăsată așa, acea virgulă finală va genera o eroare în MySQL dacă este executată.

Putem folosi metoda rstring() și putem elimina virgula finală:

In [48]: SQL_CREATE_TBL = SQL_CREATE_TBL.rstrip(“ ,”)

Tot ceea ce a mai rămas pentru a finaliza instrucțiunea CREATE TABLE este pur și simplu să adăugați paranteza de închidere și punct și virgulă la variabila „SQL_CREATE_TBL”:

In [49]: SQL_CREATE_TBL += “);”

Variabila „SQL_CREATE_TBL” este acum completă, stochând instrucțiunea CREATE TABLE completă:

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);’

Întreaga structură de cod pentru instrucțiunea dinamică CREATE TABLE este prezentată mai jos:

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 += “);”

Sigur, este mai bine să tastați cu mâna acel grozav de declarație CREATE TABLE!

Acum trebuie să executăm de fapt comanda CREATE TABLE în instanța MySQL folosind variabila „SQL_CREATE_TBL”. Există numeroase drivere/conectori Python cu care puteți accesa o bază de date MySQL. Sunt cel mai familiarizat cu driverul pyodbc, așa că acesta este cel pe care îl voi folosi pentru încărcare.

În aceeași sesiune ipython, import pyodbc, stabilesc o conexiune și un obiect cursor și mută în baza de date „învățare” cu instrucțiunea USE learning:

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>

În primul rând, să executăm comanda CREATE TABLE folosind variabila „SQL_CREATE_TBL”:

In [57]: try:
 …: cur.execute(SQL_CREATE_TBL)
 …: except pyodbc.DatabaseError as e:
 …: raise e
 …: cur.rollback()
 …: else:
 …: cur.commit()
 …:

Odată finalizat, voi verifica că tabelul „so_data” există în baza de date „învățare”:

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)

Interogând tabelul COLUMNS din baza de date information_schema, putem vedea că toate cele 85 de coloane au fost create:

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)

Pentru toate valorile coloanelor rândurilor CSV, avem nevoie de o interogare parametrizată pe care să o folosim în instrucțiunea INSERT. Din nou, putem folosi atributul panda columns pentru numărul corect de valori ale substituentului parametrului „?”. Apoi, includeți acei substituenți într-o interogare INSERT INTO:

In [59]: placeholders = “,”.join(“?” * len(data_set.columns))
In [61]: SQL_INSERT = “INSERT INTO so_data VALUES({});”.format(placeholders)

Pentru a continua, avem nevoie de unele funcționalități CSV, așa că voi importa și modulul încorporat csv:

In [62]: import csv

În cele din urmă, am ajuns la momentul adevărului; încărcarea…

AVERTISMENT!!!

Aceasta nu este deloc o operațiune rapidă și va dura ceva timp... Există peste 88.000 de rânduri în fișierul de date CSV, care necesită multe INSERT.

Știm din încercări și necazuri că totul nu este în mâinile sau controlul nostru. Pentru a fi în siguranță, ar trebui să lucrăm într-o formă de tranzacție. Pentru a face asta, voi seta autocommit la false în conexiunea pyodbc (autocommit este setat la True în mod implicit) și voi încapsula întreaga încărcare într-un bloc try/except/else/finally. În cazul în care ceva nu merge bine în timpul încărcării, orice INSERT care s-a finalizat va fi derulat înapoi și nu vor fi stocate date - sau denaturate.

Aici merge…

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

Odată finalizată execuția, în tabelul MySQL „so_data”, putem vedea că numărul total de rânduri este 88883:

MySQL localhost:33060+ ssl learning SQL > SELECT COUNT(*) FROM so_data;
+ — — — — — +
| COUNT(*)  |
+ — — — — — +
| 88883     |
+ — — — — — +
1 row in set (0.2939 sec)

Încărcarea a reușit.

Lucruri de reținut…

  • Dacă fișierul CSV țintă nu are un rând de antet de nume de coloane, codul pe care l-am prezentat în postare nu va funcționa.
  • Încărcarea demonstrată aici nu este rapidă sau optimizată de niciun fel de imaginație. Sunt multe inserții care se lansează. (Laptopul meu nu este probabil cel mai bun mediu de testare.)
  • Nu uitați, pentru ca acest script să funcționeze, toate tipurile de date coloane trebuie să fie setate la un tip comun și poate fi necesar să ALTER unele coloane după cum consideră nevoile dvs. odată ce încărcarea este finalizată.
  • Cântărește toate opțiunile tale. Instrucțiunea dinamică CREATE TABLE poate fi de puțină folos dacă trebuie să petreceți de trei ori mai mult timp executând instrucțiunile ALTER TABLE pentru a implementa tipurile de date de coloană adecvate.

Sperăm că veți găsi o utilizare pentru acest tip de comandă dinamică CREATE TABLE. L-am folosit prototipând câteva tabele MySQL pentru un anumit set de date de care eram interesat. Spuneți-mi ce părere aveți în comentariile de mai jos și vă mulțumesc pentru lectura!

Pyodbc CRUD

Dacă sunteți interesat să utilizați driverul pyodbc cu MySQL, am scris o serie de 4 părți despre operațiunile CRUD pe care le veți găsi valoroase.

  1. „Pyodbc SQL CRUD — Creați: Exemple cu MySQL”
  2. Pyodbc SQL CRUD — Citiți: Exemple cu MySQL
  3. Pyodbc SQL CRUD — Actualizare: Exemple cu MySQL
  4. Pyodbc SQL CRUD — Șterge: Exemple cu MySQL

Iti place ce ai citit? Vezi ceva incorect? Vă rugăm să comentați mai jos și vă mulțumim pentru lectură!!!

Un apel la acțiune!

Vă mulțumim pentru timpul acordat citirii acestei postări. Sper din tot sufletul că ai descoperit ceva interesant și lămuritor. Vă rugăm să împărtășiți descoperirile dvs. aici, cu altcineva pe care îl cunoașteți, care ar obține aceeași valoare din asta.

Vizitați „pagina Portofoliu-Proiecte” pentru a vedea postarea de blog/scrierea tehnică pe care le-am finalizat pentru clienți.

„Am menționat cât de mult îmi place o ceașcă de cafea?!?!”

Pentru a primi notificări prin e-mail (Never Spam) de la acest blog („Digital Owl’s Prose”) pentru cele mai recente postări de blog pe măsură ce sunt publicate, vă rugăm să vă abonați (din propria voință) făcând clic pe butonul „Click To Subscribe!” din bara laterală pagina de start! (Nu ezitați să consultați în orice moment „Pagina Politică de confidențialitate a Digital Owl’s Prose” pentru orice întrebări pe care le puteți avea despre: actualizări prin e-mail, înscriere, renunțare, formulare de contact etc...)

Asigurați-vă că vizitați pagina „„Best Of”” pentru o colecție cu cele mai bune postări de blog ale mele.

„Josh Otwell” are o pasiune de a studia și de a crește ca dezvoltator SQL și blogger. Alte activități preferate îl găsesc cu nasul îngropat într-o carte bună, articol sau linia de comandă Linux. Printre aceștia, împărtășește dragostea pentru jocurile RPG de masă, pentru a citi romane fantastice și pentru a petrece timp cu soția și cele două fiice.

Disclaimer: exemplele prezentate în această postare sunt idei ipotetice despre cum să obțineți tipuri similare de rezultate. Ele nu sunt cea mai bună soluție(e). Majoritatea, dacă nu toate, exemplele oferite, sunt realizate pe un mediu de lucru de dezvoltare personală/învățare și nu ar trebui să fie considerate de calitate a producției sau gata. Obiectivele și nevoile dvs. particulare pot varia. Utilizați acele practici care vă avantajează cel mai bine nevoile și obiectivele. Opiniile îmi aparțin.Publicat inițial la https://joshuaotwell.com pe 8 iulie 2020.