Author Archives: Yuji Tomita

I'm a human person. I excel in crafting holistic technology solutions that extend beyond a narrow technical scope.

Export Chrome History as CSV (Spreadsheet)

Chrome stores history as an SQLITE3 database.

First, find it.
Close Chrome.

Open up the terminal, and navigate to the directory that contains the History file.

cd ~/Library/Application\ Support/Google/Chrome/Default/

Open the database with SQLITE3:

sqlite3 History

Enable Sqlite3 output mode as CSV by typing into sqlite shell:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output my-chrome-output.csv

Now, execute the SQL statement (copy and paste). Note that by default, the time stamp is not “human readable” so we’ll insert some logic to convert times into our local time zone / months, years, and that good stuff.

SELECT datetime(last_visit_time/1000000-11644473600,'unixepoch','localtime'),
                        url 
                 FROM urls
                 ORDER BY last_visit_time DESC

Done! Check your directory for my-chrome-output.csv.

Why this isn’t documented anywhere except in small pieces? Don’t know.

South Migration App Has No Migrations

If you get this error for an app that truly doesn’t have any migrations, and perhaps isn’t even related to your project, it most likely means the south_migrationhistory table has row that matches one of your INSTALLED_APPS.

This can happen if at one point the app had migrations, but the latest version doesn’t (happens to third party apps), or you once built an app with migrations in the early days of your project.

Remove it via SQL to fix the problem.

DELETE FROM south_migrationhistory WHERE app_name = ‘offending_appname’;

SQLAlchemy Order By Foreign Key Table Column

To order by a foreign key value, make sure you do a proper `.join` of the table you want to reference in addition to  `.options` and `joinedload` or `joinedload_all`, which is purely to retrieve data from each related row.

SQLAlchemy never ceases to amaze.. I’m glad I made the switch, as it is making me a better coder by not shielding me from the intricacies  of SQL.

http://docs.sqlalchemy.org/en/rel_0_7/orm/loading.html

How joinedload() in particular achieves this result of not impacting entity rows returned in any way is that it creates an anonymous alias of the joins it adds to your query, so that they can’t be referenced by other parts of the query. For example, the query below uses joinedload() to create a LEFT OUTER JOIN from users to addresses, however the ORDER BY added against Address.email_address is not valid – the Address entity is not named in the query:

>>> jack = session.query(User).\
... options(joinedload(User.addresses)).\
... filter(User.name=='jack').\
... order_by(Address.email_address).all()
SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id WHERE users.name = ? ORDER BY addresses.email_address <– this part is wrong ! [‘jack’]

Above, ORDER BY addresses.email_address is not valid since addresses is not in the FROM list. The correct way to load the User records and order by email address is to use Query.join():

>>> jack = session.query(User).\
... join(User.addresses).\
... filter(User.name=='jack').\
... order_by(Address.email_address).all()
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users JOIN addresses ON users.id = addresses.user_id WHERE users.name = ? ORDER BY addresses.email_address [‘jack’]

Flask Cache Buster based on Git Revision

This is cool.

Here’s a simple flask cache buster that appends a version number to all static assets based on the current git revision short hash.

https://gist.github.com/yuchant/9108622

Flask is built so amazingly. It has hooks to allow modification of all urls generated by the system.


app = make_app()

def get_git_revision_hash():
'''
Get current revision short hash and use as cache buster key.
'''
import subprocess
return subprocess.check_output(['git', 'rev-parse', '--short', 'HEAD'])

git_revision_hash = get_git_revision_hash()
STATIC_ENDPOINTS = [</div><div class="" id="file-gistfile1-txt-LC14">    'static',</div>

<div class="" id="file-gistfile1-txt-LC15">    'shop.static',</div><div class="" id="file-gistfile1-txt-LC16">]
@app.url_defaults
def static_cache_buster(endpoint, values):
if endpoint in STATIC_ENDPOINTS:
values['_v'] = git_revision_hash

Update: actually, git revision number is not what I’m looking for

This only works for developer driven content. It wouldn’t work for user uploads of course, therefore I modified this to read mtimes.

        STATIC_ENDPOINTS = [
            'static',
            'shop.static',
        ]
        @self.app.url_defaults
        def static_cache_buster(endpoint, values):
            if endpoint in STATIC_ENDPOINTS:
                # append timestamps of files
                modules = endpoint.split('.')
                if len(modules) == 2:
                    blueprint, _ = modules
                    static_folder = self.app.blueprints[blueprint].static_folder
                else:
                    static_folder = self.app.static_folder
                file_path = os.path.join(static_folder, values['filename'])
                mtime = os.path.getmtime(file_path)
                values['_v'] = mtime