Here are the core schema-design principles I follow: Start from the business grain: define exactly what one row represents, e.g. “one trade execution” or “daily account balance per portfolio and currency.” Everything follows from this. Preserve raw data: keep an…
You may have the need to reset your wordpress password and here I’ll show you how to do that, assuming you have access to your sites MySQL database. WordPress stores the password for your user in the wp_users table. The…
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…
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(”) df.to_sql(‘mytable’, engine) Read more: pandas.DataFrame.to_sql sqlalchemy engines
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…
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