نوشتن داده Google Sheets با استفاده از پایتون در MySQL

google sheet

نوشتن داده Google Sheets با استفاده از پایتون در MySQL

در کار داده ها ، ما اغلب از روش های مختلف نوشتن و تجزیه و تحلیل داده ها استفاده می کنیم. برگه های Google راهی عالی برای وارد کردن و به اشتراک گذاری داده ها است. برای مجموعه داده های بزرگتر ، کار با ورق های Google بسیار دردناک می شود و یکی از راه های ایجاد آن ایجاد صفحات کاری جدید در هنگام وجود ردیف بیش از حد است.در ادامه با وبلاگ هاستینجا همراه باشید

یک پروژه که تیم ما مشغول به کار است ۳۰۰۰ ردیف در هفته ایجاد می کند ، که طبق اکثر استانداردها داده زیادی نیست ، اما بعد از چند هفته دچار مشکل می شود. اعضای تیم بایگانی برگه ها را آغاز کردند ، که انجام هرگونه جمع آوری داده ها را بسیار دشوار می کرد. ما تصمیم گرفتیم که یک ایده بهتر برای نوشتن تمام داده های ما در یک جدول MySQL است که به تیمهای دیگر امکان می دهد داده های ما را به جداول دیگری که با آنها کار می کنند بپیوند دهند. ما شما را در طی مراحل انجام این کار با استفاده از پایتون پیاده خواهم کرد. برای این پروژه ، من از یک دستگاه نوت بوک Jupyter، Python 3.7.1 و MySQL Workbench 8.0 استفاده می کنم.

 

داده ها در برگه های Google

من یک مجموعه داده کوچک برای این پروژه با استفاده از برگه های Google ایجاد کردم. من دو برگه درج کردم ، اولین ظاهری مانند:

google sheet

توجه کنید که من عمدا دو سلول را در ستون ۲ خالی گذاشته ام. این بعداً برای مثال خواهد بود. برگه دوم (یک برگه دوم در پایین) به نظر می رسد:

google sheet

 

اتصال صفحات Google به پایتون

اولین کاری که ما باید انجام دهیم دسترسی به کنسول توسعه دهندگان Google با استفاده از این لینک است. با کلیک روی پیوند “CREATE PROJECT” در بالای صفحه ، یک پروژه جدید ایجاد کنید:

google sheet

همانطور که در زیر آمده است ، پروژه را به عنوان چیزی که مرتبط است نام ببرید

google sheet

در مرحله بعد ، باید یک حساب کاربری برای پروژه ایجاد کنیم. روی منوی پیمایش در سمت چپ بالای صفحه کلیک کنید (۳ خط افقی ، که اغلب به آن همبرگر گفته می شود) و “خدمات حساب” را انتخاب کنید:

google sheet

شما باید از بالای صفحه (الف) پروژه خود را انتخاب کرده و سپس بر روی “ایجاد حساب کاربری سرویس” (b) ضربه بزنید.

google sheet

یک نام معنی دار به حساب سرویس ارائه دهید و روی “ایجاد” کلیک کنید. در مرحله بعد ، ما مجوزهای خدمات حساب کاربری را بر روی مالک تنظیم خواهیم کرد و روی “ادامه” کلیک می کنیم. در صفحه بعد از قسمت “دسترسی کاربران به این حساب سرویس” اجازه می دهیم و به قسمت “ایجاد کلید” در زیر بروید. این یک نوار جانبی را باز می کند که به ما امکان می دهد یک نوع کلید را انتخاب کنیم. “JSON” را انتخاب کرده و “CREATE” را بزنید. با این کار فایل JSON در رایانه شما ذخیره می شود و ما برای گرفتن اطلاعات از این فایل استفاده خواهیم کرد.

 

پرونده JSON را با استفاده از چیزی مانند Notepad ++ باز کنید. خطی را که حاوی “ایمیل مشتری” است ، پیدا کنید و کل آدرس ایمیل (بین نقل قول ها) را در کلیپ بورد خود کپی کنید. در مرحله بعد ، به برگه Google خود برگردید که حاوی داده هایی است که می خواهید در یک جدول MySQL بنویسید. بر روی دکمه سبز با عنوان “اشتراک” در سمت راست بالای صفحه گسترده کلیک کنید. آدرس ایمیل را از پرونده JSON وارد کنید و روی “ارسال” کلیک کنید. با این کار به برنامه Python ما امکان دسترسی به این برگه Google را می دهد.

 

تنظیم نوت بوک Jupyter

