Pull events for a given country between 2001 and 2011, for Simon's party project.

event-pull-for-simon.sql

# Log in to event database first, e.g. 
# mysql -u xxx -p -h IPaddress -D event_data

SET @countryid = (SELECT id FROM countries WHERE countryname='CYPRUS');

# Pull events and related information, takes 1.5m for Cyprus, and save 
# to a csv file
CREATE TABLE temp_results AS
SELECT e.event_ID,
       e.event_date,
       sact.name AS source_actor_name,
       tact.name AS target_actor_name,
       types.name AS event_type_name
FROM events e
JOIN dict_actors sact ON sact.actor_id = e.source_actor_id
JOIN dict_actors tact ON tact.actor_id = e.source_actor_id
JOIN eventtypes types ON types.eventtype_ID = e.eventtype_ID
WHERE e.location_id IN
      (SELECT location_id
       FROM locations
       WHERE country_id = @countryid)
AND  event_date BETWEEN '2001-01-01' AND '2011-12-31'
ORDER BY e.event_ID;

# Log out from mySQL
\q

# Now, in R:
#library(RMySQL)
#conn <- dbConnect(MySQL(), user="me", password="pw", dbname="event_data",
#                  host="ipaddress")
#data <- dbGetQuery(conn, "SELECT * FROM temp_results;")
#write.csv(data, file="~/Desktop/cyprus.csv", row.names=F)
#dbDisconnect(conn)  # close database connection once you are done