Python 5. Database Connections with Python

I have a lot of data in Databases, MS SQL for some of the Revit Model Extract data, MySQL for my WordPress sites and some of my data and PostgreSQL for my OpenMaint data.
So linking to the Databases via Python to be able to extract, interrogate and possibly update the databases is another way to access this data. It was a real challenge getting Knime to connect to the databases . In fact, I still haven’t been able to connect to the MySQL db’s.

Local DB’s

MS SQL resides on my laptop, it is the free Express version so is a stand-alone database. I also have a copy of PostgeSQL db on my local drive for testing OpenMaint.

Remote DB’s

The WordPress MySQL databases reside on my contabo VPS (Virtual Private Server), also on the AwardSpace hosted website (I can also setup PostgreSQL db’s here too).
My PostgreSQL databases are sitting on, 1/ My PC (for using localhost setup), on the contabo VPS and also on my Amazon WS Server.

SQLite DB for testing

Creating SQLite Database for in memory or small file.  I found this video easy to follow for testing. https://www.youtube.com/watch?v=pd-0G0MigUA

I may consider using this method in the future as it seems to be easy to setup. For small case examples it could be a nice solution.

Summary

I have been able to successfully connect to the two databases on my local drive.

1. MS SQL Express. Connected easily
2. PostgreSQL.  Connected easily
3. I also am able to connect to NoSQL Mongo DB using PyMongo. See this article.

I have had no success, so far, with connecting to the remote databases.

A couple of thoughts on why that might be:

1. The remote databases may need to be accessed locally. So setting up Python on the remote server may be a solution, by pushing it to the realm of local drive access. But that isn’t what I want.
2. The AwardSpace hosted website actively blocked access to the database. This may be part of the setup of the Hosting Service.
3. Looking at a Table displayed on a webpage on my Contabo VPS with WordPress, this is using a local connection to access the database.
4. More research needs to be done to be able to link to a remote database. I had this issue in Knime as well.

Code that works:

MS SQL Express

# create the connection
# https://www.youtube.com/watch?v=uoLE14ZLkUM
#  lesson 8 here    https://nbviewer.jupyter.org/urls/bitbucket.org/hrojas/learn-pandas/raw/master/lessons/08%20-%20Lesson.ipynb
# This is a connection to the local (on same computer) MS SQL Expresss Database.
# It is calling Database 'RTV_ReporterPro_SPM'
# It is calling Table Cat_Rooms

import pandas as pd
import pyodbc
# Parameters
server = 'DESKTOP-4K3CLEV\SQLEXPRESS'
db = 'RTV_ReporterPro_SPM'

conn=pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + db + ';Trusted_Connection=yes')

# query db- gets only 5 rows
sql = """
SELECT top 5 *
FROM Cat_Rooms
"""
df = pd.io.sql.read_sql(sql, conn)
print(df)

PostgreSQL

Note, I am also testing the logging function in Python in the code below.

# https://wiki.postgresql.org/wiki/Using_psycopg2_with_PostgreSQL

import psycopg2
import logging
logging.basicConfig(filename='logfile1.txt', level=logging.INFO,
                  format='%(asctime)s:%(levelname)s:%(message)s')

host = 'localhost'
port="5432"
user = 'postgres'
password = 'xxx'
db = 'om'
conn = psycopg2.connect(database=db, user=user, password=password, host=host, port=port)
cursor = conn.cursor()
cursor.execute('SELECT "Code", "Description", "Name" FROM public."Building" WHERE "Id" = 261251')    
row = cursor.fetchone()
conn.close()
#print(row)
logging.info(row)

  SQLite

This code is a bit messy but based on the Video tutorial. So Building Table, then commenting out that code (as table built) then testing some insert methods.

# https://www.youtube.com/watch?v=pd-0G0MigUA
# SQLite3 is lightweight database either held in memory or to a file. If need bigger then make a
import sqlite3
from employee import Employee # this is importing CLASS from file in the same directory- in this instance the file employee.py

conn = sqlite3.connect('employee.db')   #(':memory:')
c = conn.cursor()
# Do this first to create Table structure in Database - Then Comment out,
# as it has been create (cannot recreate as already created after first use
# c.execute("""CREATE TABLE employees (
#             first text,
#             last text,
#             pay integer
#             )""")

# c.execute("INSERT INTO employees VALUES ('Mary','Shambler',70000) ")
# conn.commit()

# Creating functions for insert/update/delete
def insert_emp(emp):
    with conn:  # this does "Commit automatically
        c.execute("INSERT INTO employees VALUES (:first, :last, :pay)", {'first': emp.first, 'last': emp.last, 'pay': emp.pay})
def get_emps_by_name(lastname):
    c.execute("SELECT * FROM employees WHERE last=:last", {'last': lastname})
    return c.fetchall()
def update_pay(emp, pay):
    with conn:
        c.execute("""UPDATE employees SET pay = :pay
                    WHERE first = :first AND last = :last""",
                  {'first': emp.first, 'last': emp.last, 'pay': pay})
def remove_emp(emp):
    with conn:
        c.execute("DELETE from employees WHERE first = :first AND last = :last",
                  {'first': emp.first, 'last': emp.last})
# Defining employees
emp_1 = Employee('John', 'Doe', 80000)
emp_2 = Employee('Jane', 'Doe', 90000)

# inserting into the database with created functions above
# insert_emp(emp_1)
# insert_emp(emp_2)

emps = get_emps_by_name('Doe')
print(emps)

update_pay(emp_2, 95000)
remove_emp(emp_1)

# emps = get_emps_by_name('Doe')
# print(emps)
# c.execute("SELECT *FROM employees WHERE last='Shambler'")
# print(c.fetchall())

conn.commit()# use this too commit to the database.
conn.close()