UnicodeDecodeError: ‘utf-8’ codec can’t decode byte 0xf1 in position 4: invalid continuation byte

TLDR: Convert your problem file with Sublime Text by opening the file and using “Save with encoding” as utf-8. Alternatively, use iconv -t UTF-8//TRANSLIT -c Zip_Zhvi_SingleFamilyResidence.csv > new_file.csv

When does this error happen?

I wanted to parse the housing data from Zillow at their research page. Zip code is a great measure of single family home real estate values.

zillow research page time series by zipcode.png

However, when I download this data set as “Zip_Zhvi_SingleFamilyResidence.csv”, I could not simply load this data into pandas.

pandas_read_csv_UnicodeDecodeError.png

This last line seemed like the clue:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xf1 in position 4: invalid continuation byte

Well, what format is that file?

Using a Mac, we can use file -I <file_name>

file_type.png

Oh, great! its “us-ascii”, we just pass that encoding into pandas right?

pandas_error_with_encoding.png

Oh maybe, I need to specify the encoding I want. WHY PANDAS, WHY!?

pandas_error_with_encoding_again.png

Why does this error happen?

Some encoding error has occurred, maybe because you accidentally opened Excel before opening ipython or Zillow saves in a crazy format.

Awesome, lets just convert it

Let’s use the *nix program iconv to convert the file. According to the man page (man iconv), “The iconv program converts text form one encoding to another encoding. Great!man_iconv.png

Let’s use this.

iconv -f us-ascii -t utf-8 < Zip_Zhvi_SingleFamilyResidence.csv > new_zip_code_file.csv

iconv_failure.png

“cannot convert”

But iconv, that’s your only job… you know, unix philosophy, one program, one job done well etc etc.

Turns out if you use “//TRANSLIT” appended to the encoding, characters are transliterated when needed and
possible (man page)

Solution 1 – iconv with //TRANSLIT

> iconv -t UTF-8//TRANSLIT -c Zip_Zhvi_SingleFamilyResidence.csv > new_file.csv

> mv new_file.csv Zip_Zhvi_SingleFamilyResidence.csv

Solution 2 (easier to remember) – Sublime Text

Is there a better free editor than Sublime? Be a good citizen and buy your license.

Step 1: Open your file in Sublime Text

Step 2: Save with Encoding > UTF-8

DONE!

grizz_celebrating.gif

read_csv to your hearts desire 🙂

ipython> data = pd.read_csv("new_file.csv")

Save time: npm install nodemon

Nodemon will allow you to start your node.js project without manually shutting down and restarting your server.

Many people are scared of installing nodemon GLOBALLY, so here’s a guide to install nodemon locally in your node.js project.

You took two paths to get here: 1. You used the express generator, 2. You started your own express server

Path 1: You used the express generator

Step 1: Install nodemon

npm install nodemon --save

Step 2: Change your “start” npm command to use nodemon inside the “scripts” object

"start": "nodemon ./bin/www

note: you might have to create this command

Step 3: Run your new dev script

> npm start

Path 2: You started your own express server

Step 1: Install nodemon

npm install nodemon --save

Step 2: Create a “start” npm command to use nodemon pointing to your express file (inside the “scripts” object)

"start": "nodemon index.js

note: you will have to create this command

Step 3: Run your new dev script

> npm start

 

Your javascript changes will automatically restart your server.

 

*You’re welcome Ken.

Todidlist.com started as a vim shortcut

Hi all!

Todidlist.com started as a vim shortcut that I wrote about way back last year here.

I wanted to have the most accessible “did list” and vim served me so so well. 

However, once I got a new computer, I realized that my did list was missing a key feature: ubiquity

There was one simple trait that was missing from my $ did command and that was the ability to see my “Dids” on every computer and my mobile devices.

So, www.todidlist.com was born! Check it out and let me know how it treats you.

Thanks for all the support!

Setting up Redis on Heroku for ExpressJS

