Python連接數(shù)據(jù)庫的方法

本篇文章為大家展示了Python連接數(shù)據(jù)庫的方法，代碼簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

pyodbc

連接兩種技術的橋梁是pyodbc，該庫可以輕松訪問ODBC數(shù)據(jù)庫。

ODBC（開放數(shù)據(jù)庫連接的簡稱）是一種用于訪問數(shù)據(jù)庫的標準化應用程序編程接口（API），由90年代初的SQLAccess組開發(fā)。兼容的數(shù)據(jù)庫管理系統(tǒng)（DBMS）包括：

· IBM Db2

· MySQL

· Oracle

· MS Access

·MS SQL服務器

本文將使用MS SQL服務器。在多數(shù)情況下，該服務器可以直接轉移，與任何符合ODBC的數(shù)據(jù)庫都可一起使用。唯一需要更改的是連接設置。

連接

首先，要創(chuàng)建與SQL 服務器的連接，可以通過pyodbc.connect實現(xiàn)。在此函數(shù)中，還須傳遞連接字符串。此連接字符串必須指定DBMS驅動程序、服務器、要連接的特定數(shù)據(jù)庫以及連接設置。

因此，假設要連接到服務器UKXXX00123,45600和數(shù)據(jù)庫DB01，需要使用SQL Server Native Client 11.0。從內部連接使得連接被信任，無需輸入用戶名和密碼。

cnxn_str = ("Driver={SQLServer Native Client 11.0};"
"Server=UKXXX00123,45600;"
"Database=DB01;"
"Trusted_Connection=yes;")

現(xiàn)在，連接已初始化為：

cnxn = pyodbc.connect（cnxn_str）

如果不通過受信任的連接訪問數(shù)據(jù)庫，則需要輸入通常用于通過SQLServer Management Studio（SSMS）訪問服務器的用戶名和密碼。例如，如果用戶名是JoeBloggs，而密碼是Password123，則應立即更改密碼。更改密碼之前，可以按照如下進行連接：

cnxn_str = ("Driver={SQLServer Native Client 11.0};"
"Server=UKXXX00123,45600;"
"Database=DB01;"
"UID=JoeBloggs;"
"PWD=Password123;")cnxn = pyodbc.connect(cnxn_str)

現(xiàn)在我們已連接到數(shù)據(jù)庫，可以開始通過Python執(zhí)行SQL查詢。執(zhí)行查詢

SQL 服務器上運行的每個查詢都包含游標初始化和查詢執(zhí)行。如果要在服務器內部進行任何更改，還需要將這些更改提交到服務器。

先來初始化游標：

cursor = cnxn.cursor()

現(xiàn)在，每當要執(zhí)行查詢時，都要使用此游標對象。

從名為“customers”表中選擇前1000行：

cursor.execute("SELECTTOP(1000) * FROM customers")

執(zhí)行該操作，但這發(fā)生在服務器內部，實際上什么也沒有返回到Python。讓我們一起看看從SQL中提取的這些數(shù)據(jù)。

提取數(shù)據(jù)

要從SQL中提取數(shù)據(jù)到Python中，需要使用pandas。Pandas提供了一個非常方便的函數(shù)read_sql，該函數(shù)可以從SQL讀取數(shù)據(jù)。read_sql需要查詢和連接實例cnxn，如下所示：

data =pd.read_sql("SELECT TOP(1000) * FROM customers", cnxn)

這會返回到包含“customers”表中前1000行的數(shù)據(jù)框。

在SQL中變更數(shù)據(jù)

現(xiàn)在，如果要變更SQL中的數(shù)據(jù)，需要在原始的初始化連接后添加另一步，執(zhí)行查詢過程。在SQL中執(zhí)行查詢時，這些變更將保存在臨時存在的空格中，而不是直接對數(shù)據(jù)進行更改。

為了讓變更永久生效，必須提交變更。連接firstName和lastName列，創(chuàng)建fullName列。

cursor = cnxn.cursor()# firstalter the table, adding a column
cursor.execute("ALTER TABLE customer " +
"ADD fullNameVARCHAR(20)")# now update that column to contain firstName
+ lastNamecursor.execute("UPDATEcustomer " +
"SET fullName = firstName + " " + lastName")

此時，fullName并不存在于數(shù)據(jù)庫中。必須提交這些變更，讓變更永久生效：

cnxn.commit()

下一步

一旦執(zhí)行了需要執(zhí)行的任何操作任務，就可以把數(shù)據(jù)提取到Python中，也可以將數(shù)據(jù)提取到Python中，在Python中進行操作。

無論采用哪種方法，一旦Python中有了數(shù)據(jù)，就可以做很多以前無法做到的事情。

也許需要執(zhí)行一些日常報告，通常使用這些報告查詢SQL 服務器中的最新數(shù)據(jù)，計算基本統(tǒng)計信息，然后通過電子郵件發(fā)送結果。如何自動化這一過程呢？

# imports for SQL data part
import pyodbc
from datetime import datetime,timedelta
import pandas as pd
# imports forsending email
from email.mime.text importMIMEText
fromemail.mime.multipart importMIMEMultipart
import smtplib
date = datetime.today() -timedelta(days=7) # get the date 7 days ago
date = date.strftime("%Y-%m-%d") # convert to format yyyy-mm-dd
cnxn = pyodbc.connect(cnxn_str) # initialise connection (assume we havealready defined cnxn_str)
# build up ourquery string
query = ("SELECT *FROM customers "
f"WHERE joinDate > '{date}'")
# execute thequery and read to a dataframe in Python
data = pd.read_sql(query, cnxn)
del cnxn # close the connection
# make a fewcalculations
mean_payment = data['payment'].mean()
std_payment = data['payment'].std()
# get maxpayment and product details
max_vals = data[['product', 'payment']].sort_values(by=['payment'], ascending=False).iloc[0]
# write an emailmessage
txt = (f"Customerreporting for period {date} - {datetime.today().strftime('%Y-%m-%d')}.\n\n"
f"Mean payment amounts received: {mean_payment}\n"
f"Standard deviation of payment amounts: {std_payments}\n"
f"Highest payment amount of {max_vals['payment']} "
f"received from {max_vals['product']} product.")
# we will built themessage using the email library and send using smtplib
msg =MIMEMultipart()
msg['Subject'] ="Automatedcustomer report" # set emailsubject
msg.attach(MIMEText(txt)) # add text contents
# we will sendvia outlook, first we initialise connection to mail server
smtp = smtplib.SMTP('smtp-mail.outlook.com', '587')
smtp.ehlo() # say hello to the server
smtp.starttls() # we will communicate using TLSencryption
# login to outlookserver, using generic email and password
smtp.login('joebloggs@outlook.com', 'Password123')
# send email to ourboss
smtp.sendmail('joebloggs@outlook.com', 'joebloggsboss@outlook.com', msg.as_string())
# finally,disconnect from the mail server
smtp.quit()

至此，任務結束！運行此代碼快速提取前一周的數(shù)據(jù)，計算關鍵指標，并把摘要發(fā)送給老板。通過簡單的步驟，我們了解了如何通過使用SQL和Python的集成來快速建立更高效、自動化的工作流程。不僅僅可以用來做本例中的事，它還有很多用途等你開發(fā)。

上述內容就是Python連接數(shù)據(jù)庫的方法，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業(yè)資訊頻道。