اکنون که همه چیز تنظیم شده است ، می توانیم به سمت پایتون حرکت کنیم! برای این آموزش من از نوت بوک Jupyter و Python 3.7.1 استفاده می کنم. اگر پایتون روی دستگاه خود نصب نشده است ، باید این کار را انجام دهید. نوت بوک Jupyter با آناکوندا عرضه می شود که یک بسته عالی برای پایتون است. می توانید نوت بوک Jupyter را با وارد کردن نوار جستجو (در ویندوز ، در نوار وظیفه) “Jupyter Notebook” راه اندازی کنید ، یا می توانید اعلان آناکوندا را راه اندازی کنید و پس از باز شدن سریع ، “نوت بوک Jupyter” را بدون نقل قول تایپ کنید ، اما ما بعداً به آناکوندا سریع احتیاج دارد ، بنابراین پیشنهاد می کنم از اولین روش توصیف شده استفاده کنید.

Jupyter Notebook در مرورگر شما راه اندازی می شود. شما به دایرکتوری کاری خود هدایت خواهید شد. می توانید به یک پوشه متفاوت بروید ، اما اکنون با کلیک بر روی “جدید” در سمت راست بالای صفحه و انتخاب یک نوت بوک پایتون ۳ یک نوت بوک جدید ایجاد می کنیم:

google sheet

بیایید با کلیک کردن روی نام نوت بوک (بدون عنوان) در بالای صفحه ، نوت بوک را به “GoogleSheetsToMySQL” تغییر نام دهیم. در مرحله بعد ، باید فایل JSON را که بارگیری کردیم ، به فهرست شاخه کار خود انتقال دهیم. من معتقدم که دایرکتوری پیش فرض تحت “C://users//your_username” است.

قبل از شروع برنامه نویسی ، باید چند ماژول نصب کنیم. برای نصب ماژول های مورد نیاز ما ، سریع آناکوندا را باز کنید (می توانید این مورد را در نوار جستجوی ویندوز در نوار وظیفه پیدا کنید). در سریع آناکوندا ، شما نیاز به نصب ماژول های زیر دارید. با تایپ دستورات زیر از پیپ استفاده کرده و بعد از هر ضربه وارد کنید:

  1. pip install gspread
  2. pip install oauth2client
  3. pip install mysql-connector

من اینها را قبلاً نصب کرده ام ، همانطور که از خروجی فرمان سریع نشان داده شده است (هنگام تلاش برای نصب gspread):

google sheet

از آنجا که به یک پایگاه داده MySQL دسترسی خواهیم داشت ، مهم نیست که اعتبار ورود به سیستم را در کد خود قرار ندهید. ما یک پرونده پایتون (.py) ایجاد می کنیم و این اعتبارنامه ها را در آنجا ذخیره می کنیم. سپس می توانیم آنها را به راحتی در اسکریپت Jupyter Notebook وارد کنیم. سریعترین راه برای ایجاد پرونده .py با Notepad ++ است. من برای این کار از PyCharm استفاده کردم ، اما از هر IDE که ترجیح می دهید استفاده کنید. IDE خود را باز کرده و پرونده ای با نام “MySQLCredentials.py” ایجاد کنید. در این پرونده فقط باید ۴ خط تایپ کنیم و آن را در فهرست کار خود ذخیره کنیم. نیاز داریم:

پرونده را ذخیره کرده و آن را در فهرست کار خود قرار کنید.

نوشتن داده Google Sheets با استفاده از پایتون در MySQL

کدنویسی در نوت بوک Jupyter

اکنون ، به دفترچه خود برگردید. ما باید با وارد کردن اولین سلول به ماژول هایی که استفاده خواهیم کرد وارد کنیم:

سلول را اجرا کنید و در صورت بروز هرگونه خطایی نیز ، خیر! در مرحله بعد باید برخی از متغیرها را برای استفاده با gspread تنظیم کنیم:

اگر پرونده JSON خود را نام دیگری گذاشتید ، فقط اطمینان حاصل کنید که در کد بالا نام آن صحیح است. اکنون باید روشی را تعریف کنیم که داده را از برگه Google بیرون بکشد:

ما در حال تعریف یک متد با دو پارامتر هستیم. ما باید به آن بگوییم که چه برگه ای را به آن ارجاع می دهیم و فهرست صفحه. فهرست کاربر به برگه ای که ما به آن اشاره می کنیم اشاره دارد. برگه اول دارای یک شاخص ۰ است ، تب دوم دارای فهرست ۱ و غیره است. ما به این مقادیر نیاز نخواهیم داشت تا وقتی این روش را فراخوانی کنیم ، بنابراین به زودی به آن خواهیم رسید. نوع برگشتی برای این روش یک لیست است و تمامی مقادیر موجود در آن برگه را برمی گرداند. سپس لیست را با استفاده از [۱:] که اولین عنصر لیست را حذف می کند ، برش می دهیم. از آنجا که در صفحه Google دارای سرصفحه ای با نام ستون ها است ، این کاهش می یابد که بنابراین ما فقط داده ها را از ورق می کشیم.