By default, your express application will store session data in memory. That means if your server restarts all users will need to log back in. Additionally, this does not scale to more than one instance, leaks memory and does other mean things. While this works while developing your local computer (notice how you always need to log in after making a code change), you will want something better for production.

Heroku even warns you if you do this. Try running $ heroku logs --tail to see this.

Welcome Redis

Redis is an open source (BSD licensed), in-memory data structure store, used as a database, cache and message broker.

Step 1: Create the heroku add on

$ heroku addons:create heroku-redis:hobby-dev

This will create a REDIS_URL in the heroku config that you will use to connect. Use $ heroku config:get REDIS_URL to see this value.

Step 2: Download the connect-redis package for your project

$ npm install -s connect-redis

Step 3: Pass in express-session into connect-redis

Step 4: Create an instance of RedisStore as the store property of your session configuration

Note: your local computer probably does not have REDIS_URL set.

Step 5: Push to heroku and try your new session store

$ git push heroku master

How do you run your project locally now?

Option 1: default to memory session store locally

Check your NODE_ENV setting and if its not production we will default the store property to null.

Option 2: default to using a redis instance locally

Run these commands and connect to your instance directly.

Screen Shot 2019-04-07 at 2.51.07 PM.png

Gotcha Error: req.flash() requires sessions

If you try running your project locally, you will notice that your session store fails to initialize and things break – this happens since you likely do not have the REDIS_URL set.

Production ready: 

Note that redis on hobby dev will not allow SSL and to secure a production redis, you will need to pay for a production plan and add “stunnel” to your buildpack. (link)

Create an interactive terminal cli with node

Let’s create a tool that will track our some text entries.

Step 1: Create a new npm project and install the necessary libraries

$ mkdir my_cli && cd my_cli && npm init -y && npm i --save inquirer chalk

chalk – helps color the terminal prompts

inquirer – makes creating interactive prompts a breeze

Step 2: create interactive terminal prompts

Step 3: save or append to a file (did.txt)

Full code here

Sublime Text shortcut on command line (subl)

The Sublime Text documentation “OS X Command Line” references the command line tool, subl to work with files on the command line.

This allows you to open the entire current directory in sublime like this:

$ subl .

This allows you to open a specific file like this:

$ subl <file_name>

Note: The $ sign means this is run in your command line application.

subl is not available by default

Simply running $ subl is not immediately accessible when you download Sublime. Without symlink-ing the command, you will have to type the full path ​to the original subl

This is how you would use the full path to open a file.

$ /Applications/Sublime\ Text.app/Contents/SharedSupport/bin/subl <file_name>

So lets create a symlink

The official docs ask you to target your symlink to ~bin/subl but it may be nicer to keep symlinks contained to existing “load paths” like usr/local/bin

What are your “load paths”? Find your current load paths by running this command:

$ echo $PATH

You will see all load paths including these default OS X paths:

/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin

How to create subl shortcut for Sublime Text

Run this command in your terminal:

ln -s /Applications/Sublime\ Text.app/Contents/SharedSupport/bin/subl /usr/local/bin/subl

What happened?

ln means link, -s means we want a symbolic link

We will create a symlink at usr/local/bin/subl that will point to the original Sublime command. Deleting this link will have no effect on the original file.

How to publish an npm module

Step 0: You need some code to share.

Create a new Github repo or choose one you currently want to publish

Here is the command line argument to create a repo called “adder”

$ curl -u <your_username> https://api.github.com/user/repos -d "{\"name\": \"adder\"}"

Once you have chosen a Github repo clone it

Step 1: Create a proper package.json

The best way to do this is to run the npm init command

$ npm init

This will insure you have the required package.json file with name and version set.

Step 2: Publish

$ npm publish

If you are not logged in, you will need to run npm login

Thats it!

Step 3: Test your npm package

Double check your package information with $ npm info <package_name>

Alternatively, go to another repo and run $ npm install <package_name>

