O'Reilly logo
live online training icon Live Online training

Programming with SQL

Data

Bringing data to your codebase

Thomas Nield

Whether you are building data science models or engineering software, data is the lifeblood of applications. Importing and exporting static text files is one way to work with data. However, as demands grow to bring our code to production, it is necessary to work directly with data at the source. This is where SQL comes in and gets paired with your favorite programming language. When you make your Python, R, or Java codebase work directly with relational databases, you truly bring your work to life and that much closer to a finished product.

In this 3-hour online training, we will cover the fundamentals of using SQL with Python, R, and Java. We will learn the basics of connecting, reading, and writing data as well as cover specialized topics like data frames (Pandas, R, and Tablesaw), connection management, and practical design.

To make setup quick and easy, we will use the simplicity and power of SQLite as our database platform.

What you'll learn-and how you can apply it

By the end of this live, hands-on, online course, you’ll understand:

  • The fundamentals of connecting, reading, and writing SQL from Python, R, and Java.
  • Common design patterns as well as mapping records to data frames and classes
  • Best practices in pooling and disposing connections as well as maximizing performance.

And you’ll be able to:

  • Use live data in your data science models rather than static text files, as well as export data quickly and easily into relational databases.
  • Create practical tools and applications that can persist data with minimal architecture.
  • Apply best practices and design strategies to create flexible and resilient codebases without taxing your database system or code performance.

This training course is for you because...

  • You’re a data science professional wanting to connect directly to SQL databases from your data science models.
  • You are a software engineer familiar with some basic SQL, and want your applications/services to work directly with relational databases.
  • You are a SQL user curious to see how SQL is leveraged in Python, R, and Java codebases and evaluate why pairing SQL with coding is worthwhile.

Prerequisites

  • You should already be familiar with basic SQL commands like SELECT, WHERE, INSERT, UPDATE, and DELETE.
  • You do not need any prior experience with INNER JOIN, LEFT JOIN, GROUP BY, and other transformative operators.

Recommended preparation:

Recommended follow-up:

About your instructor

  • Thomas Nield (author of Getting Started with SQL) has a business analyst background and works at Southwest Airlines in Revenue Management. Early in his career he became fascinated with technology and bought dozens of books to master programming in Java, C#, Kotlin, and database design. He is passionate about sharing what he learns and enabling others with new skillsets, even if they do not work in IT. He enjoys making technical content relatable and relevant to those unfamiliar or intimidated by it.

    Thomas has developed several database-driven applications for Southwest Airlines that generate revenue for the entire airline network. He believes technology should conform to the business, and emphasizes usefulness and real-world practicality while balancing the perspectives of IT and business professionals.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Intro and Setting Up (10 minutes)

  • Presentation: Why Program with SQL?
  • Presentation: Setting up Python, R, and Java environments
    • Python - SQLAlchemy, Pandas
    • R - RSQLite and DBI
    • Java - JDBC, SQLite, and Tablesaw
  • Q&A

Reading Data in Python, R, and Java (50 minutes)

  • Walkthrough: connecting and disconnecting from a database
  • Walkthrough: Iterating a SELECT query
  • Walkthrough: Mapping a SELECT query to data structures
  • Walkthrough: Mapping a SELECT query to a data frame
  • Q&A
  • Walkthrough: Streams and List Comprehensions
  • Walkthrough: Passing parameters and arguments safely
  • Q&A
  • Exercise: Write a Python/Java/R function
  • Break (10 minutes)

Writing Data in Python, R, and Java (40 minutes)

  • Walkthrough: INSERT data
  • Walkthrough: UPDATE data
  • Walkthrough: DELETE data
  • Q&A
  • Walkthrough: Writing data frames into a table
  • Walkthrough: Creating a transaction
  • Walkthrough: Batching large write volumes
  • Q&A
  • Exercise: Write a Python/Java/R function
  • Break (10 minutes)

Connection Management and Design Strategy (60 minutes)

  • Presentation: Connection Pools
  • Walkthrough: Implementing connection pools
  • Presentation: Query strategies and design decisions
  • Presentation: Caching, refreshing, and validating data
  • Demo: Preventing SQL injection
  • Homework: Building a personal finance app