Databases

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

Leave a Comment / Databases / By

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 create a versioned table from a crud table in Postgres with triggers Read More »

How to export CSV file to database with Python

Leave a Comment / Databases, Programming / By

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)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 …

How to export CSV file to database with Python Read More »

How to work with spatial data in Amazon Redshift

Leave a Comment / Databases, Spatial stuff / By

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 work with spatial data in Amazon Redshift Read More »