قبل از شروع کار ، بررسی می کنیم که این روش می تواند داده ها را از برگه ما بگیرد.

google sheet

در یک سلول جدید در دفترچه ، وارد کنید

ممکن است خطایی در گفتن اینکه باید API Drive را فعال کنید ، خطایی دریافت کنید. این پیوند را فراهم می کند که می توانید بر روی آن کلیک کنید تا API Drive فعال شود. اکنون سلول را مجدداً مجدداً مورد استفاده قرار داده و مطمئن شوید که هیچ گونه خطایی را نخواهید کرد. من برای فعال کردن ۲ API جداگانه مجبور شدم چندین بار آن را اجرا کنم و بدون اینکه خطایی به من وارد شود باید یک دقیقه منتظر بمانم. دیگر لازم نیست این کار را انجام دهید. خوب ، اکنون بررسی کنید که داده ای را که انتظار دارید داشته باشید. می توانید ردیف اول لیست و همچنین طول لیست (در این حالت چند ردیف داده) را بررسی کنید:

که باید برگردد:

حال باید نوشتن روشی برای نوشتن این داده ها در MySQL تنظیم کنیم. استفاده از یک بلوک try-only-end در اینجا مفید خواهد بود. چندین بلوک از نظرات وجود دارد که با داشتن # جلوی آنها ، که فقط برای روشن شدن وجود دارد:

برای آزمایش این ، دستور زیر را اجرا می کنیم (برای اجرای متد GetShapsheetData () یا به “داده” تعریف شده نیاز دارید).

خروجی باید:

نوشتن داده Google Sheets با استفاده از پایتون در MySQL

بررسی داده ها در MySQL Workbench موفقیت آمیز به نظر می رسد ، اما یک مسئله وجود دارد که باید برطرف شود. در جدول زیر MySQL Workbench آمده است:

google sheet

 

توجه کنید که ما ۲ موضوع ممکن داریم. ردیف آخر همه NULL است که به احتمال زیاد ما انتظار نداریم این سطر گنجانده شود. اما مهمتر از همه ، مقادیر گمشده در ستون۲ NULL نیست! این امر به این دلیل است که پایتون داده های صفحه را می خواند و مقدار خالی را مشاهده می کند ، که به احتمال زیاد به عنوان یک رشته خالی تعبیر می شود. ما احتمالاً رشته های خالی را در اینجا نمی خواهیم و در عوض انتظار مقادیر NULL را داریم. بنابراین بیایید روشی برای پاک کردن مقادیر رشته خالی بنویسیم و آنها را به عنوان مقادیر NULL بنویسیم.

آنچه این کار را انجام می دهد بررسی مقادیر رشته خالی (‘) و جایگزینی آنها با کلمه کلیدی Python” None “است که برای MySQL به عنوان NULL نوشته خواهد شد. بگذارید این را بررسی کنیم:

بیایید در MySQL Workbench بررسی کنیم تا ببینیم این چه شکلی است:

google sheet

خیلی بهتر! من معمولاً در پایان ردیف NULL نمی گیرم ، اما در صورت نیاز می توانیم این کار را رها کنیم.

 

اکنون بیایید بگوییم که ما می خواهیم جدول را با صفحه دوم کار خود ، با صفحه فهرست کارایی = ۱٫ به روز کنیم. این بار که نمی خواهیم جدول را رها کنیم ، فقط می خواهیم آن را وارد کنیم. ما می توانیم یک روش جدید به نام UpdateMySQLTable تعریف کنیم. ما می خواهیم از همان روشی که در بالا توضیح داده شده استفاده کنیم (WritToMySQLTable) بجز اینکه خطوط کد زیر را حذف خواهیم کرد:

این روش را با فهرست برگه جدید اجرا کنید:

و اکنون یک جدول با ۲۰ ردیف ، ۱۰ عدد از هر برگه خواهید داشت. جدول زیر در MySQL Workbench به نظر می رسد:

google sheet

فقط مطمئن شوید که نام و ستون ستون ها مطابقت دارد ، در غیر این صورت مجبور خواهید بود که آنها را مرتب کنید تا آنها انجام دهند. برای اطلاعات تیم من ، اگر متدولوژی ما تغییر کند ، در ستون جدید می نویسیم و من دوباره برمی گردم و ستون های خالی را در برگه های قدیمی وارد می کنم و دوباره تمام داده ها را وارد می کنم. اکنون می توانید از داشتن یک پایگاه داده در MySQL با عملکرد ورق های Google استفاده کنید! این چیزی است که کد من در نوت بوک Jupyter به نظر می رسد. کد نویسی مبارک!

google sheet

google sheet

google sheet

نیاز به مشاوره دارید؟

تیم فروش آماده ارائه پیشنهاد و پاسخگویی به سوالات شماست.