from collections import OrderedDict
from peewee import *
import re
import requests

db = PostgresqlDatabase(
  'andersen',
  user = 'andersen',
  password = 'kniB3duE4drYil6hinD2yeD8juth8coUn2ef7Ayb',
  host = 'andersen-db.cu9qvcawtap6.us-east-1.rds.amazonaws.com'
  )

db.connect()



class strain(Model):
  """C Elegans strain complete info database"""
  strain = CharField()
  isotype = CharField(null=True)
  longitude = FloatField(null = True)
  latitude = FloatField(null = True)
  isolation = CharField(null=True)
  location = CharField(null=True)
  prev_names = CharField(null=True)
  warning_msg =  CharField(null=True)
  sequenced = BooleanField()

  class Meta:
    database = db


db.drop_tables([strain], safe=True)
db.create_tables([strain])

r = requests.get("https://raw.githubusercontent.com/AndersenLab/Andersen-Lab-Strains/master/processed/strain_info_join.tsv")

lines = r.text.splitlines()


header = ["strain", "isotype", "longitude", "latitude", "isolation", "location", "prev_names", 
"warning_msg", "sequenced"]

for line in lines[1:]:
  strain_info = re.split('\t',line)
  data = OrderedDict(zip(header,strain_info))
  data = {k:v for k,v in data.items()}
  for k in data.keys():
    if data[k] == "NA":
      data[k] = None
  strain(**data).save()
 
@@ -80,209 +80,279 @@
 sts%0A
-%0Adb = PostgresqlDatabase(%0A  'andersen',%0A  user = 'andersen',%0A  password = 'kniB3duE4drYil6hinD2yeD8juth8coUn2ef7Ayb',%0A  host = 'andersen-db.cu9qvcawtap6.us-east-1.rds.amazonaws.com'%0A  )%0A%0Adb.connect()%0A%0A
+import json%0A%0Acredentials = json.loads(open(%22credentials.json%22,'r').read())%0A%0Adb = PostgresqlDatabase(%0A  'andersen',%0A  **credentials%0A  )%0A%0Adb.connect()%0A%0A%0Abooldict = %7B%22TRUE%22: True, %22FALSE%22: False%7D%0A%0A#=======================#%0A# Generate strain table #%0A#=======================#
 %0A%0Acl
@@ -439,16 +439,28 @@
 arField(
+index = True
 )%0A  isot
@@ -472,32 +472,46 @@
  CharField(null=
+True, index = 
 True)%0A  longitud
@@ -846,17 +846,157 @@
 rain%5D)%0A%0A
-r
+header = %5B%22strain%22, %22isotype%22, %22longitude%22, %22latitude%22, %22isolation%22, %22location%22, %22prev_names%22, %0A%22warning_msg%22, %22sequenced%22%5D%0A%0Astrain_info_join
  = reque
@@ -1120,17 +1120,32 @@
 lines = 
-r
+strain_info_join
 .text.sp
@@ -1160,134 +1160,45 @@
 ()%0A%0A
-%0Aheader = %5B%22strain%22, %22isotype%22, %22longitude%22, %22latitude%22, %22isolation%22, %22location%22, %22prev_names%22, %0A%22warning_msg%22, %22sequenced%22%5D%0A%0A
+strain_data = %5B%5D%0Awith db.atomic():%0A  
 for 
@@ -1216,16 +1216,18 @@
 es%5B1:%5D:%0A
+  
   strain
@@ -1256,20 +1256,19 @@
 line)%0A  
-data
+  l
  = Order
@@ -1301,20 +1301,19 @@
 nfo))%0A  
-data
+  l
  = %7Bk:v 
@@ -1323,20 +1323,17 @@
  k,v in 
-data
+l
 .items()
@@ -1334,16 +1334,64 @@
 tems()%7D%0A
+    l%5B%22sequenced%22%5D = booldict%5Bl%5B%22sequenced%22%5D%5D%0A  
   for k 
@@ -1393,20 +1393,17 @@
 or k in 
-data
+l
 .keys():
@@ -1411,15 +1411,14 @@
     
+  
 if 
-data
+l
 %5Bk%5D 
@@ -1432,20 +1432,19 @@
 :%0A      
-data
+  l
 %5Bk%5D = No
@@ -1446,24 +1446,26 @@
  = None%0A
+  
   strain
 (**data)
@@ -1460,21 +1460,86 @@
 rain
-(**data).sav
+_data.append(l)%0A%0Awith db.atomic():%0A  strain.insert_many(strain_data).execut
 e()%0A 
+  

Remove credentials from file; speed up insert