How to use python to query your RMS

I have worked with dozens of different police departments in my career. Often a first step is helping their analyst(s) figure out how to properly query their record management system (RMS). Here I will outline my steps to do this, as it is fairly standard approach I take across different police departments.

The majority of them have some ad-hoc approach set up where they manually use Crystal Reports to dump data into a csv file, and then do analytics on that csv file. This is inefficient, modifying Crystal Reports is quite painful, and impedes truly automating the process.

There is an easier way that can cut out the “pointing and clicking buttons in Crystal Reports”, and allows one to fully automate grabbing the data. For example, say the Captain of the Detectives wanted you to send him a report once a week for all open assigned cases. In the scenario where you need to manually click around in Crystal Reports, you need to physically be in the office. Whereas what I am going to show, you could fully automate this task, and it would work get the Captain his stats even if you are not at the office.

Crystal Reports under the hood uses SQL – structured query language – to query the database and generate files. (Your RMS under the hood is a relational database.) You can use those same connections to query data directly in other software, like python or R (or PowerBI or Access). Doing queries directly you will understand the data much better, and be able to answer ad-hoc queries much faster.

The recipe for me to figure out how to do this is easy enough for me to write in a post:

Screenshot of ODBC Admin for windows

The next step shows how to generate queries on your RMS using python. (If this is too advanced, check out my entry level python book to help you get started with python.)

Then in python, to query the database you can use the pyodbc library. It will often look something like:

import pandas as pd
import pyodbc

print(pyodbc.drivers()) # to see driver versions installed

This will show a list of drivers, often something like ['SQL Server','ODBC Driver 17 for SQL Server','SQL Server Native Client 11.0','Microsoft Access Driver (*.mdb, *.accdb)'] etc. These are the same ODBC drivers that were listed in the ODBC Data Source Administrator. So you will need to figure out which one corresponds to your RMS database. (If you want a nicer GUI application to check out the tables than Crystal Reports, you can use SQL Server Management Studio.)

Then the next step is to set up code to create the connection. It will often look something like below in python (note you shouldn’t save passwords directly in text files, so later create environment variables to save your password in)

usr = 'username'
pwd = 'password'
server = 'server from configure'
dbname = 'databasename'

conn = pyodb.connect("Driver={ODBC Driver 17 for SQL Server};"
                    f"Server={sever};"
                    f"Database={dbname};"
                    f"UID={usr};"
                    f"PWD={pwd};")

If the string to connect to the database is wrong, you might get different error messages at this point. For example, if the server name is not quite correct, you may get “Server is not found”. At the command line, you can also try ping fill_in_serveraddress to see if you can connect. It may be you need to add a trailing domain name like server.city.gov for example.

Once you have the connection established, you can then do a test query. A simple way to start is to query the different table names available. Many databases you can use the query SHOW TABLES;, but SQL Server is the more complicated query below:

query = """
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
"""

tables = pd.read_sql(query,conn)

And this will produce a pandas dataframe for the tables that exist in your RMS system.

Note be careful with this ability. If this is the production database (and not a separate reporting server), you will want to avoid making large queries. I discuss tips like this and more in my SQL chapter in my intro python book, click the Store tab at the top of the page for purchase options. Here is a preview of the first two chapters (if on a phone, you can view the PDF directly here):