I was having trouble figuring out how to dump the data from PostgreSQL with PGAdmin until I have this on PostgreSQL Tutorial.
Following the directions, I was able to do a dump as .tar file. They also have instructions on how to restore it.
I needed to seed my app, hosted on Heroku, with new users. Since I wanted to created a lot of the them, I wanted to CSV. My users have avatars, so I wanted to upload those for the users as well.
Here’s how I did it:
1. I stored the original images I wanted to use for the avatars on AWS S3 (make sure that they are set with public permission to read them.
2. I created a CSV that had columns (in this order) for the users: username, email, password, password_confirmation, avatar. In the avatar field, I put the URL of the S3 image. I named this CSV file “user_data” and put it in the DB directory.
3. Using the answer to this Stack Overflow question, I created a file (I named user_seed.rake) in lib/tasks. The code in the file I used was:
namespace :csv do
desc “Import CSV Data”
task :import_stuff => :environment do
csv_file_path = ‘db/user_data.csv’
CSV.foreach(csv_file_path) do |row|
:username => row,
:email => row,
:password => row,
:password_confirmation => row,
:avatar => URI.parse(row)
puts “Row added!”
4. Run rake csv:import_stuff
I needed to select the last records in a table. I could do a MySQL query with no limit on records, but that could be inefficient. Instead you could and this to the end of your MySQL query:
order by id desc limit x
SELECT * FROM articles order by id desc limit 5
This will return the last five records in the table.
After completing development of one of my apps, I had to load one of my MySQL tables with data. After putting the data in CSV format, I wanted to upload it to my MySQL table. I searched the web and found the syntax to use (I use MySQL workbench and from there I went into the database and created and executed a new query). However, it took awhile for me to figure out how to make the file name work.
Here’s the syntax I used in MySQL Workbench for the query to execute (replace the text in colors with your data):
load data local infile ‘C://yourdirectory/yourdirectory2/filename.csv‘ into table your_table_name fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’
(field1, field2, field3, field4, field5)
(for some reason the characters don’t copy correctly from this post, so you can copy similar syntax from here)