Category: Databases

  • How to create a versioned table from a crud table in Postgres with triggers

    Example You have a user table in Postgres: CREATE SCHEMA tutorial; CREATE TABLE tutorial.crud_users ( id INT NOT NULL, username VARCHAR NOT NULL, CONSTRAINT crud_table_pkey PRIMARY KEY (id) ); You insert new users and update usernames for existing users. Update is a destructive operation that overwrites data, so how do you keep track of what […]

  • How to export CSV file to database with Python

    Pandas and SQLAlchemy offer powerful conversions between CSV files and tables in databases. Here is a small example: import pandas as pd from sqlalchemy import create_engine df = pd.read_csv(‘mydata.csv’) engine = create_engine(‘sqlite:///mydata.db’) df.to_sql(‘mytable’, engine) Read more: pandas.DataFrame.to_sql sqlalchemy engines

  • How to work with spatial data in Amazon Redshift

    While Redshift does not offer native support for spatial data, indexes and functions, there exists a partial workaround. Redshift supports Python UDFs and can also load custom Python libraries. Out of the box, Redshift has numpy, scipy, pandas and many other useful Python libraries. For spatial functionality, one saving grace is the high quality spatial […]

  • How to compute Fibonacci sequence in SQL

    Inspired and simplified from a set of slides on using RDBMS for storing, managing, and querying graphs: with recursive fib(i,j) as ( select 0,1 union all select j, i+j from fib where j