In [3]:

    

from pony.orm import *
import pymysql
from datetime import date, datetime


    

In [1]:

    

import logging
logging.basicConfig(filename='/Users/ZihanZhou/GoogleCloud/2016spring/mada/graphql_flask_pony_crunchBase/pony/pony.log', level=logging.INFO)


    

In [4]:

    

sql_debug(True)


    

In [5]:

    

db = Database()


    

In [1]:

    

# class Person(db.Entity):
#     name = Required(str)
#     age = Required(int)
#     cars = Set("Car")
# class Car(db.Entity):
#     make = Required(str)
#     model = Required(str)
#     owner = Required(Person)
from pony.orm import *
import pymysql
from datetime import date, datetime
from decimal import Decimal
db = Database()

class People(db.Entity):
    _table_ = "cb_people"
    id = Required(int, unique=True)
    object_id = PrimaryKey(str)
    first_name = Required(str)
    last_name = Required(str)
    birthplace = Optional(str)
    affiliation_name = Optional(str)
    degrees = Set("Degrees")
    relationship = Set("Relationships")
    

class Degrees(db.Entity):
    _table_ = "cb_degrees"
    id = PrimaryKey(int, auto=True)
    owner = Required(People, column="object_id")
    degree_type = Optional(str)
    subject = Optional(str)
    institution = Optional(str)
    graduated_at = Optional(date)

class Offices(db.Entity):
    _table_ = "cb_offices"
    owner = Required("Objects",column="object_id")
    id = PrimaryKey(int)
    office_id = Required(int,unique=True)
    description = Optional(str)
    region = Optional(str)
    address1 = Optional(str)
    address2 = Optional(str)
    city = Optional(str)
    zip_code = Optional(str)
    state_code = Optional(str)
    country_code = Optional(str)
    latitude = Optional(str)
    longitude = Optional(str)

class Objects(db.Entity):
    _table_="cb_objects"
    relationship = Set("Relationships")
    id = PrimaryKey(str)
    entity_type = Required(str)
    entity_id = Required(int)
    composite_key(entity_type,entity_id)
    parent_id = Optional(str)
    name = Required(str)
    normalized_name = Required(str)
    permalink = Required(str)
    category_code = Optional(str)
    founded_at = Optional(date)
    closed_at = Optional(date)
    domain = Optional(str)
    homepage_url = Optional(str)
    twitter_username = Optional(str)
    logo_url = Optional(str)
    logo_width = Optional(int)
    logo_height = Optional(int)
    short_description = Optional(str)
    description = Optional(str)
    tag_list = Optional(str)
    country_code = Optional(str)
    state_code = Optional(str)
    city = Optional(str)
    region = Optional(str)
    first_investment_at = Optional(date)
    last_investment_at = Optional(date)
    investment_rounds = Optional(int)
    invested_companies = Optional(int)
    first_funding_at = Optional(date)
    last_funding_at = Optional(date)
    funding_rounds = Optional(int)
    funding_total_usd = Optional(Decimal)
    first_milestone_at = Optional(date)
    last_milestone_at = Optional(date)
    milestones = Optional(int)
    relationships = Optional(int)
    offices = Set(Offices)
    created_by = Optional(str)
    created_at = Optional(datetime)
    updated_at = Optional(datetime)
    
class Relationships(db.Entity):
    _table_="cb_relationships"
    id = PrimaryKey(int)
    relationship_id = Required(int)
    person_object_id = Required(People)
    relationship_object_id = Required(Objects)
    start_at = Optional(date)
    end_at = Optional(date)
    is_past = Optional(int)
    title = Optional(str)
    

db.bind('mysql', host='', user='root', passwd='zh3036', db='cb_test')
db.generate_mapping(check_tables=True, create_tables=True)


    

In [6]:

    

People.select(lambda x:"Zuckerberg"in x.last_name)[:]


    

    
Out[6]:





[People[u'p:10'], People[u'p:27818']]

In [2]:

    




    

In [5]:

    

for i in a.relationship:
    print i


    

    




Relationships[9]
Relationships[433506]

In [11]:

    

Relationships[9].to_dict()


    

    
Out[11]:





