How to Find Postgres Log File and Postgres Data Directory from PSQL
If you want to find the location of your log file in Postgres, you'll need to hop into a Psql session.. psql dbname Then it's as simple as running.. show data_directory ; Which will output the data directory, in my case.. /Users/sea...
Written by Sean Behan on 05/25/2018
How to Just Get SQL Statement Error with SQLAlchemy Python Database Wrapper
If you're working with SQLAlchemy, the best database driver for Python, and want to see only SQL syntax errors, you need to use the StatementError exception class. On it, is an attribute `orig`, that contains just the SQL syntax error... and not any data...
Written by Sean Behan on 05/10/2018
How to Log and Query SQL Queries Hitting Your Database with MySQL
Here is some code just in case you want to look at and query the queries hitting your MySQL database. Enter this from the mysql client console. mysql> SET GLOBAL log_output = 'TABLE' mysql> SET GLOBAL general_log = 'ON'; mysql> select event_ti...
Written by Sean Behan on 03/17/2018
How to import CSV into SQLite3
To import a CSV file into SQLite is easy. sqlite3 my.db .mode csv .import path/to/file.csv name_of_table And done.
Written by Sean Behan on 12/03/2017
How to Use Named Variables with Postgres and PHP PDO Driver
You can write reusable scripts with Postgres by taking advantage of named variables. A named variable starts with a `:` in your sql script. Here is an example select :a_number You can then use this statement with `psql` and the `--variable` fl...
Written by Sean Behan on 11/11/2017
Manual ManyToMany Through with Django's ORM
Here is a code snippet that demonstrates how to set up a __ManyToMany through__ relationship in Django. In Rails, the equivalent would be called a __has_many through__ association. If you set the __through__ argument on the ManyToManyField, Django wil...
Written by Sean Behan on 07/21/2017
How to Fix Raw query must include the primary key with Django ORM
When running raw SQL queries in Django you must include a primary key otherwise an invalid query exception is raised. Normally this is fine but when running more complex queries a primary key may not be available or even make sense. There is a simple ...
Written by Sean Behan on 07/15/2017
Simple SQL for Counting New Signups
Here is a little snippet that will return new signups (or new records) for today select id, email, created_at::date date from signups where email not in (select distinct email from signups where created_at < current_date)
Written by Sean Behan on 07/13/2017
How to Find An SQLite Database with React Native and the iPhone Simulator.
I spent a few hours digging around my file system using `find` and `grep` hunting for my SQLite database that the iPhone Simulator was using for my React Native project. And no luck. But I found a simple solution was to use `react-native log-ios` and ...
Written by Sean Behan on 06/29/2017
How to Make Cross Database Queries with Postgres and DBLink Extension
Here are a few snippets for cross database queries. It's important to note that you must be explicit in enumerating the columns and types you are querying. Otherwise, things will probably not work as expected. -- enable extension create extension db...
Written by Sean Behan on 03/18/2017
How to Resolve ERROR 1396 (HY000): Operation CREATE USER failed for Error in MySQL
If you run into this error when trying to create a user in mysql, chances are you already have this user account created. create user 'someuser'@'localhost' identified by 'somepassword'; ERROR 1396 (HY000): Operation CREATE USER failed for 'someuser...
Written by Sean Behan on 03/02/2017
Extract Domain Names From Links in Text with Postgres and a Single SQL Query
This query and pattern will return urls in text all within a single SQL query. select substring(column_name from '.*://([^/]*)') as domain_name from table_name; And here it is in a larger query, say for retrieving page view counts for referrers. ...
Written by Sean Behan on 11/23/2013
How to cast a string of comma separated numbers into an array of integers for Postgres
If you have an string of numbers like "1,2,3" and you want to turn it into an array of integers you need to cast it into an integer array type. "{1,2,3}"::int[] This is commonly used together when grabbing a set using the ANY clause. sele...
Written by Sean Behan on 09/26/2013
Backup and Rotate MySQL Databases Simple Bash Script
Make a directory ( it can anywhere ) called baks/mysql mkdir -p /baks/mysql Create a file (it can be anywhere) called /root/ and put this script in it #!/bin/bash # modify the following to suit your environment export DB...
Written by Sean Behan on 05/16/2017
Installing Ruby on Rails 3, MySQL, Git, Ruby Enterprise Edition, Passenger (Mod_Rails) on Ubuntu with Rackspace Cloud.
Short and sweet. Here all the commands I run in this order to set up a brand new box. It usually takes about 10 - 15 minutes on a 256 MB RAM instance. Compiling Ruby Enterprise Edition, which is super easy, will take the most amount of time. It will seem ...
Written by Sean Behan on 06/17/2012
Deploying to Dreamhost
Remember to include the host declaration in the database.yml file when you deploy to Dreamhost. Dreamhost does not use "localhost" which is typically the default setting when using the mysql adapter and developing locally or even on a small site. At lea...
Written by Sean Behan on 06/17/2012
My Review of Moodle 1.9 Extension Development
I wrote a review for Joseph Thibault's Moodle News on extension development for Moodle. The book is quite good and I think an essential resource for anyone wanting to develop in Moodle. The book focuses on plugin development, but it will also give you an ...
Written by Sean Behan on 06/17/2012
Workshop Dog
Workshop Dog is a free events calendar for dog training workshops and group lessons. Users can list their training workshops as well as create a business profile. Jobs may be posted to the site for a small fee. Events that are created are distributed to...
Written by Sean Behan on 06/17/2012
Active Record Find Methods
Active Record find methods for selecting range from Student.find(:all, :conditions => { :grade => 9..12 }) return a range Student.find(:all, :conditions => { :grade => [9,11,12] }) will retu...
Written by Sean Behan on 06/17/2012
Dump MySQL Database without Drop Table Syntax
Output .sql file for MySQL but without the drop table syntax before table name use the --skip-add-drop-table flag mysqldump -u root -p database_name --skip-add-drop-table --skip-lock-tables > database_name.sql
Written by Sean Behan on 06/17/2012
Generate MySQL Datetime Type Using PHP Date() Function
If you want to insert a datetime that matches the default mysql datetime type format use this date('Y-m-d H:i:s');
Written by Sean Behan on 06/17/2012
Managing Timestamps in MySQL with a Trigger
MySQL doesn't support having two columns with time stamping on both initialization and/or on updating at the same time. It would be nice to be able to do *this* where the created_at column gets the current_timestamp on initialization and the updated_at ge...
Written by Sean Behan on 06/17/2012
Install do_mysql Ruby Gem on Mac OS X
I ran into the same problem when installing mysql gem for Rails development. This fix worked for me The same thing works with the data objects gem. Just specify the path...
Written by Sean Behan on 06/17/2012
Fixing MySQL for Rails 2.2 Development on Mac OS X
Oh what trouble Rails 2.2 and MySQL (on Mac OS X) can be. Rails, as of version >= 2.2, no longer comes bundled with the MySQL adapter. This means you'll need to install it yourself, but it appears that the gem for installing it is also broken. This will ...
Written by Sean Behan on 06/17/2012
Quick Syntax to Pipe an SQL Query Directly to a file
Here is a quick way to put the contents of a database table into a simple text file. This could be handy if for example, you just want to grab some emails and pop the results into a simple csv file. Your sql statement can be as creative as sql allows. All...
Written by Sean Behan on 06/17/2012
Sample Rails Database Config for MySQL
Sample Ruby on Rails database config file for connecting to mysql. production: adapter: mysql encoding: utf8 reconnect: false database: db_production pool: 5 username: db_user password: db_password #socket: /tmp/mysql.sock #this may vary ...
Written by Sean Behan on 06/17/2012
mysql on rails 2.3.2
mysql driver is no longer bundled w/ rails. you'll need to install it yourself w/ sudo gem install mysql however, on ubuntu (heron) this won't work. issue these commands first sudo apt-get install libmysql-ruby libmysqlclient-dev if libmysqlclient-dev...
Written by Sean Behan on 06/17/2012
Setting up a new ubuntu server with apache2, php, ruby on rails, rubygems, mysql, and git
Here are a list of commands to get up and running with Apache2 with Phussion Passenger for Rails, PHP5, MySQL5, Ruby on Rails with Gems, and the source control software Git. I think that this is a pretty ideal environment for a development box and even pr...
Written by Sean Behan on 06/17/2012