{"id":1059,"date":"2014-05-15T21:04:18","date_gmt":"2014-05-15T21:04:18","guid":{"rendered":"http:\/\/www.raccoonstar.com\/?p=1059"},"modified":"2014-09-13T20:27:00","modified_gmt":"2014-09-13T20:27:00","slug":"newb-sql-time","status":"publish","type":"post","link":"http:\/\/www.raccoonstar.com\/?p=1059","title":{"rendered":"Newb SQL Time"},"content":{"rendered":"

Because this is a blog for notes and I’m forgetful. Here’s some SQL things:<\/p>\n

1. Connecting to a remote mySQL server over ssh: (source<\/a>)
\nssh -L 3306:localhost:3306 raccoon@raccoonstar.com<\/code><\/p>\n

Now you can use MySQL Workbench instead of just a command line! woo!<\/p>\n

2. Now, to import my CSVs… Make the database
\ncreate database testdb;<\/code><\/p>\n

Then create the table… (After stealing Shamiq’s cleverness and using find-replace to steal the first line of the CSV and replace with varchar(255s…))
\ncreate table TableNameWoo (
\ncolumn1 varchar(255),
\ncolumn2 varchar(255),
\ncolumn3 varchar(255));<\/code><\/p>\n

Now that your table exists, import dat CSV~
\nLOAD DATA LOCAL INFILE '~\/csvs\/filename.csv'
\nINTO TABLE testdb.TableNameWoo FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n';<\/code><\/p>\n

mySQL then yelled at me: ERROR 1148 (42000): The used command is not allowed with this MySQL version<\/p>\n

Google then revealed that I needed to start mysql with “–local-infile”, which worked! (Though I’m not sure why)\u02c7<\/p>\n

Now you have a table with your CSV in it! Woo. :D<\/p>\n","protected":false},"excerpt":{"rendered":"

Because this is a blog for notes and I’m forgetful. Here’s some SQL things: 1. Connecting to a remote mySQL server over ssh: (source) ssh -L 3306:localhost:3306 raccoon@raccoonstar.com Now you can use MySQL Workbench instead of just a command line! woo! 2. Now, to import my CSVs… Make the database create database testdb; Then create […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[68],"tags":[105],"class_list":["post-1059","post","type-post","status-publish","format-standard","hentry","category-coding","tag-sql"],"_links":{"self":[{"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=\/wp\/v2\/posts\/1059","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1059"}],"version-history":[{"count":4,"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=\/wp\/v2\/posts\/1059\/revisions"}],"predecessor-version":[{"id":1064,"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=\/wp\/v2\/posts\/1059\/revisions\/1064"}],"wp:attachment":[{"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1059"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1059"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.raccoonstar.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1059"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}