Python : Python with Database

Python with SQL Server

Python with My SQL

Python with Snow Flake

Python with Mongo DB

Python with SQL Server:

This method relies on “pyodbc” library to set up the Python SQL Server Integration.

There are 2 ways to establish this Python SQL Server connection:

Approach 1 to Setup Python SQL Server Connection: You can depend on a trusted internal connection using the following code:

cnxn_str = ("Driver={SQL Server Native Client 11.0};" "Server=USXXX00345,67800;" "Database=DB02;" "Trusted_Connection=yes;") cnxn = pyodbc.connect(cnxn_str)

2. You don’t have a trusted internal connection and wish to set up the required SQL Server connection using SQL Server Management Studio (SSMS). This will require you to enter your username (say, Alex) and password(Alex123) as shown in the following code:

cnxn_str = ("Driver={SQL Server Native Client 11.0};" "Server=USXXX00345,67800;" "Database=DB02;" "UID=Alex;" "PWD=Alex123;") cnxn = pyodbc.connect(cnxn_str)

Run an SQL Query

Now, every query that you will perform on the SQL Server will involve a cursor initialization and query execution sequence. Moreover, any changes made inside the SQL Server must also reflect in Python

cursor = cnxn.cursor()

cursor.execute("SELECT TOP(100) * FROM associates")

data = pd.read_sql("SELECT TOP(100) * FROM associates", cnxn)

Apply Modifications in SQL Server

cursor = cnxn.cursor() # first alter the table, adding a column cursor.execute("ALTER TABLE associates " + "ADD fullName VARCHAR(20)") # now update that column to contain firstName + lastName cursor.execute("UPDATE associate " + "SET fullName = firstName + " " + lastName")

cnxn.commit()

Automate the Python SQL Server Functioning

imports for SQL data part import pyodbc from datetime import datetime, timedelta import pandas as pd # imports for sending email from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart 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 have already defined cnxn_str) # build up our query string query = ("SELECT * FROM associates " f"WHERE joinDate > '{date}'") # execute the query and read to a dataframe in Python data = pd.read_sql(query, cnxn) del cnxn # close the connection # make a few calculations mean_payment = data['payment'].mean() std_payment = data['payment'].std() # get max payment and product details max_vals = data[['product', 'payment']].sort_values(by=['payment'], ascending=False).iloc[0] # write an email message txt = (f"Customer reporting for period {date} - {datetime.today().strftime('%Y-%m-%d')}.nn" 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 the message using the email library and send using smtplib msg = MIMEMultipart() msg['Subject'] = "Automated customer report" # set email subject msg.attach(MIMEText(txt)) # add text contents # we will send via 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 TLS encryption # login to outlook server, using generic email and password smtp.login('Alex@outlook.com', 'Alex123') # send email to our boss smtp.sendmail('Alex@outlook.com', 'boss@outlook.com', msg.as_string()) # finally, disconnect from the mail server smtp.quit()

Python with MySQL:

from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        create_db_query = "CREATE DATABASE online_movie_rating"
        with connection.cursor() as cursor:
            cursor.execute(create_db_query)

            connection.commit()

except Error as e:
    print(e)

Python with Snowflake:

pip install snowflake-connector-python

import snowflake.connector

https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-example