Hech qachon qo'lda katta CREATE TABLE bayonot yozishga to'g'ri kelganmi? O'nlab ustunlari bormi? Balki bir necha o'nlab ustunlar? Katta CREATE TABLE buyruqlar bilan yordam beradigan ba'zi GUI vositalari mavjud. Yoki, men tanish bo'lmagan boshqa drag-n-drop dasturiy ta'minot turlari. Agar siz Python kodining bir necha qatorini yozsangiz va qo'lda terilgandan ko'ra kamroq kuch sarflagan holda ulkan CREATE TABLE bayonotiga g'amxo'rlik qila olsangiz-chi? Qiziqmi? O'qishni davom ettiring va pandalar, pyodbc va MySQL-dan qanday foydalanishni ko'ring…
Ishlatilgan operatsion tizim, dasturiy ta'minot va ma'lumotlar bazasi:
- OpenSuse Leap 15.1
- MySQL 8.0.20
- Python 3.7.2
- pandas 1.0.4
O'z-o'zini reklama qilish:
Agar sizga bu yerda yozilgan kontent yoqsa, albatta, ushbu blogni va sevimli post(lar)ingizni undan foyda ko'rishi yoki yoqtirishi mumkin bo'lgan boshqalar bilan baham ko'ring. "Kofe mening eng sevimli ichimlikim bo'lgani uchun, agar xohlasangiz, menga ham sotib olishingiz mumkin!"
Agar xohlasangiz, ushbu postda foydalanilgan "Stack Overflow Developer So'rovi natijalari" ma'lumotlar to'plamini o'zingizning tadqiqotingiz uchun yuklab olishingiz mumkin.
Boshlash uchun men pandalarni ipython sessiyasiga import qilaman va Stack Overflow soʻrovi natijalarini yuklab olishdan maqsadli CSVga yoʻlni belgilayman:
In [1]: import pandas as pd In [2]: csv_file = r’/home/joshua/Practice_Data/developer_survey_2019/survey_results_public.csv’
Pandas read_csv() funktsiyasidan foydalanib, men 'data_set' nomli DataFrame yarataman. DataFrame-ning o'zi biz CREATE TABLE bayonoti uchun foydalanishimiz mumkin bo'lgan bir nechta atributlarni taqdim etadi:
In [3]: data_set = pd.read_csv(csv_file, delimiter=’,’)
columns atributiga nazar tashlasak, import qilingan CSV-da bir nechta ustunlar mavjudligini ko'rishimiz mumkin:
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
Aniq bo'lish uchun 85 ta ustun. Voy-buy! O'sha CREATE TABLE bayonoti dahshatli bo'ladi!
Birinchi xatboshida aytib o'tganimdek, bunday katta CREATE TABLE iborani qo'lda qo'lda yozish juda qiyin. Va keraksiz. Pandalar va pyodbc Python kutubxonalaribir oz makkorlik bilan CREATE TABLE bayonotini boshqarishga qodir.
Ustunlar sonini ta'minlash uchun - CSV faylidagi ustunlar soniga asoslanib - men uning iterable imkoniyatlari uchun o'zboshimchalik bilan list python yarataman (buning sababini keyinroq postda bilib olasiz).
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
Ogoh bo'ling, men Python gurusi emasman. Buni amalga oshiradigan yaxshiroq Pythonic usullar ham bor. Aqlli o'quvchilar list dan nima maqsadda foydalanayotganimni ko'rganingizdan so'ng, ularni quyidagi izohlarda menga ko'rsatishlari mumkin.
Men qo‘llayotgan yondashuvda bir nechtaqo‘shimchalar bor va birinchisini shu yerda ta’kidlamoqchiman. Dinamik - ma'lum darajada avtomatlashtirilgan - CREATE TABLE bayonotiga ega bo'lish uchun barcha ustunlar bir xil ma'lumotlar turiga ega bo'lishi kerak (dastlabki kamida). Ushbu maxsus misol uchun men TEXT ma'lumotlar turini tanladim, lekin VARCHAR ham ishlashi mumkin. Ma'lumotlar to'plamiga qarab, agar siz aniq raqamli ma'lumotlar bilan ishlayotgan bo'lsangiz, barcha INTEGER yoki DOUBLE dan foydalanishingiz mumkin.
Shuni yodda tutingki, qaysi ma'lumotlar turini tanlashingizdan qat'i nazar, siz MySQL-da ALTER TABLE bayonot(lar)ni ishga tushirishingiz va yuklash tugallangandan so'ng kerakli ustunlar uchun to'g'ri ma'lumotlar turlarini qo'llashingiz kerak bo'ladi. Yoki dastlabki bosqich jadvalidagi maʼlumotlar qatorlarini boshqa doimiy jadvalga koʻchiring va ularni shu jarayonda yozing. ALTER TABLE qimmat boʻlishi mumkin, chunki 80 mingdan ortiq maʼlumotlar qatori mavjud. Sizning kilometringiz farq qilishi mumkin
Ko'rib turganimizdek, biz qurishimiz kerak bo'lgan dinamik CREATE TABLE buyrug'i uchun satrlarni birlashtirish kuchi juda muhimdir.
Birinchidan, men CREATE TABLE buyrug'ini va jadval nomini o'rnataman - "so_data" - bu misol uchun, uni "SQL_CREATE_TBL" o'zgaruvchisida saqlash:
In [41]: SQL_CREATE_TBL = “CREATE TABLE so_data(“
Keyin, for halqasidan foydalanib, har bir ustun nomini - pandas columns atributi orqali - TEXT ma'lumotlar turi bilan birga "SQL_CREATE_TBL" o'zgaruvchisiga qo'shing. satr qo'shish range() va len() usullaridan foydalangan holda CSV faylida (85) mavjud bo'lgan ustunlar sonining har biri uchun bir marta sodir bo'ladi. Bir vaqtning o'zida format() string usuli DataFrame columns atributidan olingan column nom qiymatini kiritadi:
In [43]: for name in range(0, len(col_count)):
…: SQL_CREATE_TBL += “{} TEXT, “.format(data_set.columns[name])
for tsikli tugallangandan so'ng 'SQL_CREATE_TBL' o'zgaruvchisi tarkibini ko'rib chiqamiz:
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, ‘
TEXT ma'lumotlar turiga ega bo'lgan barcha ustun nomlari endi satrning bir qismidir. Biroq, ba'zi tozalash qilish kerak. “SQL_CREATE_TBL” qatorida oxirgi “SurveyEase” ustuni uchun TEXT kalit so‘zidan keyin vergul qo‘yilgan. Agar shunday qoldirilsa, keyingi vergul bajarilsa, MySQL-da xatolik yuzaga keladi.
Biz rstring() usulidan foydalanishimiz va keyingi vergulni olib tashlashimiz mumkin:
In [48]: SQL_CREATE_TBL = SQL_CREATE_TBL.rstrip(“ ,”)
CREATE TABLE iborasini bajarish uchun faqat ‘SQL_CREATE_TBL’ o‘zgaruvchisiga yopuvchi qavs va nuqta-vergulni qo‘shish kifoya:
In [49]: SQL_CREATE_TBL += “);”
'SQL_CREATE_TBL' o'zgaruvchisi endi to'liq CREATE TABLE bayonotini saqlaydi:
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);’
Dinamik CREATE TABLE bayonoti uchun butun kod tuzilishi quyida ko'rsatilgan:
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 += “);”
CREATE TABLE iborani qo'lda yozish, albatta!
Endi biz MySQL misolida 'SQL_CREATE_TBL' o'zgaruvchisi yordamida CREATE TABLE buyrug'ini amalda bajarishimiz kerak. MySQL ma'lumotlar bazasiga kirishingiz mumkin bo'lgan ko'plab Python drayverlari/ulagichlari mavjud. Men pyodbc drayverini yaxshi bilaman, shuning uchun uni yuklash uchun ishlataman.
Xuddi shu ipython sessiyasida men pyodbc ni import qilaman, ulanish va kursor ob'ektini o'rnataman va USE learning bayonoti bilan "o'rganish" ma'lumotlar bazasiga ko'chiraman:
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>
Avvalo, keling, 'SQL_CREATE_TBL' o'zgaruvchisi yordamida CREATE TABLE buyrug'ini bajaramiz:
In [57]: try: …: cur.execute(SQL_CREATE_TBL) …: except pyodbc.DatabaseError as e: …: raise e …: cur.rollback() …: else: …: cur.commit() …:
Tugallangach, men "so_data" jadvali "o'rganish" ma'lumotlar bazasida mavjudligini tekshiraman:
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)
information_schema ma'lumotlar bazasidagi COLUMNS jadvaliga so'rov o'tkazsak, biz barcha 85 ta ustun yaratilganligini ko'rishimiz mumkin:
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)
CSV satrlarining barcha ustun qiymatlari uchun INSERT bayonotida foydalanish uchun parametrlangan so'rov kerak. Shunga qaramay, biz to'g'ri parametr to'ldiruvchisi '?' qiymatlari uchun pandas columns atributidan foydalanishimiz mumkin. Keyin, ushbu to'ldiruvchilarni INSERT INTO so'roviga qo'shing:
In [59]: placeholders = “,”.join(“?” * len(data_set.columns))
In [61]: SQL_INSERT = “INSERT INTO so_data VALUES({});”.format(placeholders)
Davom etish uchun bizga ba'zi CSV funksiyalari kerak, shuning uchun men o'rnatilgan csv modulini ham import qilaman:
In [62]: import csv
Nihoyat, biz haqiqat momentiga keldik; yuklash…
DIQQAT!!!
Bu hech qanday tezkor operatsiya emas va biroz vaqt talab etadi… CSV maʼlumotlar faylida 88 mingdan ortiq qatorlar mavjud boʻlib, ular koʻp INSERT’larni talab qiladi.
Biz sinovlardan bilamizki, hamma narsa bizning qo'limizda yoki nazoratimizda emas. Xavfsiz bo'lish uchun biz qandaydir tranzaksiya shaklida ishlashimiz kerak. Buning uchun men pyodbc ulanishida autocommit ni false ga o'rnataman (autocommit sukut bo'yicha True ga o'rnatiladi) va butun yuklashni try/except/else/finally blokiga kiritaman. Yuklash paytida biror narsa noto'g'ri bo'lsa, tugallangan har qanday INSERT'lar orqaga qaytariladi va hech qanday ma'lumot saqlanmaydi yoki burilmaydi.
Mana...
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
Bajarish tugallangandan so'ng, "so_data" MySQL jadvalida biz satrlarning umumiy soni 88883 ekanligini ko'rishimiz mumkin:
MySQL localhost:33060+ ssl learning SQL > SELECT COUNT(*) FROM so_data; + — — — — — + | COUNT(*) | + — — — — — + | 88883 | + — — — — — + 1 row in set (0.2939 sec)
Yuklash muvaffaqiyatli.
Yodda tutish kerak bo'lgan narsalar ...
- Agar maqsadli CSV faylida ustun nomlarining sarlavha qatori bo'lmasa, men postda taqdim etgan kod ishlamaydi.
- Bu erda ko'rsatilgan yuklash tez emas yoki har qanday tasavvurga moslashtirilmagan. Bu juda ko'p qo'shimchalar o'chiriladi. (Mening noutbukim ham eng yaxshi sinov muhiti emas.)
- Ushbu skript ishlashi uchun barcha ustunlar maʼlumotlar turlari umumiy turga oʻrnatilishi kerakligini unutmang va yuklash tugallangandan soʻng ehtiyojlaringizga mos keladigan baʼzi ustunlar
ALTERboʻlishi mumkin. - Barcha variantlaringizni torting. Tegishli ustun ma'lumotlar turlarini amalga oshirish uchun
ALTER TABLEbayonot(lar)ni bajarish uchun 3x vaqt sarflashingiz kerak bo'lsa, dinamikCREATE TABLEbayonoti kam foyda keltirishi mumkin.
Umid qilamanki, siz ushbu turdagi dinamik CREATE TABLE buyrug'i uchun foydalanishni topasiz. Men undan oʻzimni qiziqtirgan maʼlum maʼlumotlar toʻplami uchun bir nechta MySQL jadvallarini prototiplashda foydalandim. Quyidagi izohlarda fikringizni bildiring va oʻqiganingiz uchun tashakkur!
Pyodbc CRUD
Agar siz MySQL bilan pyodbc drayverini ishlatmoqchi bo'lsangiz, men CRUD operatsiyalari bo'yicha 4 qismli seriyani yozdim, siz uchun foydali bo'ladi.
- "Pyodbc SQL CRUD - Yarating: MySQL bilan misollar"
- "Pyodbc SQL CRUD - O'qing: MySQL bilan misollar"
- "Pyodbc SQL CRUD - Yangilash: MySQL bilan misollar"
- "Pyodbc SQL CRUD - O'chirish: MySQL bilan misollar"
O'qiganingiz yoqdimi? Hech qanday noto'g'ri narsani ko'rdingizmi? Iltimos, quyida izoh bering va o'qiganingiz uchun tashakkur !!!
Harakatga chaqiruv!
Ushbu postni o'qishga vaqt ajratganingiz uchun tashakkur. Umid qilamanki, siz qiziqarli va ma'rifiy narsani kashf qildingiz. Iltimos, o'z topilmalaringizni shu yerda, xuddi shunday qiymatga ega bo'lgan boshqa birov bilan baham ko'ring.
Mijozlar uchun to'ldirgan blog postim/texnik yozuvimni ko'rish uchun Portfolio-Loyihalar sahifasiga tashrif buyuring.
"Men bir chashka qahvani qanchalik yaxshi ko'rishimni aytdimmi?!?"
Ushbu blogdan ("Digital Owl's Prose") e-pochta xabarnomalarini (hech qachon spam bo'lmasin) olish uchun ular e'lon qilingan so'nggi blog postlari uchun yon paneldagi "Obuna bo'lish uchun bosing!" tugmasini bosish orqali obuna bo'ling bosh sahifa! (Istalgan vaqtda "Digital Owl's Prose Maxfiylik siyosati sahifasi" ni ko'rib chiqing: elektron pochta xabarlari, ro'yxatdan o'tish, rad etish, aloqa shakllari va hk.)
Ishonch hosil qiling va mening eng yaxshi blog postlarim toʻplamini olish uchun “Best Of” sahifasiga tashrif buyuring.
"Josh Otwell" SQL Developer va blogger sifatida o'qish va o'sishga ishtiyoqi bor. Boshqa sevimli mashg'ulotlar uni burni yaxshi kitob, maqola yoki Linux buyruq satriga ko'milgan holda topadi. Ular orasida u stol usti RPG o'yinlarini, fantastik romanlarni o'qishni va xotini va ikki qizi bilan vaqt o'tkazishni yaxshi ko'radi.
Rad etish: Ushbu postda keltirilgan misollar shunga o'xshash natijalarga qanday erishish mumkinligi haqidagi faraziy g'oyalardir. Ular eng yaxshi yechim (lar) emas. Taqdim etilgan misollarning aksariyati, agar hammasi bo'lmasa ham, shaxsiy rivojlanish/ta'lim ish stantsiyasida amalga oshiriladi va ishlab chiqarish sifati yoki tayyor deb hisoblanmasligi kerak. Sizning maxsus maqsadlaringiz va ehtiyojlaringiz farq qilishi mumkin. Ehtiyojlaringiz va maqsadlaringizga eng yaxshi foyda keltiradigan amaliyotlardan foydalaning. Fikrlar o‘zimniki.Aslida “https://joshuaotwell.com” saytida 2020-yil 8-iyulda chop etilgan.
