{ "cells": [ { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 11, "hidden": false, "row": 0, "width": 12 }, "report_default": {} } } } }, "source": [ "\n", "
\n", " \n", " Website\n", " \n", "
\n", "\n", "Ghani, Rayid, Frauke Kreuter, Julia Lane, Adrianne Bradford, Alex Engler, Nicolas Guetta Jeanrenaud, Graham Henke, Daniela Hochfellner, Clayton Hunter, Brian Kim, Avishek Kumar, and Jonathan Morgan." ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 4, "hidden": false, "row": 11, "width": 4 }, "report_default": {} } } } }, "source": [ "# Databases\n", "---" ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 10, "hidden": false, "row": 15, "width": 12 }, "report_default": {} } } } }, "source": [ "## Table of Contents\n", "\n", "- [Introduction](#Introduction)\n", " - [Learning objectives](#Learning-objectives)\n", " - [Methods](#Methods)\n", "\n", "- [Connection information](#Connection-information)\n", "- [GUI clients](#GUI-clients)\n", "\n", " - [GUI - pgAdmin](#GUI---pgAdmin)\n", "\n", "- [Python database clients](#Python-database-clients)\n", "\n", " - [Python - `psycopg2`](#Python---psycopg2)\n", " - [Python - `SQLAlchemy`](#Python---SQLAlchemy)\n", " - [Python - `pandas`](#Python---pandas)" ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 12, "hidden": false, "row": 25, "width": 12 }, "report_default": {} } } } }, "source": [ "## Introduction\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "Regardless of how you connect, most interactions with relational database management systems (RDBMS) are carried out via Structured Query Language (SQL). Many programming languages are more similar than different. \n", "SQL is genuinely different conceptually and syntactically.\n", "\n", "To make learning SQL easier, in this notebook we list a number of database clients you can use to connect to a PostgreSQL database and run SQL queries, so you can try them out and find one you prefer to use (we recommend pgAdmin if you are new to databases). \n", "\n", "We will follow the following sequence: \n", "1. Connection Information: We'll outline the information needed to connect to our class database server. \n", "2. Then, we'll briefly look at how to use a number of different SQL clients, and the pros and cons of each.\n", "3. Finally, we'll each pick one to connect and test before we move on to focusing on SQL." ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 9, "hidden": false, "row": 37, "width": 12 }, "report_default": {} } } } }, "source": [ "### Learning objectives\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "This notebook documents different database clients you can use to run SQL queries against the PostgreSQL database used for this class. PostgreSQL is an open source relational database management system (DBMS) developed by a worldwide team of volunteers.\n", "\n", "**Learning objectives: **\n", "\n", "- Understand options for connecting to a PostgreSQL database and running SQL, including pros and cons of each.\n", "- Pick an SQL interface to use while learning SQL." ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 12, "hidden": false, "row": 46, "width": 12 }, "report_default": {} } } } }, "source": [ "### Methods\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "We cover the following database clients in this notebook: \n", "\n", "1. Graphical User Interface (GUI) application 'pgAdmin'\n", "2. Using SQL in Python with:\n", "\n", " - Direct database connection - `psycopg2`\n", " - `SQLAlchemy`\n", " - `pandas`\n", "\n", "You can use any of these clients to run SQL in the database. Some are easier to use or better suited in certain situations over others. Each client's section includes information on good and bad points of each.\n", "\n", "If you are here to learn SQL, once you've looked over your options, pick one and proceed to the notebook \"Intro to SQL\" to learn more about the SQL language." ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 15, "hidden": false, "row": 58, "width": 12 }, "report_default": {} } } } }, "source": [ "## Connection information\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "All of the programs that connect to and query a database listed below need to be initially told how to connect to the database one wants to query. There are a set of common connection properties that are used to specify how to connect to a broad range of database servers:\n", "\n", "- **_host name_**: the network name of the database server one is connecting to, if the database is not on your local computer.\n", "- **_host port_**: the network port on which the database server is listening, if the database is not on your local computers. Most database server types have a default port that is assumed if you don't specify a port (5432 for PostgreSQL, for example, or 3306 for MySQL).\n", "- **_username_**: for databases that authenticate a connection based on user credentials, the username you want to use to connect.\n", "- **_password_**: for databases that authenticate a connection based on user credentials, the password you want to use to authenticate your username.\n", "- **_database name_**: The name of the database to which you want to connect.\n", "\n", "Not all setups will need all of these parameters to be specified to successfully connect to the database. For our class database in the ADRF, for example, we only need to specify:\n", "\n", "- **_host name_**: 10.10.2.10\n", "- **_database name_**: appliedda\n", "\n", "The database server listens on the default PostgreSQL port (5432), so no port is needed, and it authenticates the user based on whether that user has a linux user on the database server itself, rather than requiring a username and password (though access to schemas and tables inside are controlled by a more stringent set of per-user access privileges stored within the database)." ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 7, "hidden": false, "row": 73, "width": 12 }, "report_default": {} } } } }, "source": [ "## GUI clients\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "The first database clients we will cover are Graphical User Interface (GUI) clients. These clients are designed to be used with mouse and keyboard, and to simplify submitting queries to a database and interacting with the results.\n", "\n", "We will briefly cover connecting to a database and running a query in the GUI database client **_pgAdmin_**, a PostgreSQL-specific database client." ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 27, "hidden": false, "row": 80, "width": 12 }, "report_default": {} } } } }, "source": [ "### GUI - pgAdmin\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "pgAdmin is a PostgreSQL client written and suppported by the PostgreSQL community. It isn't the most beautiful program, but it is full-featured and available on many platforms. It doesn't let you connect to any databases other than PostgreSQL.\n", "\n", "**1. Running pgadmin** Double-click the \"`pgAdmin III`\" icon on the Desktop in the ADRF workspace.\n", "\n", "\n" ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 31, "hidden": false, "row": 107, "width": 12 }, "report_default": {} } } } }, "source": [ "**2. Creating a connection to the class database** In pgadmin:\n", "\n", "- Go to the file menu, then click on the \"Add Connection to Server\" option on top-left.\n", "- In the \"New Server Registration\" window that opens, set:\n", "\n", " - the \"Name\" to whatever you want to call the connection (we've used \"ADRF-appliedda\" in this example)\n", " - the \"Host\" to \"10.10.2.10\"\n", " - the \"Username\" field to your username (it won't let you leave it empty)\n", " - and uncheck the \"Store password\" checkbox\n", "\n", "" ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 26, "hidden": false, "row": 138, "width": 12 }, "report_default": {} } } } }, "source": [ "\n", "**3. Connecting to the class database 'appliedda'** \n", "\n", "- Double-click on the \"ADRF-appliedda\" link in the pane on the left, under \"Server Groups\" --> \"Servers (1)\".\n", "- If prompted for a password, just click \"OK\". You do not have to type any password\n", "- On successful connection, you should see items under \"ADRF-appliedda\", including \"Databases\". Click on the \"+\" sign to the left of \"Databases\".\n", "- Double-click on \"appliedda\" (it will probably have a red X on its icon, denoting that it is not currently connected.).\n", "\n", "\n", "\n" ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 34, "hidden": false, "row": 164, "width": 12 }, "report_default": {} } } } }, "source": [ "**4. Running a Query** \n", "Once you are connected to the \"appliedda\" database, you can start running queries using this GUI. Click on the button that looks like a magnifying glass with \"SQL\" inside it, at the top center of the window. Enter your SQL query in the \"SQL Editor\" in the top left.\n", "\n", "Let us count the number of rows in the dataset il_des_kcmo.il_qcew_employers: \n", "\n", " SELECT COUNT(*) \n", " FROM il_des_kcmo.il_qcew_employers;\n", "\n", "Now, press the green triangle \"play\" button to run the query. In the data output tab (down left)- you will see the results of this query.\n", "\n", "\n", "\n", "Other queries you can run: \n", "\n", "- Counting number of unique employers in the data:\n", "\n", " SELECT COUNT(distinct ein) \n", " FROM il_des_kcmo.il_qcew_employers;\n", "\n", "- Counting number of different records for each NAICS industry code: \n", "\n", " SELECT naics, COUNT(*) AS cnt \n", " FROM il_des_kcmo.il_qcew_employers \n", " GROUP BY naics;" ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 15, "hidden": false, "row": 198, "width": 12 }, "report_default": {} } } } }, "source": [ "## Python database clients\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "Apart from client GUIs, we can also access PostgreSQL using programming languages like Python. We do this using libraries of code that extend core Python named 'packages'.\n", "\n", "The commands work similarly, you can execute almost any SQL in a programming language that you can in a manual client, and the results are returned in a format that lets you interact with them after the SQL statements finish.\n", "\n", "_(Python lets you interact with databases using SQL just like you would in any SQL GUI or terminal. Python code can do SELECTs, CREATEs, INSERTs, UPDATEs, and DELETEs, and any other SQL)_\n", "\n", "Below are three ways one can interact with PostgreSQL using Python:\n", "\n", "1. **_`psycopg2`_** - The Python `psycopg2` package implements Python's DBAPI, a mostly-standardized API for database interaction, to allow for querying PostgreSQL, It is the closest you can get in Python to a direct database connection.\n", "2. **_`SQLAlchemy`_** - `SQLAlchemy` can be used to map Python objects to database tables, but it also contains a wrapper around DBAPI that allows for query code be more consistently re-used across databases.\n", "3. **_`pandas`_** - `pandas` is an analysis package that can use a database connection (with either `SQLAlchemy` or `psycopg2`) to read the results of SQL queries directly into a `pandas` DataFrame, allowing you to further analyze the data in Python." ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 20, "hidden": false, "row": 213, "width": 12 }, "report_default": {} } } } }, "source": [ "### Python - `psycopg2`\n", "\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "The `psycopg2` package is the most popular PostgreSQL adapter for the Python programming language. This Python package implements the standard DBAPI Python interface for interacting with a relational database. This is the closest you can get to connecting directly to the database in Python - there aren't any objects creating in-memory tables or layers of abstraction between you and the data. Your Python sends SQL directly to the database and then deals row-by-row with the results.\n", "\n", "__Pros:__\n", "- This is often the best way to use Python to manage a database (ALTER, CREATE, INSERT, UPDATE, etc.). Fancier packages sometimes don't deal well with more complicated management SQL statements.\n", "- It also is often what you have to resort to for genuinely big data, since the different ways you can fetch rows from the results of a query give you fine-grained control over exactly how much data is in memory at a given time.\n", "- If you have a particularly vexing problem with a more feature-rich package, this is going to be your bare-bones troubleshooting sanity check to see if the problem is with that package rather than your SQL or your database. \n", "\n", "__Cons:__\n", "- All this control and bare-bones-ed-ness means that some things that are pretty easy in pandas can take a lot more code, time, and learning at this lower level. Pandas manages a lot of the details of connecting to and interacting with a database for you.\n", "\n", "__Mixed:__\n", "- In theory, when you write DBAPI-compliant code, that code can be used to interact with any database that has a DBAPI=compliant driver package. In practice, DBAPI drivers are about 95% compatible between databases and SQL for some tasks can be different from database to database, so you end up with code that can be ported between databases with a few tweaks and modifications, and then needing to test it all to make sure your SQL works." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 4, "height": 4, "hidden": false, "row": 11, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# importing datetime and psycopg2 package\n", "import datetime\n", "import psycopg2\n", "import psycopg2.extras\n", "\n", "print( \"psycopg2 imports completed at \" + str( datetime.datetime.now() ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 8, "height": 4, "hidden": false, "row": 11, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# set up connection properties\n", "db_host = \"10.10.2.10\"\n", "db_database = \"appliedda\"\n", "\n", "# and connect.\n", "pgsql_connection = psycopg2.connect( host = db_host, database = db_database )\n", "\n", "print( \"psycopg2 connection to host: \" + db_host + \", database: \" + db_database \n", " + \" completed at \" + str( datetime.datetime.now() ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 4, "hidden": false, "row": 233, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# results come back as a list of columns:\n", "pgsql_cursor = pgsql_connection.cursor()\n", "\n", "# results come back as a dictionary where values are mapped to column names (preferred)\n", "pgsql_cursor = pgsql_connection.cursor( cursor_factory = psycopg2.extras.DictCursor )\n", "\n", "print( \"psycopg2 cursor created at \" + str( datetime.datetime.now() ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 4, "height": 4, "hidden": false, "row": 233, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# SQL\n", "sql_string = \"SELECT COUNT( * ) AS row_count FROM il_des_kcmo.il_qcew_employers;\"\n", "\n", "# execute it.\n", "pgsql_cursor.execute( sql_string )\n", "\n", "# fetch first (and only) row, then output the count\n", "first_row = pgsql_cursor.fetchone()\n", "print( \"row_count = \" + str( first_row[ \"row_count\" ] ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 8, "height": 155, "hidden": false, "row": 233, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# SQL\n", "sql_string = \"SELECT * FROM il_des_kcmo.il_qcew_employers LIMIT 1000;\"\n", "\n", "# execute it.\n", "pgsql_cursor.execute( sql_string )\n", "\n", "# ==> fetch rows to loop over:\n", "\n", "# all rows.\n", "#result_list = pgsql_cursor.fetchall()\n", "\n", "# first 10 rows.\n", "result_list = pgsql_cursor.fetchmany( size = 10 )\n", "\n", "# loop\n", "result_counter = 0\n", "for result_row in result_list:\n", " \n", " result_counter += 1\n", " print( \"- row \" + str( result_counter ) + \": \" + str( result_row ) )\n", " \n", "#-- END loop over 10 rows --#\n", "\n", "# ==> loop over the rest one at a time.\n", "result_counter = 0\n", "result_row = pgsql_cursor.fetchone()\n", "while result_row is not None:\n", " \n", " # increment counter\n", " result_counter += 1\n", " \n", " # get next row\n", " result_row = pgsql_cursor.fetchone()\n", " \n", "#-- END loop over rows, one at a time. --#\n", "\n", "print( \"fetchone() row_count = \" + str( result_counter ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 4, "hidden": false, "row": 237, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# Close Connection and cursor\n", "pgsql_cursor.close()\n", "pgsql_connection.close()\n", "\n", "print( \"psycopg2 cursor and connection closed at \" + str( datetime.datetime.now() ) )" ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 19, "hidden": false, "row": 388, "width": 12 }, "report_default": {} } } } }, "source": [ "### Python - `SQLAlchemy`\n", "\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "`SQLAlchemy` is a higher-level Python database library that, among many other things, contains a wrapper around DBAPI that makes a subset of the DBAPI API work the same for any database `SQLAlchemy` supports (though it doesn't work exactly like DBAPI... nothing's perfect). You can use this wrapper to write Python code that can be re-used with different databases (though you'll have to make sure the SQL also is portable). `SQLAlchemy` also includes advanced features like connection pooling in its implementation of DBAPI that help to make it perform better than a direct database connection.\n", "\n", "Just be aware that the farther you move from a direct connection, the more potential there is for things to go wrong. Under the hood, `SQLAlchemy` is using `psycopg2` for its PostgreSQL database access, so now you have two relatively complex packages working in tandem. If you get a particularly vexing bug running SQL with `SQLAlchemy`, in particular complex SQL or statements that update or alter the database, make sure to try that SQL with a pure DBAPI client or in the command line client to see if it is a problem with `SQLAlchemy`, not with your SQL or database.\n", "\n", "`SQLAlchemy`'s database connection is called an engine. To connect a `SQLAlchemy` engine to a database, you will:\n", "\n", "- create a `SQLAlchemy` connection string for your database.\n", "- use that string to initialize an engine and connect it to your database.\n", "\n", "A full connection URL for `SQLAlchemy` looks like this: \n", "\n", " dialect+driver://username:password@host:port/database\n", "\n", "If you recall back to our connection properties, we only need to specify host name and database. In `SQLAlchemy`, any elements of the URL that are not needed can be omitted. So for our database, the connection URL is:\n", "\n", " postgresql://10.10.2.10/appliedda" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "hidden": true }, "report_default": {} } } } }, "outputs": [], "source": [ "# imports\n", "import sqlalchemy\n", "import datetime" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 4, "height": 4, "hidden": false, "row": 237, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# Connect\n", "connection_string = 'postgresql://10.10.2.10/appliedda'\n", "pgsql_engine = sqlalchemy.create_engine( connection_string )\n", "\n", "print( \"SQLAlchemy engine connected to \" + connection_string + \" at \" + str( datetime.datetime.now() ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 4, "hidden": false, "row": 241, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# Single row query - with the streaming option so it does not return results until we \"fetch\" them:\n", "sql_string = \"SELECT COUNT( * ) AS row_count FROM il_des_kcmo.il_qcew_employers;\"\n", "query_result = pgsql_engine.execution_options( stream_results = True ).execute( sql_string )\n", "\n", "# output results - you can also check what columns \"query_result\" has by accessing\n", "# it's \"keys\" since it is just a Python dict object. Like so:\n", "print( query_result.keys() )\n", "\n", "# print an empty string to separate out our two more useful print statements\n", "print('')\n", "\n", "# fetch first (and only) row, then output the count\n", "first_row = query_result.fetchone()\n", "print(\"row_count = \" + str( first_row[ \"row_count\" ] ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 170, "hidden": false, "row": 407, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# run query with the streaming option so it does not return results until we \"fetch\" them:\n", "\n", "# SQL\n", "sql_string = \"SELECT * FROM il_des_kcmo.il_qcew_employers LIMIT 1000;\"\n", "\n", "# execute it.\n", "query_result = pgsql_engine.execution_options( stream_results = True ).execute( sql_string )\n", "\n", "# ==> fetch rows to loop over:\n", "\n", "# all rows.\n", "#result_list = query_result.fetchall()\n", "\n", "# first 10 rows.\n", "result_list = query_result.fetchmany( size = 10 )\n", "\n", "# loop\n", "result_counter = 0\n", "for result_row in result_list:\n", " \n", " result_counter += 1\n", " print( \"- row \" + str( result_counter ) + \": \" + str( result_row ) )\n", " \n", "#-- END loop over 10 rows --#\n", "\n", "# ==> loop over the rest one at a time.\n", "result_counter = 0\n", "result_row = query_result.fetchone()\n", "while result_row is not None:\n", " \n", " # increment counter\n", " result_counter += 1\n", " \n", " # get next row\n", " result_row = query_result.fetchone()\n", " \n", "#-- END loop over rows, one at a time. --#\n", "\n", "print( \"fetchone() row_count = \" + str( result_counter ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 4, "height": 4, "hidden": false, "row": 241, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# Clean up:\n", "pgsql_engine.dispose()\n", "\n", "print( \"SQLAlchemy engine dispose() called at \" + str( datetime.datetime.now() ) )" ] }, { "cell_type": "markdown", "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 14, "hidden": false, "row": 577, "width": 12 }, "report_default": {} } } } }, "source": [ "### Python - `pandas`\n", "\n", "- Back to [Table of Contents](#Table-of-Contents)\n", "\n", "Next we'll use the [pandas package](http://pandas.pydata.org/) to populate `pandas` DataFrames from the results of SQL queries. `pandas` uses a `SQLAlchemy` database engine to connect to databases and run queries. It then reads data returned from a given SQL query and further processes it to store it in a tabular data format called a \"DataFrame\" (a term that will be familiar for those with R or STATA experience).\n", "\n", "DataFrames allow for easy statistical analysis, and can be directly used for machine learning. They also load your entire result set into memory by default, and so are not suitable for really large data sets.\n", "\n", "And, as discussed in the `SQLAlchemy` section, this is yet another layer added on top of other relatively complex database packages, such that you multiply the potential for a peculiarity in one to cause obscure, difficult-to-troubleshoot problems in one of the other layers. It won't occur frequently, but if you run into weird or inexplicable problems when turning SQL into DataFrames, try running the SQL using lower layers to isolate the problem.\n", "\n", "In the code cell below, we'll use `SQLAlchemy` to connect to the database, then we'll give this engine to pandas and let it retrieve and process data.\n", "\n", "_Note: in addition to processing SQL queries, `pandas` has a range of [Input/Output tools](http://pandas.pydata.org/pandas-docs/stable/io.html) that let it read from and write to a large variety of tabular data formats, including CSV and Excel files, databases via SQL, JSON files, and even SAS and Stata data files. In the example below, we'll use the `pandas.read_sql()` function to read the results of an SQL query into a data frame._" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "hidden": true }, "report_default": {} } } } }, "outputs": [], "source": [ "# imports\n", "import datetime\n", "import pandas" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 4, "hidden": false, "row": 245, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# Connect - create SQLAlchemy engine for pandas to use.\n", "connection_string = 'postgresql://10.10.2.10/appliedda'\n", "pgsql_engine = sqlalchemy.create_engine( connection_string )\n", "\n", "print( \"SQLAlchemy engine connected to \" + connection_string + \" at \" + str( datetime.datetime.now() ) )" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 4, "height": 4, "hidden": false, "row": 245, "width": 4 }, "report_default": {} } } } }, "outputs": [], "source": [ "# Single row query\n", "sql_string = \"SELECT COUNT( * ) AS row_count FROM il_des_kcmo.il_qcew_employers;\"\n", "df = pandas.read_sql( sql_string, con = pgsql_engine )\n", "\n", "# get row_count - first get first row\n", "first_row = df.iloc[ 0 ]\n", "\n", "# then grab value.\n", "row_count = first_row[ \"row_count\" ]\n", "\n", "print(\"row_count = \" + str( row_count ) )\n", "\n", "# and call head().\n", "df.head()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "col": 0, "height": 13, "hidden": false, "row": 591, "width": 12 }, "report_default": {} } } } }, "outputs": [], "source": [ "# SQL\n", "sql_string = \"SELECT * FROM il_des_kcmo.il_qcew_employers LIMIT 2000;\"\n", "\n", "# execute it.\n", "df = pandas.read_sql( sql_string, con = pgsql_engine )\n", "\n", "# unlike previous Python examples, rows are already fetched and in a dataframe:\n", "\n", "# you can loop over them...\n", "row_count = 0\n", "for result_row in df.iterrows():\n", " \n", " row_count += 1\n", " \n", "#-- END loop over rows. --#\n", "\n", "print( \"loop row_count = \" + str( row_count ) )\n", "\n", "# Print out the first X using head()\n", "output_count = 10\n", "df.head( output_count )\n", "\n", "# etc." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "extensions": { "jupyter_dashboards": { "version": 1, "views": { "grid_default": { "hidden": true }, "report_default": {} } } } }, "outputs": [], "source": [ "# Close Connection - Except you don't have to because pandas does it for you!" ] } ], "metadata": { "extensions": { "jupyter_dashboards": { "activeView": "grid_default", "version": 1, "views": { "grid_default": { "cellMargin": 10, "defaultCellHeight": 20, "maxColumns": 12, "name": "grid", "type": "grid" }, "report_default": { "name": "report", "type": "report" } } } }, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.3" }, "toc": { "nav_menu": {}, "number_sections": false, "sideBar": true, "skip_h1_title": false, "toc_cell": false, "toc_position": { "height": "651px", "left": "0px", "right": "903px", "top": "111px", "width": "230px" }, "toc_section_display": true, "toc_window_display": false } }, "nbformat": 4, "nbformat_minor": 4 }