{'end_at': None,
 'id': 9,
 'is_past': 0,
 'person_object_id': u'p:10',
 'relationship_id': 9,
 'relationship_object_id': u'c:5',
 'start_at': None,
 'title': u'Founder and CEO, Board Of Directors'}

In [ ]:

    




    

In [15]:

    

Objects.select(lambda x: "facebook.com" in x.domain)[:]


    

    
Out[15]:





[Objects[u'c:104650'],
 Objects[u'c:12337'],
 Objects[u'c:130849'],
 Objects[u'c:141770'],
 Objects[u'c:147409'],
 Objects[u'c:152492'],
 Objects[u'c:152503'],
 Objects[u'c:152694'],
 Objects[u'c:152840'],
 Objects[u'c:153326'],
 Objects[u'c:153798'],
 Objects[u'c:15961'],
 Objects[u'c:164670'],
 Objects[u'c:164989'],
 Objects[u'c:166025'],
 Objects[u'c:167560'],
 Objects[u'c:168155'],
 Objects[u'c:171208'],
 Objects[u'c:173229'],
 Objects[u'c:175582'],
 Objects[u'c:176522'],
 Objects[u'c:177564'],
 Objects[u'c:183507'],
 Objects[u'c:187926'],
 Objects[u'c:18834'],
 Objects[u'c:188394'],
 Objects[u'c:191207'],
 Objects[u'c:192120'],
 Objects[u'c:192588'],
 Objects[u'c:192676'],
 Objects[u'c:192940'],
 Objects[u'c:193175'],
 Objects[u'c:193651'],
 Objects[u'c:193844'],
 Objects[u'c:195849'],
 Objects[u'c:196803'],
 Objects[u'c:205401'],
 Objects[u'c:213141'],
 Objects[u'c:219721'],
 Objects[u'c:220122'],
 Objects[u'c:224142'],
 Objects[u'c:22594'],
 Objects[u'c:231439'],
 Objects[u'c:232868'],
 Objects[u'c:234694'],
 Objects[u'c:239650'],
 Objects[u'c:244007'],
 Objects[u'c:245766'],
 Objects[u'c:246608'],
 Objects[u'c:257212'],
 Objects[u'c:260809'],
 Objects[u'c:261523'],
 Objects[u'c:263346'],
 Objects[u'c:266285'],
 Objects[u'c:268473'],
 Objects[u'c:270462'],
 Objects[u'c:271213'],
 Objects[u'c:271441'],
 Objects[u'c:271483'],
 Objects[u'c:276494'],
 Objects[u'c:277460'],
 Objects[u'c:280634'],
 Objects[u'c:282411'],
 Objects[u'c:284984'],
 Objects[u'c:34623'],
 Objects[u'c:36244'],
 Objects[u'c:36405'],
 Objects[u'c:37621'],
 Objects[u'c:41310'],
 Objects[u'c:42334'],
 Objects[u'c:42412'],
 Objects[u'c:44174'],
 Objects[u'c:46656'],
 Objects[u'c:46947'],
 Objects[u'c:5'],
 Objects[u'c:52453'],
 Objects[u'c:53552'],
 Objects[u'c:5664'],
 Objects[u'c:57632'],
 Objects[u'c:58598'],
 Objects[u'c:59417'],
 Objects[u'c:6063'],
 Objects[u'c:60837'],
 Objects[u'c:60944'],
 Objects[u'c:62002'],
 Objects[u'c:62771'],
 Objects[u'c:6632'],
 Objects[u'c:69775'],
 Objects[u'c:70503'],
 Objects[u'c:71530'],
 Objects[u'c:75930'],
 Objects[u'c:81164'],
 Objects[u'c:8258'],
 Objects[u'c:85372'],
 Objects[u'f:2553'],
 Objects[u'p:100250'],
 Objects[u'p:101206'],
 Objects[u'p:101695'],
 Objects[u'p:102466'],
 Objects[u'p:104013'],
 Objects[u'p:105159'],
 Objects[u'p:106398'],
 Objects[u'p:108442'],
 Objects[u'p:109511'],
 Objects[u'p:109718'],
 Objects[u'p:110958'],
 Objects[u'p:111384'],
 Objects[u'p:111970'],
 Objects[u'p:113022'],
 Objects[u'p:113795'],
 Objects[u'p:114647'],
 Objects[u'p:114799'],
 Objects[u'p:114904'],
 Objects[u'p:116172'],
 Objects[u'p:118619'],
 Objects[u'p:119024'],
 Objects[u'p:119116'],
 Objects[u'p:119373'],
 Objects[u'p:119910'],
 Objects[u'p:11996'],
 Objects[u'p:120419'],
 Objects[u'p:120838'],
 Objects[u'p:121705'],
 Objects[u'p:123039'],
 Objects[u'p:123874'],
 Objects[u'p:126780'],
 Objects[u'p:126808'],
 Objects[u'p:127140'],
 Objects[u'p:131039'],
 Objects[u'p:131482'],
 Objects[u'p:131525'],
 Objects[u'p:132268'],
 Objects[u'p:132483'],
 Objects[u'p:132620'],
 Objects[u'p:132768'],
 Objects[u'p:134365'],
 Objects[u'p:134447'],
 Objects[u'p:134695'],
 Objects[u'p:134721'],
 Objects[u'p:134912'],
 Objects[u'p:135964'],
 Objects[u'p:136725'],
 Objects[u'p:136774'],
 Objects[u'p:136961'],
 Objects[u'p:137564'],
 Objects[u'p:140212'],
 Objects[u'p:140612'],
 Objects[u'p:140753'],
 Objects[u'p:142361'],
 Objects[u'p:143550'],
 Objects[u'p:146275'],
 Objects[u'p:147503'],
 Objects[u'p:147808'],
 Objects[u'p:147814'],
 Objects[u'p:147999'],
 Objects[u'p:148177'],
 Objects[u'p:149274'],
 Objects[u'p:149507'],
 Objects[u'p:150087'],
 Objects[u'p:150117'],
 Objects[u'p:150446'],
 Objects[u'p:151380'],
 Objects[u'p:152101'],
 Objects[u'p:152296'],
 Objects[u'p:152863'],
 Objects[u'p:153635'],
 Objects[u'p:153692'],
 Objects[u'p:154197'],
 Objects[u'p:155268'],
 Objects[u'p:156349'],
 Objects[u'p:156934'],
 Objects[u'p:159508'],
 Objects[u'p:160818'],
 Objects[u'p:161559'],
 Objects[u'p:161902'],
 Objects[u'p:163093'],
 Objects[u'p:163374'],
 Objects[u'p:163852'],
 Objects[u'p:166341'],
 Objects[u'p:166583'],
 Objects[u'p:167030'],
 Objects[u'p:167051'],
 Objects[u'p:167142'],
 Objects[u'p:167271'],
 Objects[u'p:167335'],
 Objects[u'p:168258'],
 Objects[u'p:168542'],
 Objects[u'p:168865'],
 Objects[u'p:169137'],
 Objects[u'p:169198'],
 Objects[u'p:169228'],
 Objects[u'p:169744'],
 Objects[u'p:170734'],
 Objects[u'p:171842'],
 Objects[u'p:172624'],
 Objects[u'p:172933'],
 Objects[u'p:173529'],
 Objects[u'p:173554'],
 Objects[u'p:173779'],
 Objects[u'p:173792'],
 Objects[u'p:174379'],
 Objects[u'p:175360'],
 Objects[u'p:175597'],
 Objects[u'p:175709'],
 Objects[u'p:175770'],
 Objects[u'p:177093'],
 Objects[u'p:177634'],
 Objects[u'p:178296'],
 Objects[u'p:178416'],
 Objects[u'p:178753'],
 Objects[u'p:179218'],
 Objects[u'p:179421'],
 Objects[u'p:179731'],
 Objects[u'p:179970'],
 Objects[u'p:180852'],
 Objects[u'p:180946'],
 Objects[u'p:181072'],
 Objects[u'p:181551'],
 Objects[u'p:18408'],
 Objects[u'p:185443'],
 Objects[u'p:185569'],
 Objects[u'p:185912'],
 Objects[u'p:187031'],
 Objects[u'p:187715'],
 Objects[u'p:187811'],
 Objects[u'p:189162'],
 Objects[u'p:190821'],
 Objects[u'p:192147'],
 Objects[u'p:192956'],
 Objects[u'p:195014'],
 Objects[u'p:19623'],
 Objects[u'p:197098'],
 Objects[u'p:197198'],
 Objects[u'p:198882'],
 Objects[u'p:199912'],
 Objects[u'p:201285'],
 Objects[u'p:201691'],
 Objects[u'p:203741'],
 Objects[u'p:205221'],
 Objects[u'p:205341'],
 Objects[u'p:205504'],
 Objects[u'p:206099'],
 Objects[u'p:206935'],
 Objects[u'p:207987'],
 Objects[u'p:211524'],
 Objects[u'p:212755'],
 Objects[u'p:215102'],
 Objects[u'p:215292'],
 Objects[u'p:216356'],
 Objects[u'p:218484'],
 Objects[u'p:219009'],
 Objects[u'p:221389'],
 Objects[u'p:221790'],
 Objects[u'p:231286'],
 Objects[u'p:232022'],
 Objects[u'p:232907'],
 Objects[u'p:23932'],
 Objects[u'p:241253'],
 Objects[u'p:242922'],
 Objects[u'p:243010'],
 Objects[u'p:245801'],
 Objects[u'p:249524'],
 Objects[u'p:254794'],
 Objects[u'p:256140'],
 Objects[u'p:257571'],
 Objects[u'p:260000'],
 Objects[u'p:26429'],
 Objects[u'p:26481'],
 Objects[u'p:266788'],
 Objects[u'p:36151'],
 Objects[u'p:38450'],
 Objects[u'p:40453'],
 Objects[u'p:41843'],
 Objects[u'p:42113'],
 Objects[u'p:42404'],
 Objects[u'p:42986'],
 Objects[u'p:44421'],
 Objects[u'p:45831'],
 Objects[u'p:46674'],
 Objects[u'p:47636'],
 Objects[u'p:4982'],
 Objects[u'p:50298'],
 Objects[u'p:51249'],
 Objects[u'p:52684'],
 Objects[u'p:52737'],
 Objects[u'p:52929'],
 Objects[u'p:54048'],
 Objects[u'p:54630'],
 Objects[u'p:56007'],
 Objects[u'p:5731'],
 Objects[u'p:58330'],
 Objects[u'p:58417'],
 Objects[u'p:59396'],
 Objects[u'p:59750'],
 Objects[u'p:61522'],
 Objects[u'p:61910'],
 Objects[u'p:64086'],
 Objects[u'p:64780'],
 Objects[u'p:65220'],
 Objects[u'p:66942'],
 Objects[u'p:67688'],
 Objects[u'p:68578'],
 Objects[u'p:68579'],
 Objects[u'p:68766'],
 Objects[u'p:68989'],
 Objects[u'p:69376'],
 Objects[u'p:69741'],
 Objects[u'p:69947'],
 Objects[u'p:71109'],
 Objects[u'p:71126'],
 Objects[u'p:71697'],
 Objects[u'p:71758'],
 Objects[u'p:72089'],
 Objects[u'p:73139'],
 Objects[u'p:73581'],
 Objects[u'p:73645'],
 Objects[u'p:73760'],
 Objects[u'p:73846'],
 Objects[u'p:74039'],
 Objects[u'p:74484'],
 Objects[u'p:75269'],
 Objects[u'p:75428'],
 Objects[u'p:75587'],
 Objects[u'p:78096'],
 Objects[u'p:79907'],
 Objects[u'p:8009'],
 Objects[u'p:80288'],
 Objects[u'p:80849'],
 Objects[u'p:83622'],
 Objects[u'p:83727'],
 Objects[u'p:83806'],
 Objects[u'p:84204'],
 Objects[u'p:84324'],
 Objects[u'p:84461'],
 Objects[u'p:85083'],
 Objects[u'p:86126'],
 Objects[u'p:86341'],
 Objects[u'p:86740'],
 Objects[u'p:86741'],
 Objects[u'p:87352'],
 Objects[u'p:87786'],
 Objects[u'p:87787'],
 Objects[u'p:89621'],
 Objects[u'p:91550'],
 Objects[u'p:93718'],
 Objects[u'p:94084'],
 Objects[u'p:98370'],
 Objects[u'p:99520'],
 Objects[u'r:102'],
 Objects[u'r:1067'],
 Objects[u'r:10933'],
 Objects[u'r:1157'],
 Objects[u'r:1193'],
 Objects[u'r:12063'],
 Objects[u'r:12064'],
 Objects[u'r:1209'],
 Objects[u'r:12703'],
 Objects[u'r:1374'],
 Objects[u'r:15036'],
 Objects[u'r:1606'],
 Objects[u'r:1612'],
 Objects[u'r:16761'],
 Objects[u'r:17764'],
 Objects[u'r:1847'],
 Objects[u'r:18680'],
 Objects[u'r:19309'],
 Objects[u'r:19310'],
 Objects[u'r:19810'],
 Objects[u'r:19943'],
 Objects[u'r:20013'],
 Objects[u'r:20023'],
 Objects[u'r:20536'],
 Objects[u'r:20537'],
 Objects[u'r:20577'],
 Objects[u'r:20578'],
 Objects[u'r:20726'],
 Objects[u'r:21155'],
 Objects[u'r:21214'],
 Objects[u'r:21215'],
 Objects[u'r:214'],
 Objects[u'r:22179'],
 Objects[u'r:22256'],
 Objects[u'r:22679'],
 Objects[u'r:22964'],
 Objects[u'r:2310'],
 Objects[u'r:23231'],
 Objects[u'r:2390'],
 Objects[u'r:24129'],
 Objects[u'r:24660'],
 Objects[u'r:2494'],
 Objects[u'r:2683'],
 Objects[u'r:28000'],
 Objects[u'r:29047'],
 Objects[u'r:29048'],
 Objects[u'r:29101'],
 Objects[u'r:29420'],
 Objects[u'r:29865'],
 Objects[u'r:30655'],
 Objects[u'r:30656'],
 Objects[u'r:30657'],
 Objects[u'r:3281'],
 Objects[u'r:33447'],
 Objects[u'r:34848'],
 Objects[u'r:34850'],
 Objects[u'r:34954'],
 Objects[u'r:3599'],
 Objects[u'r:3600'],
 Objects[u'r:36333'],
 Objects[u'r:373'],
 Objects[u'r:3895'],
 Objects[u'r:390'],
 Objects[u'r:4732'],
 Objects[u'r:4835'],
 Objects[u'r:4836'],
 Objects[u'r:5221'],
 Objects[u'r:5687'],
 Objects[u'r:6012'],
 Objects[u'r:6386'],
 Objects[u'r:673'],
 Objects[u'r:721'],
 Objects[u'r:7732'],
 Objects[u'r:8138'],
 Objects[u'r:8655'],
 Objects[u'r:8656'],
 Objects[u'r:9568']]