Congrats! Your code is out in the wild on the npm registry 🙂

Troubleshooting: Package name already exists

You might run into the issue where someone else has taken the name of your npm package. Note that you can set a different name on npm than your github repo. One option is get super creative with your npm package name and change the “name” key in package.json. Another option is to namespace your package to your username like this:

"name": @theptrk/express 

and then publishing this with the public flag

$ npm publish --access=public

What if I want to publish a new version?

npm will reject your package if you do not bump the version number. Use npm version and choose either major, minor, or patch. This is the example for patch

$ npm version {major, minor, patch}

$ npm publish

Express Sequelize Heroku Postgres Configuration Success

You are in flow developing on your local database and you decide to upload everything to heroku and share it with the world.

How do you configure your production database?

It’s easier than you think. Let’s assume you have already set up your site.

If you used the sequelize init command, you will have a file db/models/index.js that looks like this

Usually, your sequelize database will initialize with the usual database, username password, etc but in the special case that your config has use_env_variable, sequelize will recognize that an environment variable (usually representing a URI) can be used in place of the usual credentials.

What is your env/config variable?

Find your heroku config variables in the heroku website or by running $ heroku config. Note, config and env variables are used to reference the same settings here.

When you create a database on your heroku account, it will default a new configuration setting called “DATABASE_URL”

You can access this with process.env.DATABASE_URL in any of your node.js code (on production). Your local machine will have different environment variables set.

Step 1: Set use_env_variable

But set it to what? Set it what heroku designates as your database url, DATABASE_URL!

Thats it!

You may need to migrate your database if you aren’t using sync

Step 2: Migrate your database

Simply run the migrate command in the heroku environment with heroku run. This avoids any configuration errors of running from your local environment

$ heroku run ./node_modules/.bin/sequelize db:migrate --debug

Final config

Step 3: Check your database

Run the heroku psql command and run the “list tables” command.

$ heroku psql

$<app_name>::DATABASE=> \dt

Possible Errors

Error: no pg_hba.conf entry for host…, SSL OFF

Add the ssl setting into dialectOptions of your config.json

Error: The server does not support SSL connections

This may be an outdated error. But previously, users who tried to migrate on their local computer would run into this error.

$ NODE_ENV=production DATABASEURL=$(heroku config:get DATABASE_URL) ./node_modules/.bin/sequelize db:migrate

This will not work correctly unless you set up your local computer to support ssl in the migration. You could get a certificate and configure your sequelize config to use that BUT theres an easier way.

ERROR: SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432

It’s quite possible you forgot to push your code to heroku. Make sure you git push heroku master!!!

Node Sequelize Postgres Tutorial

Why use Sequelize?

Sometimes you want your team to use an ORM. Often you need to run migrations. Sequelize provides ORM features and a much needed migrations library that allows us to create producible and version controlled database schemas.

Setup your project

Step 1: Create an npm project in your desired directory

$ npm init

Step 2: Install the packages

$ npm install --save sequelize sequelize-cli pg pg-hstore

Step 3: Create a new database

You need to have postgres installed on your machine for this. If you do not have postgres you can use brew to install it: $ brew install postgres

Now create a new database called here called “user-demo-db”

$ createdb user-demo-db

Step 4: Initialize your sequelize directories

$ node_modules/.bin/sequelize init

Now what? Using Sequelize

You have two options to create database tables: We can either create our database tables with the psql shell using raw queries or we can use sequelize to manage our database.

When to use “sync” and when to use “migrations”

Using sequelize, there are two options for creating tables: 1. Using sync or 2. Using migrations.

In most tutorials you will see code using sync() or even sync({force: true}). This is completely fine in a test database since this option will completely overwrite your database based on your model files. Note: this means you can destroy data with this option.

Why use migrations?

With migrations, we are incrementally changing the database tables and will retain a record of all these changes in our “migration” files. This record is a valuable record of our changes and will allow us to avoid destroying our data.

