Load data from a MySQL or SQL SERVER database - lvphj/epydemiology GitHub Wiki

Python function to read data from a MySQL or SQL SERVER database into a Pandas dataframe.

phjGetDataFromDatabase()

import pandas as pd
import epydemiology as epy

df = epy.phjGetDataFromDatabase(phjQueryStr = None,
                                phjQueryPathAndFileName = None,
                                phjPrintResults = False)

Description

The function is used to query MySQL or SQL SERVER databases using an SQL query that can be entered as a string or is stored as a text file. As the function runs, the user will be prompted to enter all other required parameters including server address, username and password details. A maximum of three attempts allowed to enter correct login information. The password is entered and remains securely obscured on-screen.

Function parameters

  1. phjQueryStr

    A string representing a SELECT...FROM query. Only SELECT query are allowed to run. A query entered as a text file (below) is given preference over a query entered as a string.

  2. phjQueryPathAndFilename

    The full path and filename of the SQL text file containing the SQL query. A query entered as a text file (below) is given preference over a query entered as a string.

  3. phjPrintResults (default = False)

    Print the imported results.

Exceptions raised

None.

Returns

Pandas dataframe containing data read from database.

Other notes

None.

Examples

Example 1 – Query stored in file

An example of the function in use is given below. If the SQL query to be used to query a SQL SERVER database is saved as a text file named 'theSQLQueryFile.mssql' on the Desktop, the function can be used to import returned data using:

# The following external libraries are imported automatically but are incuded here for completeness.
import pandas as pd
import pymysql
import pymssql
import epydemiology as epy

myDF = epy.phjGetDataFromDatabase(phjQueryPathAndFile = '/Users/username/Desktop/theSQLQueryFile.mssql',
                                  phjPrintResults = True)

Example 2 – Query entered directly in function call

# The following external libraries are imported automatically but are incuded here for completeness.
import pandas as pd
import pymysql
import pymssql
import epydemiology as epy

myDF = epy.phjGetDataFromDatabase(phjQueryStr = 'SELECT * FROM Table1',
                                  phjPrintResults = True)