-
Notifications
You must be signed in to change notification settings - Fork 0
/
export2.py
49 lines (43 loc) · 1.42 KB
/
export2.py
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
from sqlalchemy import create_engine
import pandas as pd
# Read data from CSV into a Pandas DataFrame
csv_file = 'taxi_preprocessed2.csv'
df = pd.read_csv(csv_file, nrows= 100)
# Connect to database
engine = create_engine('postgresql://postgres:123456@localhost:5432/taxi_trajectory')
#['trip_id', 'call_type', 'origin_call', 'origin_stand', 'taxi_id',
# 'timestamp', 'day_type', 'missing_data', 'polyline', 'distance', 'year',
# 'month', 'day', 'hour', 'week_day', 'start_lat', 'start_long',
# 'end_lat', 'end_long', 'geom', 'total_time', 'isnotvalid']
# Create PostGIS table
create_table_query = '''
CREATE TABLE IF NOT EXISTS taxi_trips (
trip_id BIGINT PRIMARY KEY,
call_type VARCHAR(1),
origin_call BIGINT,
origin_stand BIGINT,
taxi_id BIGINT,
timestamp BIGINT,
day_type VARCHAR(1),
missing_data BOOLEAN,
polyline TEXT,
distance FLOAT,
year INTEGER,
month INTEGER,
day INTEGER,
hour INTEGER,
week_day INTEGER,
start_lat FLOAT,
start_long FLOAT,
end_lat FLOAT,
end_long FLOAT,
geom GEOMETRY(LINESTRING, 4326),
total_time BIGINT,
isnotvalid BOOLEAN
);
'''
#with engine.connect() as connection:
# connection.execute(create_table_query)
# Insert data into table
table_name = 'taxi_trips'
df.to_sql(table_name, engine, if_exists='append', index=False, method='multi', chunksize=100)