هاستینجا |هاست لینوکس-هاست وردپرس-هاست پایتون-سرور-ثبت دامنه

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

نوشته شده در 2019/11/12

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” است.

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

pip install gspread
pip install oauth2client
pip install mysql-connector

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

google sheet

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

user = (your username)
password = (your password)
host = (hostname)
database = (database schema you will be using)

user = (your username)

password = (your password)

host = (hostname)

database = (database schema you will be using)

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

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

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

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

# import libraries
import gspread
import MySQLCredentials as mc
import mysql.connector
from oauth2client.service_account import ServiceAccountCredentials

# import libraries

import gspread

import MySQLCredentials as mc

import mysql.connector

from oauth2client.service_account import ServiceAccountCredentials

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

# initialize variables for gspread
scope = [‘https://spreadsheets.google.com/feeds',
‘https://www.googleapis.com/auth/drive']
creds = 
ServiceAccountCredentials.from_json_keyfile_name(‘GoogleSheetsToMySQL.json’, scope)
client = gspread.authorize(creds)

# initialize variables for gspread

scope = [‘https://spreadsheets.google.com/feeds',

‘https://www.googleapis.com/auth/drive']

creds =

ServiceAccountCredentials.from_json_keyfile_name(‘GoogleSheetsToMySQL.json’, scope)

client = gspread.authorize(creds)

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

# define method to pull data from spreadsheet
def GetSpreadsheetData(sheetName, worksheetIndex): 
   sheet = client.open(sheetName).get_worksheet(worksheetIndex)
    return sheet.get_all_values()[1:]

# define method to pull data from spreadsheet

def GetSpreadsheetData(sheetName, worksheetIndex):

sheet = client.open(sheetName).get_worksheet(worksheetIndex)

return sheet.get_all_values()[1:]

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

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

google sheet

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

data = GetSpreadsheetData(‘GoogleSheetData’, 0)

1	data = GetSpreadsheetData(‘GoogleSheetData’, 0)

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

print(data[0])
print(len(data))

1 2	print(data[0]) print(len(data))

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

[‘1’, ‘a’, ‘2019–01–01 1:00:00’]
10

1 2	[‘1’, ‘a’, ‘2019–01–01 1:00:00’] 10

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

# define method to write list of data to MySQL table

def WriteToMySQLTable(sql_data, tableName):

# we are using a try/except block (also called a try/catch block in other languages) which is good for error handling. It will “try” to execute anything in the “try” block, and if there is an error, it will report the error in the “except” block. Regardless of any errors, the “finally” block will always be executed.

 try:

# Here we include the connection credentials for MySQL. We create a connection object that we pass the credentials to, and notice that we can specify the database which is ‘sys’ in the MySQLCredentials.py file because I’m using since I’m using the default database in MySQL Workbench 8.0.

 connection = mysql.connector.connect(

user = mc.user,

password = mc.password,

host = mc.host,

database = mc.database

)

# This command will drop the table, and we could just have the table name hardcoded into the string, but instead I am using the name of the table passed into the method. {} is a placeholder for what we want to pass into this string, and using .format(blah) we can pass the string name from the variable passed into the method here.

sql_drop = “ DROP TABLE IF EXISTS {} “.format(tableName)

# Now we will create the table, and the triple quotes are used so that when we go to the next line of code, we remain in a string. Otherwise it will terminate the string at the end of the line, and we want ALL of this to be one giant string. When injecting data into VALUES, we use the placeholder %s for each column of data we have.

sql_create_table = “””CREATE TABLE {}( 
            Column1 INT(11),
            Column2 VARCHAR(30),
            Column3 DATETIME,
            PRIMARY KEY (Column1)
            )”””.format(tableName)

sql_insert_statement = “””INSERT INTO {}( 
            Column1,
            Column2,
            Column3 )
            VALUES ( %s,%s,%s )”””.format(tableName)

# Here we create a cursor, which we will use to execute the MySQL statements above. After each statement is executed, a message will be printed to the console if the execution was successful.

cursor = connection.cursor()  
cursor.execute(sql_drop)   
print(‘Table {} has been dropped’.format(tableName))  
cursor.execute(sql_create_table)
print(‘Table {} has been created’.format(tableName))

# We need to write each row of data to the table, so we use a for loop that will insert each row of data one at a time

for i in sql_data:            
cursor.execute(sql_insert_statement, i)

# Now we execute the commit statement, and print to the console that the table was updated successfully

connection.commit()
print(“Table {} successfully updated.”.format(tableName))

# Errors are handled in the except block, and we will get the information printed to the console if there is an error

except mysql.connector.Error as error :

connection.rollback()

print(“Error: {}. Table {} not updated!”.format(error, tableName))

# We need to close the cursor and the connection, and this needs to be done regardless of what happened above.

finally:
        cursor.execute(‘SELECT COUNT(*) FROM {}’.format(tableName))
        rowCount = cursor.fetchone()[0]
        print(tableName, ‘row count:’, rowCount)
        if connection.is_connected():
            cursor.close()
            connection.close()
            print(“MySQL connection is closed.”)

# define method to write list of data to MySQL table

def WriteToMySQLTable(sql_data, tableName):

# we are using a try/except block (also called a try/catch block in other languages) which is good for error handling. It will “try” to execute anything in the “try” block, and if there is an error, it will report the error in the “except” block. Regardless of any errors, the “finally” block will always be executed.

try:

# Here we include the connection credentials for MySQL. We create a connection object that we pass the credentials to, and notice that we can specify the database which is ‘sys’ in the MySQLCredentials.py file because I’m using since I’m using the default database in MySQL Workbench 8.0.

connection = mysql.connector.connect(

user = mc.user,

password = mc.password,

host = mc.host,

database = mc.database

)

# This command will drop the table, and we could just have the table name hardcoded into the string, but instead I am using the name of the table passed into the method. {} is a placeholder for what we want to pass into this string, and using .format(blah) we can pass the string name from the variable passed into the method here.

sql_drop = “ DROP TABLE IF EXISTS {} “.format(tableName)

# Now we will create the table, and the triple quotes are used so that when we go to the next line of code, we remain in a string. Otherwise it will terminate the string at the end of the line, and we want ALL of this to be one giant string. When injecting data into VALUES, we use the placeholder %s for each column of data we have.

sql_create_table = “””CREATE TABLE {}(

Column1 INT(11),

Column2 VARCHAR(30),

Column3 DATETIME,

PRIMARY KEY (Column1)

)”””.format(tableName)

sql_insert_statement = “””INSERT INTO {}(

Column1,

Column2,

Column3 )

VALUES ( %s,%s,%s )”””.format(tableName)

# Here we create a cursor, which we will use to execute the MySQL statements above. After each statement is executed, a message will be printed to the console if the execution was successful.

cursor = connection.cursor()

cursor.execute(sql_drop)

print(‘Table {} has been dropped’.format(tableName))

cursor.execute(sql_create_table)

print(‘Table {} has been created’.format(tableName))

# We need to write each row of data to the table, so we use a for loop that will insert each row of data one at a time

for i in sql_data:

cursor.execute(sql_insert_statement, i)

# Now we execute the commit statement, and print to the console that the table was updated successfully

connection.commit()

print(“Table {} successfully updated.”.format(tableName))

# Errors are handled in the except block, and we will get the information printed to the console if there is an error

except mysql.connector.Error as error :

connection.rollback()

print(“Error: {}. Table {} not updated!”.format(error, tableName))

# We need to close the cursor and the connection, and this needs to be done regardless of what happened above.

finally:

cursor.execute(‘SELECT COUNT(*) FROM {}’.format(tableName))

rowCount = cursor.fetchone()[0]

print(tableName, ‘row count:’, rowCount)

if connection.is_connected():

cursor.close()

connection.close()

print(“MySQL connection is closed.”)

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

WriteToMySQLTable(data, ‘MyData’)

1	WriteToMySQLTable(data, ‘MyData’)

خروجی باید:

Table MyData has been dropped 
Table MyData has been created 
Table MyData successfully updated. 
MyData row count: 10 
MySQL connection is closed.

Table MyData has been dropped

Table MyData has been created

Table MyData successfully updated.

MyData row count: 10

MySQL connection is closed.

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

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

google sheet

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

def PreserveNULLValues(listName):
    print(‘Preserving NULL values…’)
    for x in range(len(listName)):
        for y in range(len(listName[x])):
            if listName[x][y] == ‘’:
                listName[x][y] = None
    print(‘NULL values preserved.’)

def PreserveNULLValues(listName):

print(‘Preserving NULL values…’)

for x in range(len(listName)):

for y in range(len(listName[x])):

if listName[x][y] == ‘’:

listName[x][y] = None

print(‘NULL values preserved.’)

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

data = GetSpreadsheetData(‘GoogleSheetData’, 0)
PreserveNULLValues(data)
WriteToMySQLTable(data, ‘MyData’)

data = GetSpreadsheetData(‘GoogleSheetData’, 0)

PreserveNULLValues(data)

WriteToMySQLTable(data, ‘MyData’)

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

google sheet

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

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

sql_drop = …
sql_create_table = …

1 2	sql_drop = … sql_create_table = …

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

data = GetSpreadsheetData(‘GoogleSheetData’, 1)
PreserveNULLValues(data)
UpdateMySQLTable(data, ‘MyData’)

data = GetSpreadsheetData(‘GoogleSheetData’, 1)

PreserveNULLValues(data)

UpdateMySQLTable(data, ‘MyData’)

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

google sheet

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

google sheet

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

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

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

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

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

جستجو

دسته بندی ها

برچسب ها

مطالب اخیر

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