-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinsert_data.rb
More file actions
80 lines (65 loc) · 1.86 KB
/
Copy pathinsert_data.rb
File metadata and controls
80 lines (65 loc) · 1.86 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
require "csv"
require "pg"
filename = ARGV.shift
DB = PG.connect(dbname: "insights")
def take_client_data(row)
{
name: row["client_name"],
age: row["age"],
gender: row["gender"],
occupation: row["occupation"],
nationality: row["nationality"]
}
end
def take_restaurant_data(row)
{
name: row["restaurant_name"],
category: row["category"],
city: row["city"],
address: row["address"]
}
end
def take_dish_data(row)
{
name: row["dish"]
}
end
def take_dish_restaurant_data(row, restaurant, dish)
{
price_dish: row["price"],
dish_id: dish["id"],
restaurant_id: restaurant["id"]
}
end
def take_visit_data(row, client, dish_restaurant)
{
visit_date: row["visit_date"],
client_id: client["id"],
dishes_restaurants_id: dish_restaurant["id"]
}
end
def create(table, data)
data.transform_values! { |value| "'#{value.gsub("'", "''")}'" }
res = DB.exec("INSERT INTO #{table} (#{data.keys.join(',')}) VALUES (#{data.values.join(',')}) RETURNING *;")
res.first
end
def find(table, column, value)
value = "'#{value.gsub("'", "''")}'"
res = DB.exec("SELECT * from #{table} where #{column} = #{value};")
res.first
end
def find_or_create(table, column, data)
find(table, column, data[column.to_sym]) || create(table, data)
end
CSV.foreach(filename, headers: true) do |row|
client_data = take_client_data(row)
client = find_or_create("clients", "name", client_data)
restaurant_data = take_restaurant_data(row)
restaurant = find_or_create("restaurants", "name", restaurant_data)
dish_data = take_dish_data(row)
dish = find_or_create("dishes", "name", dish_data)
dish_restaurant_data = take_dish_restaurant_data(row, restaurant, dish)
dish_restaurant = create("dishes_restaurants", dish_restaurant_data)
visits_data = take_visit_data(row, client, dish_restaurant)
create("visits", visits_data)
end