Shulou(Shulou.com)05/31 Report--

This article focuses on "how to quickly establish a workflow through the integration of SQL and Python". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to quickly establish a workflow through the integration of SQL and Python.

Almost everyone is using SQL and Python,Python are excellent all-star languages for data analysis, machine learning, and web development, while SQL is the de facto standard for databases. What happens if you combine the two?

In fact, it is not difficult to combine the two. We can quickly take advantage of the dynamic nature of Python to control and build SQL queries. Once the setup is complete, we do not need to do anything.

The combination of these two tools can be said to be the strongest partner, automation and efficiency have reached a new level.

Pyodbc

The bridge between the two technologies is pyodbc, which provides easy access to ODBC databases.

ODBC (Open Database Connectivity for short) is a standardized application programming interface (API) for accessing databases, developed by the SQLAccess group in the early 1990s. Compatible database management systems (DBMS) include:

IBM Db2

MySQL

Oracle

MS Access

MS SQL server

In most cases, the server can be transferred directly and can be used with any ODBC-compliant database. The only thing that needs to be changed is the connection settings.

Connect

First, to create a connection to the SQL server, you can do it through pyodbc.connect. In this function, you also have to pass the connection string. This connection string must specify the DBMS driver, server, specific database to connect to, and connection settings.

Therefore, suppose you need to use SQL Server Native Client 11.0 to connect to the server UKXXX00123,45600 and the database DB01. From the internal connection, the connection is trusted without entering a user name and password.

Cnxn_str = ("Driver= {SQLServer Native Client 11.0};"Server=UKXXX00123,45600;"Database=DB01;"Trusted_Connection=yes;") now the connection has been initialized to: cnxn = pyodbc.connect (cnxn_str)

If you do not access the database through a trusted connection, you need to enter the user name and password that is typically used to access the server through SQLServer Management Studio (SSMS). For example, if the user name is JoeBloggs and the password is Password123, you should change the password immediately. Before changing the password, you can connect as follows:

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

Now that we are connected to the database, we can start executing the SQL query through Python.

Execute query

Each query run on the SQL server contains cursor initialization and query execution. If you want to make any changes within the server, you also need to commit those changes to the server.

Initialize the cursor first:

Cursor = cnxn.cursor ()

You now use this cursor object whenever you want to execute a query.

Select the first 1000 rows from the table named "customers":

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

Do this, but this happens inside the server, and actually nothing is returned to the Python. Let's take a look at the data extracted from SQL.

Extract data

To extract data from SQL to Python, you need to use pandas. Pandas provides a very convenient function, read_sql, which can read data from SQL. Read_sql needs to query and connect to the instance cnxn, as shown below:

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

This returns to the data box containing the first 1000 rows of the "customers" table.

Change data in SQL

Now, if you want to change the data in the SQL, you need to add another step after the original initialization of the connection and execute the query process. When you execute a query in SQL, these changes are saved in temporary spaces rather than making changes to the data directly.

In order for the changes to take effect permanently, the changes must be submitted. Connect the firstName and lastName columns to create the fullName column.

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")

At this point, fullName does not exist in the database. These changes must be submitted for them to take effect permanently:

Cnxn.commit ()

Next step

Once you have performed any operational tasks that need to be performed, you can extract the data into Python, or you can extract the data into Python and manipulate it in Python.

Either way, once you have data in Python, you can do a lot of things that you couldn't do before.

You may need to perform some daily reports that are typically used to query the latest data in the SQL server, calculate basic statistics, and then email the results. How to automate this process?

# 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 ('% Ymuri% mmurf% 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 () so far I believe you have a deeper understanding of "how to quickly establish a workflow through the integration of SQL and Python". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.