In [17]:

    

Objects[u'c:12337'].to_dict()


    

    
Out[17]:





{'category_code': u'web',
 'city': None,
 'closed_at': datetime.date(2008, 1, 1),
 'country_code': None,
 'created_at': datetime.datetime(2008, 10, 15, 20, 8, 46),
 'created_by': u'gene',
 'description': None,
 'domain': u'apps.facebook.com::party_buzz',
 'entity_id': 12337,
 'entity_type': u'Company',
 'first_funding_at': None,
 'first_investment_at': None,
 'first_milestone_at': datetime.date(2008, 11, 11),
 'founded_at': None,
 'funding_rounds': None,
 'funding_total_usd': None,
 'homepage_url': u'http://apps.facebook.com/party_buzz',
 'id': u'c:12337',
 'invested_companies': None,
 'investment_rounds': None,
 'last_funding_at': None,
 'last_investment_at': None,
 'last_milestone_at': datetime.date(2008, 11, 11),
 'logo_height': 249,
 'logo_url': u'http://s3.amazonaws.com/crunchbase_prod_assets/assets/images/resized/0002/6815/26815v2-max-250x250.jpg',
 'logo_width': 399,
 'milestones': 1,
 'name': u'Party Buzz',
 'normalized_name': u'party buzz',
 'parent_id': None,
 'permalink': u'/company/party-buzz',
 'region': u'unknown',
 'relationships': None,
 'short_description': None,
 'state_code': None,
 'tag_list': u'facebook-app, party-finder',
 'twitter_username': None,
 'updated_at': datetime.datetime(2013, 8, 28, 21, 39, 1)}