Step 5 : Create your first model by generating your first migration files

Here we are creating a new model by generating both the initial model file AND the migration file for a sample model User with some attributes firstName;lastName;email

* Note: we are using the singular “User” and not the plural “Users”. This will be an ORM convention that you will need to get used to.

$ node_modules/.bin/sequelize model:generate --name User --attributes firstName:string,lastName:string,email:string

Look inside your new model file: 

It should export a new “model” using sequelize.define("User", ...) with the attributes that you specified in the command line arguments.

* Note that the default id field is INTEGER and not something cool like UUID, its your code now, you can change this.

Look inside your new migration file:

It should use the  queryInterface function  createTable('User', ...) with attributes that you specified in the command line arguments plus three fields that sequelize will add to every table id, createdAt and updatedAt

* One thing that is key to note is that by default you table name is pluralized

Step 6: Run the migration

Before you run this migration, you may change the migration/model file to edit your attributes.

$ node_modules/.bin/sequelize db:migrate

Sequelize will create a SequelizeMeta table in your database to keep track of which migrations have been run. That also means you can create many other models before running this command and it will run all not-yet-run migrations

Check your migration

After you run your migration you can check your table status with psql shell or pgadmin

$ psql user-demo-db

psql> \d "Users" <- note the “s”; note the double quotes 

Get used to this pattern, you will use this a lot.

(Optional) Create an instance of your model – User

Note that we use the singular User instead of the Users from the actual table

node> var models = require('./db/models')

node> models.create.User({

  firstName: "John", lastName: "Doe", email: "jd@gmail.com"})

Step 7: How to create a 1:Many (1:M) relationship

Lets create a model called Task that can only have one User. 1(User):Many(Tasks).

Create your migration files and migrate your database to add the new table

$ node_modules/.bin/sequelize model:generate --name task --attributes body:string,done:boolean

$ node_modules/.bin/sequelize db:migrate

Now we will need to associate the Task to the User in some way.

According to the docs we need to run Task.belongsTo(User)

However if we add this to the db/models/Task.js file in the associate function, our file now fails to load correctly when accessing db/models within the node shell. We can open up our node shell and do this manually.

node> const models = require('./db/models')

node> models.Task.belongsTo(models.User)

What happened?

BelongsTo associations are associations where the foreign key for the one-to-one relation exists on the source model.

– docs

We ran the code <source>.belongsTo(<target>) with having the appropriate “foreign key” on our source model (Our Task model is missing the column UserId). Instead of undoing our migration, this is a perfect time to learn how to do a database migration.

Step 8: Fix our 1:M relationship

This time we only need the migration file, and not the model generated file.

$ node_modules/.bin/sequelize migration:generate --name add_userid_to_task

Adding a foreign key field to a model – Task

We want to create a new column UserId on our Task model/table that represents a foreign key relationship. The docs explain that all we need is a references key with the correct values

Your migration file will look something like this: code

return queryInterface.addColumn(
  SOURCE_TABLE_NAME,  // "Tasks"
  TARGET_FIELD_NAME, // "UserId"
  {
    type: Sequelize.INTEGER,
    reference: {
      model: TARGET_TABLE_NAME, // "Users"
      key: "id"
    }
    onDelete: 'cascade
  }
)

Important Notes:

  • we have to match the type of the target field (“integer” in our case above, “UUID” if you wanted to create an advanced User model)
  • Your source “table” name is likely the plural version of your “model” name. You can get the model name with node> require('../models')[model_name].getTableName()
  • Your target field name should be “title-case” based on sequelize convention, so if you’re targeting id on User, your target field name is UserId

Step 9: Remember to migrate your database!

$ node_modules/.bin/sequelize db:migrate

 

More: 

Code: Sample

Docs: Sequelize Docs

Blog Post: How to configure sequelize with heroku

Blog Post: How to define Sequelize associations using migrations