O'Reilly logo
live online training icon Live Online training

Programming with SQL

Bringing data to your codebase

Topic: Data
Thomas Nield

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

Expert Thomas Nield walks you through the fundamentals of using SQL with Python, R, and Java. You’ll learn the basics of connecting, reading, and writing data, as well as specialized topics like data frames (pandas, R, and Tablesaw), connection management, and practical design. To make setup quick and easy, you’ll use the simplicity and power of SQLite as your database platform.

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

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

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

And you’ll be able to:

  • Use live data in your data science models rather than static text files, and 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 who wants to connect directly to SQL databases from your data science models.
  • You’re a software engineer familiar with some basic SQL who wants your applications or services to work directly with relational databases.
  • You’re an SQL user who wants to understand why pairing SQL with coding is worthwhile and learn how to leverage SQL in Python, R, and Java codebases.

Prerequisites

Prerequisites:

  • Familiarity with basic SQL commands (SELECT, WHERE, INSERT, UPDATE, DELETE, etc.)

Recommended preparation:

Recommended follow-up:

About your instructor

  • Thomas Nield is an operations research consultant as well as a writer, conference speaker, and trainer. He enjoys making technical content relatable and relevant to those unfamiliar or intimidated by it. Thomas regularly teaches classes on analytics, machine learning, and mathematical optimization. He’s authored two books, including Getting Started with SQL (O'Reilly) and Learning RxJava (Packt), and has written several popular articles, including “How It Feels to Learn Data Science in 2019” and “Is Deep Learning Already Hitting Its Limitations?”

Schedule

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

Intro and setting up (10 minutes)

  • Presentation: Why you should program with SQL; setting up Python, R, and Java environments
  • Q&A

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

  • Presentation: Connecting and disconnecting from a database; iterating a SELECT query; mapping a SELECT query to data structures; mapping a SELECT query to a data frame; understanding streams and list comprehensions; passing parameters and arguments safely
  • Hands-on exercise: Write a Python, Java, and R function
  • Q&A

Break (10 minutes)

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

  • Presentation: Understanding INSERT, UPDATE, and DELETE data; writing data frames into a table; creating a transaction; batching large write volumes
  • Hands-on exercise: Write a Python, Java, and R function
  • Q&A

Break (10 minutes)

Connection management and design strategy (60 minutes)

  • Presentation: Connection pools; implementing connection pools; understanding query strategies and design decisions; caching, refreshing, and validating data; preventing SQL injection
  • Hands-on exercise: Build a personal finance app