In [18]:

    

# Zuckerberg


    

In [19]:

    

a[:15]


    

    
Out[19]:





[Degrees[1],
 Degrees[2],
 Degrees[3],
 Degrees[4],
 Degrees[5],
 Degrees[6],
 Degrees[7],
 Degrees[8],
 Degrees[9],
 Degrees[10],
 Degrees[11],
 Degrees[12],
 Degrees[13],
 Degrees[14],
 Degrees[15]]

In [21]:

    

show(a[:][5])


    

    




---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-21-fbb39c43997a> in <module>()
----> 1 show(a[:][5])

/Users/ZihanZhou/anaconda/lib/python2.7/site-packages/pony/orm/core.pyc in __getitem__(query, key)

/Users/ZihanZhou/anaconda/lib/python2.7/site-packages/pony/utils.pyc in cut_traceback(func, *args, **kwargs)
     79             if last_pony_tb is None: raise
     80             if tb.tb_frame.f_globals.get('__name__') == 'pony.utils' and tb.tb_frame.f_code.co_name == 'throw':
---> 81                 reraise(exc_type, exc, last_pony_tb)
     82             raise exc  # Set "pony.options.CUT_TRACEBACK = False" to see full traceback
     83         finally:

/Users/ZihanZhou/anaconda/lib/python2.7/site-packages/pony/orm/core.pyc in validate(attr, val, obj, entity, from_db)
   2185         val = Attribute.validate(attr, val, obj, entity, from_db)
   2186         if val == '' or (val is None and not (attr.auto or attr.is_volatile or attr.sql_default)):
