In [1]:
import pandas as pd
from sqlalchemy import create_engine
In [2]:
user = 'arvind'
passw = 'biomass123'
str1 = 'postgresql+pg8000://' + user +':' + passw + '@switch-db2.erg.berkeley.edu:5432/apl_cec?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory'
engine_source = create_engine(str1)
In [3]:
df_feeders = pd.read_csv('/Users/Arvind/biomass_data/csv/PGE_Feeders_Final_Feeders.csv')
In [4]:
del df_feeders['Pt 2 Lat']
del df_feeders['Pt 2 Long']
In [5]:
abbreviations = dict()
abbreviations['Feeder #'] = 'fnum'
abbreviations['Name'] = 'name'
abbreviations['Nominal Circuit Voltage (kv)'] = 'ncv'
abbreviations['Circuit Capacity (MW)'] = 'cc'
abbreviations['Circuit Projected Peak Load (MW)'] = 'cppl'
abbreviations['SS Bank #'] = 'ssb'
abbreviations['Existing Distributed Generation (MW)'] = 'edg'
abbreviations['Queued Distributed Generation (MW)'] = 'qdg'
abbreviations['Total Distributed Generation (MW)'] = 'tdg'
abbreviations['LS_ID'] = 'ls_id'
abbreviations['Zone ID'] = 'z_id'
abbreviations['Lat'] = 'lat'
abbreviations['Long'] = 'lon'
df_feeders = df_feeders.rename(columns = abbreviations)
In [6]:
list(df_feeders.columns.values)
df_feeders
Out[6]:
fnum
name
ncv
cc
cppl
ssb
edg
qdg
tdg
ls_id
z_id
lat
lon
0
12011108
OAKLAND 1108
12
0.00
2.29
OAKLAND #3
0.17540
0.00
0.17540
600001913
Default
37.80949
-122.29334
1
12011109
OAKLAND 1109
12
8.75
5.05
OAKLAND #2
6.78560
0.00
6.78560
600003618
12011109-19
37.79171
-122.24822
2
12011110
OAKLAND 1110
12
10.26
4.83
OAKLAND #3
8.06470
0.00
8.06470
600000516
12011110-1
37.80126
-122.29040
3
12011111
OAKLAND 1111
12
8.42
4.68
OAKLAND #1
0.00000
0.00
0.00000
600015326
Default
37.79809
-122.28077
4
12011112
OAKLAND 1112
12
8.51
4.47
OAKLAND #1
0.08150
0.00
0.08150
600001534
12011112-9
37.80470
-122.28675
5
12011113
OAKLAND 1113
12
8.51
3.51
OAKLAND #2
0.26330
0.00
0.26330
600000713
12011113-1
37.80261
-122.28857
6
12011114
OAKLAND 1114
12
10.28
5.92
OAKLAND #2
0.23160
0.00
0.23160
600007379
12011114-1
37.79283
-122.25773
7
12011115
OAKLAND 1115
12
7.37
6.01
OAKLAND #2
0.07620
0.00
0.07620
600004592
12011115-17
37.79751
-122.26819
8
12011116
OAKLAND 1116
12
7.19
2.27
OAKLAND #1
0.56240
0.00
0.56240
600003540
12011116-8
37.82361
-122.27685
9
12011117
OAKLAND 1117
12
8.42
2.52
OAKLAND #2
0.07990
0.00
0.07990
600002879
12011117-2
37.81486
-122.28886
10
12011118
OAKLAND 1118
12
8.42
1.58
OAKLAND #3
0.10000
0.00
0.10000
600001220
12011118-1
37.82024
-122.29245
11
12011119
OAKLAND 1119
12
6.91
3.59
OAKLAND #2
3.40000
0.00
3.40000
600000691
12011119-1
37.80087
-122.28925
12
12011120
OAKLAND 1120
12
8.51
5.65
OAKLAND #1
0.00340
0.00
0.00340
600000304
12011120-1
37.80009
-122.28439
13
12011121
OAKLAND 1121
12
8.51
2.89
OAKLAND #1
0.82710
0.00
0.82710
600001886
12011121-2
37.80431
-122.29037
14
12011132
OAKLAND 1132
12
9.61
2.69
OAKLAND #3
0.00000
0.00
0.00000
600000293
12011132-1
37.79808
-122.28122
15
12011133
OAKLAND 1133
12
9.61
6.20
OAKLAND #3
0.11780
0.00
0.11780
600000831
12011133-6
37.80240
-122.26959
16
12011134
OAKLAND 1134
12
7.56
5.97
OAKLAND #2
0.00000
0.00
0.00000
600001235
12011134-1
37.82208
-122.29084
17
12021103
CLAYTON 1103
12
5.00
3.12
CLAYTON #5
0.38070
0.00
0.38070
900007331
12021103-1
37.95360
-121.96499
18
12021104
CLAYTON 1104
12
9.10
6.61
CLAYTON #5
1.21240
0.00
1.21240
900000690
Default
37.96195
-121.96481
19
12022211
CLAYTON 2211
21
21.50
13.26
CLAYTON #3
0.79960
0.00
0.79960
900000962
Default
37.95519
-121.95837
20
12022212
CLAYTON 2212
21
22.60
17.56
CLAYTON #3
2.54650
0.00
2.54650
900065809
Default
37.87084
-121.73205
21
12022213
CLAYTON 2213
21
19.00
13.30
CLAYTON #1
0.87750
0.00
0.87750
900001445
12022213-14
37.93542
-121.87777
22
12022214
CLAYTON 2214
21
19.00
7.26
CLAYTON #3
0.28720
0.00
0.28720
900027899
12022214-1
37.94489
-121.96999
23
12022215
CLAYTON 2215
21
22.60
21.37
CLAYTON #2
4.35700
0.00
4.35700
900092873
Default
37.85663
-121.97281
24
12022216
CLAYTON 2216
21
21.30
20.74
CLAYTON #1
1.98736
0.00
1.98736
900005095
12022216-1
37.94834
-121.96815
25
12022217
CLAYTON 2217
21
19.00
14.55
CLAYTON #2
1.07000
0.00
1.07000
900028009
12022217-1
37.94464
-121.96981
26
12022218
CLAYTON 2218
21
21.80
5.59
CLAYTON #1
0.62900
0.00
0.62900
900033930
12022218-4
37.94154
-121.92910
27
12040406
SUBSTATION D 0406
4
2.09
1.07
SUBSTATION D #44
0.03140
0.00
0.03140
600005608
12040406-1
37.83200
-122.27040
28
12040407
SUBSTATION D 0407
4
2.32
0.64
SUBSTATION D #44
0.00640
0.00
0.00640
600006476
12040407-1
37.83718
-122.25071
29
12040408
SUBSTATION D 0408
4
2.16
1.56
SUBSTATION D #44
0.06960
0.00
0.06960
600006058
Default
37.84586
-122.25996
...
...
...
...
...
...
...
...
...
...
...
...
...
...
3105
255331101
SHARON 1101
12
10.00
0.00
SHARON #1
0.00000
0.00
0.00000
1100046766
Default
37.09062
-120.16108
3106
255361104
LE GRAND 1104
12
7.03
8.96
LE GRAND #1
1.44100
0.00
1.44100
1100077355
255361104-17
37.21260
-119.96445
3107
255361106
LE GRAND 1106
12
8.66
5.00
LE GRAND #1
0.59600
0.00
0.59600
1100045458
255361106-37
37.17835
-120.27406
3108
255361110
LE GRAND 1110
12
11.90
11.18
LE GRAND #1
0.87710
0.00
0.87710
1100008572
Default
37.19911
-120.09179
3109
255361112
LE GRAND 1112
12
9.10
0.00
LE GRAND #1
0.00000
0.00
0.00000
1100094163
Default
37.18444
-120.27414
3110
255371106
ORO LOMA 1106
12
9.01
8.34
ORO LOMA #1
1.18000
0.00
1.18000
1100145696
255371106-55
36.86486
-120.67215
3111
255371108
ORO LOMA 1108
12
8.73
0.00
ORO LOMA #1
0.03460
0.00
0.03460
1100004689
255371108-21
36.88579
-120.50802
3112
255371110
ORO LOMA 1110
12
5.83
2.72
ORO LOMA #1
0.00000
0.00
0.00000
1100215796
Default
36.88658
-120.65545
3113
255371112
ORO LOMA 1112
12
3.80
2.01
ORO LOMA #1
0.00000
0.00
0.00000
1100211845
Default
36.88647
-120.65559
3114
255371114
ORO LOMA 1114
12
8.72
4.34
ORO LOMA #1
0.00000
0.00
0.00000
1100211871
Default
36.88636
-120.65558
3115
255371115
ORO LOMA 1115
12
12.19
4.90
ORO LOMA #3
0.00500
0.00
0.00500
1100147694
255371115-1
36.95287
-120.63030
3116
255371116
ORO LOMA 1116
12
12.19
2.46
ORO LOMA #3
0.08400
0.00
0.08400
1100002645
255371116-4
36.85077
-120.56895
3117
255371117
ORO LOMA 1117
12
12.19
6.07
ORO LOMA #3
0.07340
0.00
0.07340
1100065838
255371117-1
36.88500
-120.65446
3118
255371118
ORO LOMA 1118
12
12.19
2.89
ORO LOMA #3
0.07112
5.00
5.07112
1100004297
Default
36.78583
-120.53071
3119
255391101
BONITA 1101
12
8.69
6.84
BONITA #1
0.80610
0.75
1.55610
1100012588
255391101-6
36.98166
-120.13063
3120
255391102
BONITA 1102
12
8.69
5.26
BONITA #1
0.53270
0.00
0.53270
1100010484
255391102-10
36.96000
-120.10058
3121
255391103
BONITA 1103
12
8.42
5.60
BONITA #1
0.00890
0.00
0.00890
1100012133
255391103-2
36.95811
-120.20251
3122
255421101
TEXACO EMIDIO 1101
12
0.00
0.00
TEXACO EMIDIO #1
0.00000
0.00
0.00000
1300046274
Default
35.06025
-119.06461
3123
255441101
TEXACO PIPELINE GRPVINE 1101
12
0.00
0.00
TEXACO PIPELINE GRPVINE #1
0.00000
0.00
0.00000
1300046279
Default
34.92701
-118.92374
3124
255451101
CALWATER 1101
12
14.28
1.33
CALWATER #1
0.00000
0.00
0.00000
1300049752
Default
35.42183
-118.90032
3125
255451102
CALWATER 1102
12
13.74
11.16
CALWATER #1
3.82980
0.00
3.82980
1300006790
255451102-26
35.43640
-118.74274
3126
258111101
SO. CAL. EDISON #11 1101
12
10.00
0.00
SO. CAL. EDISON #11 #1
0.00000
0.00
0.00000
1300029956
258111101-1
35.66029
-119.21751
3127
258131101
S.C.E. 13 1101
12
10.00
0.00
S.C.E. 13 #1
0.00000
0.00
0.00000
1300030004
Default
35.19452
-118.70472
3128
258181101
BOLTHOUSEFARMS 1101
12
10.00
0.00
BOLTHOUSEFARMS #1
0.00000
0.00
0.00000
1300035560
Default
35.35798
-118.92555
3129
258191101
SCE 1101
12
0.00
0.00
SCE #1
0.00000
0.00
0.00000
1300058507
Default
34.82473
-118.87928
3130
258531101
CRANE VALLEY 1101
12
10.00
0.00
CRANE VALLEY #1
0.00000
0.00
0.00000
1100021577
Default
37.29129
-119.52901
3131
258591101
TULE POWER HOUSE 1101
12
1.58
0.00
TULE POWER HOUSE #1
0.00000
0.00
0.00000
1200022196
Default
36.18894
-118.67010
3132
258851101
SO. CAL. EDISON #2 1101
12
0.50
0.00
SO. CAL. EDISON #2 #1
0.01010
0.00
0.01010
1100157971
Default
37.16467
-119.42913
3133
258861101
SO. CAL. EDISON #3 1101
12
0.78
0.00
SO. CAL. EDISON #3 #1
0.01180
0.00
0.01180
1100062355
258861101-1
37.14724
-119.40650
3134
258881001
SO. CAL. EDISON 1001
12
0.00
0.00
SO. CAL. EDISON #1
0.00000
0.00
0.00000
1200045589
258881001-2
36.04362
-119.42926
3135 rows × 13 columns
In [7]:
df_feeders.to_sql('pge_feeders', engine_source, schema='PGE', if_exists = 'replace', chunksize=1000)
---------------------------------------------------------------------------
InterfaceError Traceback (most recent call last)
<ipython-input-7-3c0b9b809a18> in <module>()
----> 1 df_feeders.to_sql('pge_feeders', engine_source, schema='PGE', if_exists = 'replace', chunksize=1000)
/Users/Arvind/anaconda/lib/python2.7/site-packages/pandas/core/generic.pyc in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
1163 sql.to_sql(self, name, con, flavor=flavor, schema=schema,
1164 if_exists=if_exists, index=index, index_label=index_label,
-> 1165 chunksize=chunksize, dtype=dtype)
1166
1167 def to_pickle(self, path):
/Users/Arvind/anaconda/lib/python2.7/site-packages/pandas/io/sql.pyc in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
569 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
570 index_label=index_label, schema=schema,
--> 571 chunksize=chunksize, dtype=dtype)
572
573
/Users/Arvind/anaconda/lib/python2.7/site-packages/pandas/io/sql.pyc in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
1247 if_exists=if_exists, index_label=index_label,
1248 schema=schema, dtype=dtype)
-> 1249 table.create()
1250 table.insert(chunksize)
1251 if (not name.isdigit() and not name.islower()):
/Users/Arvind/anaconda/lib/python2.7/site-packages/pandas/io/sql.pyc in create(self)
688
689 def create(self):
--> 690 if self.exists():
691 if self.if_exists == 'fail':
692 raise ValueError("Table '%s' already exists." % self.name)
/Users/Arvind/anaconda/lib/python2.7/site-packages/pandas/io/sql.pyc in exists(self)
676
677 def exists(self):
--> 678 return self.pd_sql.has_table(self.name, self.schema)
679
680 def sql_schema(self):
/Users/Arvind/anaconda/lib/python2.7/site-packages/pandas/io/sql.pyc in has_table(self, name, schema)
1275 self.connectable.dialect.has_table,
1276 name,
-> 1277 schema or self.meta.schema,
1278 )
1279
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in run_callable(self, callable_, *args, **kwargs)
1969
1970 """
-> 1971 with self.contextual_connect() as conn:
1972 return conn.run_callable(callable_, *args, **kwargs)
1973
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in contextual_connect(self, close_with_result, **kwargs)
2037 return self._connection_cls(
2038 self,
-> 2039 self._wrap_pool_connect(self.pool.connect, None),
2040 close_with_result=close_with_result,
2041 **kwargs)
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _wrap_pool_connect(self, fn, connection)
2076 if connection is None:
2077 Connection._handle_dbapi_exception_noconnection(
-> 2078 e, dialect, self)
2079 else:
2080 util.reraise(*sys.exc_info())
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception_noconnection(cls, e, dialect, engine)
1403 util.raise_from_cause(
1404 sqlalchemy_exception,
-> 1405 exc_info
1406 )
1407 else:
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
200 exc_type, exc_value, exc_tb = exc_info
201 cause = exc_value if exc_value is not exception else None
--> 202 reraise(type(exception), exception, tb=exc_tb, cause=cause)
203
204 if py3k:
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _wrap_pool_connect(self, fn, connection)
2072 dialect = self.dialect
2073 try:
-> 2074 return fn()
2075 except dialect.dbapi.Error as e:
2076 if connection is None:
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.pyc in connect(self)
374 """
375 if not self._use_threadlocal:
--> 376 return _ConnectionFairy._checkout(self)
377
378 try:
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.pyc in _checkout(cls, pool, threadconns, fairy)
711 def _checkout(cls, pool, threadconns=None, fairy=None):
712 if not fairy:
--> 713 fairy = _ConnectionRecord.checkout(pool)
714
715 fairy._pool = pool
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.pyc in checkout(cls, pool)
478 @classmethod
479 def checkout(cls, pool):
--> 480 rec = pool._do_get()
481 try:
482 dbapi_connection = rec.get_connection()
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.pyc in _do_get(self)
1058 except:
1059 with util.safe_reraise():
-> 1060 self._dec_overflow()
1061 else:
1062 return self._do_get()
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc in __exit__(self, type_, value, traceback)
58 exc_type, exc_value, exc_tb = self._exc_info
59 self._exc_info = None # remove potential circular references
---> 60 compat.reraise(exc_type, exc_value, exc_tb)
61 else:
62 if not compat.py3k and self._exc_info and self._exc_info[1]:
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.pyc in _do_get(self)
1055 if self._inc_overflow():
1056 try:
-> 1057 return self._create_connection()
1058 except:
1059 with util.safe_reraise():
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.pyc in _create_connection(self)
321 """Called by subclasses to create a new ConnectionRecord."""
322
--> 323 return _ConnectionRecord(self)
324
325 def _invalidate(self, connection, exception=None):
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.pyc in __init__(self, pool)
447 def __init__(self, pool):
448 self.__pool = pool
--> 449 self.connection = self.__connect()
450 self.finalize_callback = deque()
451
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.pyc in __connect(self)
605 try:
606 self.starttime = time.time()
--> 607 connection = self.__pool._invoke_creator(self)
608 self.__pool.logger.debug("Created new connection %r", connection)
609 return connection
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/strategies.pyc in connect(connection_record)
95 if connection is not None:
96 return connection
---> 97 return dialect.connect(*cargs, **cparams)
98
99 creator = pop_kwarg('creator', connect)
/Users/Arvind/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in connect(self, *cargs, **cparams)
383
384 def connect(self, *cargs, **cparams):
--> 385 return self.dbapi.connect(*cargs, **cparams)
386
387 def create_connect_args(self, url):
/Users/Arvind/anaconda/lib/python2.7/site-packages/pg8000/__init__.pyc in connect(user, host, unix_sock, port, database, password, ssl, timeout, **kwargs)
103 """
104 return Connection(
--> 105 user, host, unix_sock, port, database, password, ssl, timeout)
106
107 apilevel = "2.0"
/Users/Arvind/anaconda/lib/python2.7/site-packages/pg8000/core.pyc in __init__(self, user, host, unix_sock, port, database, password, ssl, timeout)
1288 except socket.error as e:
1289 self._usock.close()
-> 1290 raise InterfaceError("communication error", e)
1291 self._flush = self._sock.flush
1292 self._read = self._sock.read
InterfaceError: (pg8000.core.InterfaceError) ('communication error', error(60, 'Operation timed out'))
In [ ]:
Content source: peteWT/cec_apl
Similar notebooks: