साध्या अपग्रेडसह तुमचा डेटाबेस इन्सर्ट स्पीड नाटकीयरित्या सुधारा

चमकदारपणे जलद पायथन डेटाबेस कनेक्शन तयार करण्याचे 4 स्तर

Python सह तुमच्या डेटाबेसमध्ये डेटा अपलोड करणे सोपे आहे. तुमचा डेटा Pandas डेटाफ्रेममध्ये लोड करा आणि dataframe.to_sql() पद्धत वापरा. पण तुम्ही कधी लक्षात घेतले आहे की मोठ्या टेबलांसह काम करताना इन्सर्टला खूप वेळ लागतो? आमच्याकडे बसायला वेळ नाही, आमच्या शंका पूर्ण होण्याची वाट पाहत!

काही बदल करून तुम्ही इन्सर्ट्स खूप जलद करू शकता. तपकिरी (डिफॉल्ट to_sql() पद्धतीच्या लेखनाच्या वेळेची हिरव्या पट्टीशी (आमचे ध्येय) तुलना करा. अनुलंब अक्ष लोगॅरिदमिकस्केलवर आहे हे देखील लक्षात घ्या! या लेखाच्या शेवटी तुम्ही लाइटनिंग फास्ट डेटाबेस ऑपरेशन्स करण्यास सक्षम व्हाल. तयार? चल जाऊया!

ध्येय आणि पावले

आम्ही दोन चरणांमध्ये सुपरफास्ट इन्सर्शन पद्धतीकडे कार्य करू. पहिला भाग आमच्या डेटाबेसशी योग्यरित्या कसे कनेक्ट करावे यावर लक्ष केंद्रित करतो, दुसरा भाग चढत्या वेगाने डेटा घालण्याचे 4 मार्ग एक्सप्लोर करेल.

1. आमच्या डेटाबेसशी कनेक्ट करत आहे

कोणत्याही डेटाबेसशी संप्रेषण करण्यासाठी, आपल्याला प्रथम डेटाबेस-इंजिन तयार करणे आवश्यक आहे. हे इंजिन तुमच्‍या अजगर-वस्‍तूंचे (जसे की पांडा डेटाफ्रेम) डेटाबेसमध्‍ये अंतर्भूत केले जाऊ शकते असे भाषांतर करते. असे करण्यासाठी ते आपल्या डेटाबेसमध्ये कसे प्रवेश करू शकते हे माहित असणे आवश्यक आहे. यासाठी कनेक्शन स्ट्रिंग वापरली जाते. आम्ही प्रथम कनेक्शन स्ट्रिंग तयार करू आणि नंतर आमचे डेटाबेस इंजिन तयार करण्यासाठी याचा वापर करू.

1.1 कनेक्शन स्ट्रिंग तयार करा

कनेक्शन स्ट्रिंगमध्ये डेटाबेसचा प्रकार, odbc-driver आणि डेटाबेसमध्ये प्रवेश करण्यासाठी आवश्यक असलेल्या डेटाबेस क्रेडेंशियल्सबद्दल माहिती असते.

constring = "mssql+pyodbc://USERNAME:PASSWORD@DATABASESERVER_IP/DATABASENAME?driver=SQL+Server+Native+Client+11.0"

वरील उदाहरणामध्ये आम्ही Microsoft SQL सर्व्हर डेटाबेसशी कनेक्ट करण्यासाठी एक स्ट्रिंग तयार करतो. जसे आपण पाहू शकता की आम्ही आमची डेटाबेस क्रेडेंशियल (वापरकर्तानाव, पासवर्ड, आमच्या डेटाबेस सर्व्हरचा IP आणि आमच्या डेटाबेस सर्व्हरचे नाव), तसेच आम्ही वापरत असलेला ड्रायव्हर निर्दिष्ट करतो. कनेक्शन स्ट्रिंगचे स्वरूप प्रत्येक डेटाबेसमध्ये भिन्न असते, तुमची कनेक्शन स्ट्रिंग कशी असावी याचे विहंगावलोकन करण्यासाठी connectionstrings.com पहा.

ड्रायव्हरबद्दल: ड्रायव्हरला तुम्ही वापरत असलेल्या डेटाबेसच्या आवृत्तीशी जुळणे आवश्यक आहे. वरील उदाहरणामध्ये आम्ही MS SQL सर्व्हर 2011 वापरतो त्यामुळे आम्हाला SQL सर्व्हर नेटिव्ह क्लायंट 11 ड्रायव्हरची आवश्यकता आहे. तुम्ही पायथन स्क्रिप्ट चालवत असलेल्या मशीनवर हा ड्रायव्हर थांबलेला असणे आवश्यक आहे. ‘ODBC डेटा स्रोत’ (ड्रायव्हर्स टॅब अंतर्गत) शोधून कोणते ड्रायव्हर्स स्थापित केले आहेत ते तपासा. तुमचा आवश्यक ड्रायव्हर इन्स्टॉल नसेल तर तुम्ही ते सहज डाउनलोड करू शकता (उदा. https://www.microsoft.com/en-us/download/details.aspx?id=36434).

1.2 आमच्या कनेक्शन स्ट्रिंगसह डेटाबेस इंजिन तयार करणे

एकदा कनेक्शन स्ट्रिंग वैध झाल्यानंतर डेटाबेस इंजिन तयार करणे सोपे होते. मी सहसा खालीलप्रमाणे माझे तयार करतो.

import sqlalchemy
dbEngine = sqlalchemy.create_engine(constring, connect_args={'connect_timeout': 10}, echo=False)

प्रतिध्वनी सत्य वर सेट केल्याने तुम्हाला कार्यान्वित केलेल्या सर्व क्वेरी पाहण्याची परवानगी मिळते. तुम्ही स्ट्रिंग 'डीबग' वर इको सेट केल्यास परिणाम पंक्ती देखील मुद्रित केल्या जातील.

1.3 आमच्या डेटाबेस इंजिनची चाचणी करत आहे

तुमच्या कनेक्शनची चाचणी घेण्यासाठी खालील छोटी स्क्रिप्ट वापरा. जर सर्व काही ठीक झाले तर इंजिन वैध आहे हे मुद्रित केले पाहिजे. जर काही चूक झाली असेल तर ती एरर प्रिंट करेल.

try:
    with dbEngine.connect() as con:
        con.execute("SELECT 1")
    print('engine is valid')
except Exception as e:
    print(f'Engine invalid: {str(e)}')

2. जलद समाविष्ट करण्याच्या पद्धतींचे चार स्तर

आता आम्ही आमच्या डेटाबेसशी कनेक्ट करण्यात सक्षम झालो आहोत आम्ही डेटाबेसमध्ये डेटा घालणे सुरू करू शकतो. आम्ही डेटा घालण्याचे 4 मार्ग एक्सप्लोर करू, ज्याचा शेवट सर्वात जलद आहे. या लेखाच्या शेवटी तुम्हाला पांडाच्या to_sql पद्धतीचा तपशीलवार सारांश मिळेल.

2.1 व्हॅनिला to_sql पद्धत

तुम्ही या पद्धतीला डेटाफ्रेमवर कॉल करू शकता आणि डेटाबेस-इंजिन पास करू शकता. ही एक अगदी सोपी पद्धत आहे जी आम्ही प्रत्येक थेंब गती बाहेर काढण्यासाठी बदल करू शकतो. खालील उदाहरणात आम्ही डेटाफ्रेम तयार करतो आणि फक्त अपलोड करतो.

import pandas as pd
# 1. Create a dataframe
df = pd.DataFrame({'numbers': [1, 2, 3], 'colors': ['red', 'white', 'blue']})
print(df.head())
# dataframe looks like:
   numbers colors
0        1    red
1        2  white
2        3   blue
# 2. Upload this dataframe
df.to_sql(con=dbEngine, schema="dbo", name="colortable", if_exists="replace", index=False)

जसे आपण पाहू शकता की आम्हाला फक्त आमचे कनेक्शन निर्दिष्ट करावे लागेल (आम्ही आधी तयार केलेले डेटाबेस इंजिन), ज्या स्कीमामध्ये आम्हाला आमचे नवीन टेबल आणि आमचे नवीन टेबलनाव ठेवायचे आहे. याशिवाय, निर्दिष्ट केलेले schema.tablename आधीपासून अस्तित्वात असल्यास काय करावे हे आम्ही ठरवू शकतो (आमच्या बाबतीत बदला) आणि आम्हाला टेबलवर निर्देशांक ठेवायचा आहे का (पॅरामीटर्सच्या संपूर्ण सूचीसाठी पुढे पहा).

वरील उदाहरण हा डेटा घालण्याचा सर्वात सोपा मार्ग आहे परंतु सर्वात हळू देखील आहे. समस्या अशी आहे की आम्ही संपूर्ण डेटाफ्रेम एकाच वेळी लिहितो, प्रत्येक रेकॉर्डसाठी इन्सर्ट स्टेटमेंट तयार करतो. एका लहान टेबलवर, आमच्या कलर टेबलप्रमाणे, ही फार मोठी समस्या नाही, परंतु मोठ्या टेबलांवर ती नक्कीच आहे.

2.2 चंकिंग

जर आपण चंकसाईज जोडला तर आमचा कोड थोडा चांगला चालेल. हे निर्दिष्ट चंकसाईजच्या बॅचमध्ये डेटा लिहिते, भरपूर मेमरी वाचवेल.

df_large.to_sql(con=dbEngine, schema="dbo", name="largetable", if_exists="replace", index=False, chunksize=1000)

तरीही ही पद्धत आमच्या टेबलमधील प्रत्येक रेकॉर्डसाठी एक इन्सर्ट-स्टेटमेंट तयार करते जे अजूनही खूप मंद आहे.

2.3 मल्टी-इन्सर्ट

‘मल्टी’-पद्धत जोडल्याने इन्सर्टेशन गती खूप सुधारेल. प्रति रेकॉर्ड इन्सर्ट स्टेटमेंट लिहिण्याऐवजी आम्ही आता एका स्टेटमेंटमध्ये अनेक पंक्ती पाठवू शकतो. डेटाबेस प्रति रेकॉर्ड एका ऑपरेशनच्या ऐवजी एका ऑपरेशनमध्ये अनेक रेकॉर्डवर प्रक्रिया करू शकतो.

महत्त्वाचे: ही पद्धत कार्य करणार नाही आणि Microsoft SQL सर्व्हर डेटाबेससाठी आवश्यक नाही. कृपया पद्धत 4 पहा.

df_target.to_sql(con=dbEngine, schema="dbo", name="targettable", if_exists="replace", index=False, chunksize=1000, method='multi')

आदर्श भाग आकार आपल्या सारणीच्या परिमाणांवर अवलंबून असतो. पुष्कळ स्तंभ असलेल्या टेबलला फक्त 3 असलेल्या टेबलपेक्षा लहान भाग-आकाराची आवश्यकता असते.

अनेक डेटाबेससाठी डेटाबेसवर लिहिण्याचा हा वेगवान मार्ग आहे. मायक्रोसॉफ्ट सर्व्हरसाठी, तथापि, अद्याप एक वेगवान पर्याय आहे.

2.4 SQL सर्व्हर fast_executemany

SQLAlchemy 1.3 आम्हाला SQL सर्व्हरसाठी dbEngine तयार करण्यासाठी fast_executemany पर्याय प्रदान करते. ही पद्धत SQL सर्व्हर डेटाबेसवर डेटाफ्रेम लिहिण्याचा सर्वात जलद मार्ग आहे.

dbEngine = sqlalchemy.create_engine(constring, fast_executemany=True, connect_args={'connect_timeout': 10}, echo=False) 
df_target.to_sql(con=dbEngine, schema="dbo", name="targettable", if_exists="replace", index=False, chunksize=1000)

वरील कोडमध्ये तुम्हाला दिसेल की आम्हाला आमचे डेटाबेस इंजिन थोडे समायोजित करावे लागेल; आपल्याला fast_executemany पर्याय जोडावा लागेल. तेच आहे.
मग, जेव्हा आपण डेटाबेसवर आपला डेटाफ्रेम लिहितो, तेव्हा आपल्याला फक्त एक गोष्ट लक्षात ठेवायची असते की आपण आमची पद्धत (किंवा सेट पद्धत=कोणतीही) निर्दिष्ट करत नाही. हे असे आहे कारण fast_executemany डीफॉल्टनुसार मल्टी-इन्सर्ट केलेले आहे.

निष्कर्ष

या सोप्या अपग्रेडसह तुमच्याकडे आता तुमची पायथन-टू-डेटाबेस कनेक्शन सुधारण्यासाठी साधने आहेत. मी खाली उदाहरण कोड आणि चरणांसह सारांश समाविष्ट केला आहे. आनंदी कोडिंग!

-माईक

P.S: मी काय करत आहे? "माझ्या मागे ये"!

विहंगावलोकन: to_sql पॅरामीटर्स

to_sql फंक्शनच्या सर्व सेटिंग्जचे विहंगावलोकन आणि स्पष्टीकरण खाली.

• con (sqlalchemy engine)
  डेटाबेस कनेक्शन (sqlalchemy engine)
• नाव (str): आवश्यक
  तुम्हाला ज्या टेबलवर लिहायचे आहे त्याचे नाव
• स्कीमा (str): डीफॉल्ट: डीफॉल्ट डेटाबेस स्कीमा (dbo)
  तुम्हाला ज्या स्कीमावर लिहायचे आहे त्याचे नाव.
• if_exists (str):डिफॉल्ट: ‘अयशस्वी’
  निर्दिष्ट टेबल आधीपासून अस्तित्वात असल्यास काय करावे?
  - 'अयशस्वी': त्रुटी फेकते
  - 'अ‍ॅपेंड': निर्दिष्ट टेबलमध्ये डेटा जोडते
  - 'बदला': टेबल पूर्णपणे बदला (चेतावणी: हे धोकादायक आहे)
• इंडेक्स (बूल): डीफॉल्ट: ट्रू
  जर इंडेक्स ट्रू वर सेट केला असेल तर तो ‘आयडी’ नावाचा अतिरिक्त कॉलम तयार करतो, तो अनुक्रमित केला जातो.
• index_label (str किंवा sequence):डिफॉल्ट: काहीही नाही
  अनुक्रमणिका स्तंभांसाठी स्तंभ लेबल. डीफॉल्ट काहीही नाही.
• खंड आकार (इंट): डीफॉल्ट: काहीही नाही
  आकाराच्या बॅचमध्ये पंक्ती लिहा [खंड आकार]. तुमच्याकडे अनेक रेकॉर्ड्स अपलोड करणे आवश्यक असल्यास ही चांगली कल्पना आहे. यामुळे मेमरी वाचते. काहीही नसल्यास: सर्व पंक्ती एकाच वेळी लिहितात
• dtype (dict किंवा स्केलर): डीफॉल्ट काहीही नाही
  डेटाटाइप निर्दिष्ट करा
  स्केलर निर्दिष्ट केले असल्यास: हे लिहिण्यापूर्वी डेटाफ्रेममधील सर्व स्तंभांवर हा डेटाटाइप लागू करतो डेटाबेस. प्रति स्तंभ निर्दिष्ट डेटाटाइपसाठी एक शब्दकोश प्रदान करा जिथे डेटाफ्रेम स्तंभनावे की आहेत. मूल्ये sqlalchemy प्रकार आहेत (उदा. sqlalchemy. Float इ.)
• पद्धत (str): डीफॉल्ट काहीही नाही
  SQL इन्सर्शन क्लॉज नियंत्रित करते
  - काहीही नाही: मानक SQL इन्सर्ट क्लॉज वापरते (प्रति पंक्ती एक)
  - 'मल्टी': एकाच इन्सर्ट क्लॉजला अनेक मूल्ये पास करते.