-> 2187             throw(ValueError, 'Attribute %s is required' % (attr if obj is None else '%r.%s' % (obj, attr.name)))
   2188         return val
   2189 

/Users/ZihanZhou/anaconda/lib/python2.7/site-packages/pony/utils.pyc in throw(exc_type, *args, **kwargs)
    103             raise exc
    104         else:
--> 105             raise exc  # Set "pony.options.CUT_TRACEBACK = False" to see full traceback
    106     finally: del exc
    107 

ValueError: Attribute Degrees.degree_type is required

In [18]:

    

inp ="""
  `id` bigint(20) NOT NULL,
  `relationship_id` bigint(20) NOT NULL,
  `person_object_id` varchar(64) NOT NULL,
  `relationship_object_id` varchar(64) NOT NULL,
  `start_at` date DEFAULT NULL,
  `end_at` date DEFAULT NULL,
  `is_past` tinyint(4) DEFAULT NULL,
  `sequence` int(11) DEFAULT '0',
  `title` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
"""

#   PRIMARY KEY (`id`),
#   UNIQUE KEY `entity` (`entity_type`,`entity_id`),
#   KEY `permalink` (`permalink`),
#   KEY `name` (`name`),
#   KEY `normalized_name` (`normalized_name`),
#   KEY `domain` (`domain`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


    

In [13]:

    

import StringIO


    

In [51]:

    

table = StringIO.StringIO(inp)


    

In [52]:

    

table.write("123dddddddddddddddddd")


    

In [40]:

    




    

    
Out[40]:





['`id`', 'varchar(64)', 'NOT', 'NULL,']

In [19]:

    

import StringIO
def conv2fun(sql_str):
    inp = StringIO.StringIO(sql_str)
    out = StringIO.StringIO()
    for line in inp:
#         print line
        if "NULL" in line:
            tem = line.strip().split(" ")
            attr = tem[0][1:-1]
            typ = "ERR_TYP"
            op_re = "ERR_OP_RE"
            
            if "var" in tem[1]:
                typ = "str"
            elif "int" in tem[1]:
                typ ="int"
            else:
                typ = tem[1]
            if "DEFAULT" in line:
                op_re="Optional"
            else:
                op_re="Required"
            out.write("{} = {}({})\n".format(attr,op_re,typ))
    return out


    

In [20]:

    

a=conv2fun(inp)


    

In [21]:

    

print a.getvalue()


    

    




id = Required(int)
relationship_id = Required(int)
person_object_id = Required(str)
relationship_object_id = Required(str)
start_at = Optional(date)
end_at = Optional(date)
is_past = Optional(int)
title = Optional(str)
created_at = Optional(datetime)
updated_at = Optional(datetime)

In [ ]: