I have posted previously an example of using the SQL magic inside Jupyter notebooks. Today, I will show you how to execute a SQL query against a PostGIS database, get the results back into a
DataFrame object, manipulate it, and then dump the
DataFrame into a brand new table inside the very same database. This can be very handy if some of your operations are better done using plain SQL, for instance, with the help of spatial SQL functions, but other ones are easier to perform using
pandas, for instance, adding and calculating new columns when the data you need to access is not stored inside the database.
The steps are as below:
- Connect to a database using the SQL magic syntax
- Execute a
SELECTSQL query getting back a result set
- Read the result set into a
- Do stuff with the
- Dump it to a new table in the database you are connected to using the
Again, this is very handy for prototyping when you need to produce some tables doing a database design or when you need to have a new temporary table created for some application to read or when running some integration tests and needing to have a mock-up table to work with.
The sample notebook is available as a gist: