def wordTable(self): if not "wordtable" in self.db: self.db.create("wordtable",fields=( pk(), # Auto-incremental id. field("mapid",INTEGER,index=UNIQUE), field("freq",INTEGER), field("word",TEXT) ))
def __init__(self, database): db.View.__init__(self, database, "products", schema=[ db.pk(), db.field("name", db.STRING), db.field("price", db.FLOAT) ]) self.setup() self.table.insert(name="pizza", price=15.0)
def test_create_table(self): # Assert Database.create() new table. v = self.db.create("products", fields=[ db.primary_key("pid"), db.field("name", db.STRING, index=True, optional=False), db.field("price", db.FLOAT) ]) # Assert that the last query executed is stored. if self.db.type == db.SQLITE: self.assertEqual(self.db.query, "pragma table_info(`products`);") if self.db.type == db.MYSQL: self.assertEqual(self.db.query, "show columns from `products`;") # Assert new Table exists in Database.tables. self.assertTrue(isinstance(v, db.Table)) self.assertTrue(len(self.db) == 1) self.assertTrue(v.pk == "pid") self.assertTrue(v.fields == ["pid", "name", "price"]) self.assertTrue(self.db[v.name] == v) self.assertTrue(self.db.tables[v.name] == v) self.assertTrue(getattr(self.db, v.name) == v) # Assert Database._field_SQL subroutine for Database.create(). for field, sql1, sql2 in ( (db.primary_key("pid"), ("`pid` integer not null primary key auto_increment", None), ("`pid` integer not null primary key autoincrement", None)), (db.field("name", db.STRING, index=True, optional=False), ("`name` varchar(100) not null", "create index `products_name` on `products` (`name`);"), ("`name` varchar(100) not null", "create index `products_name` on `products` (`name`);")), (db.field("price", db.INTEGER), ("`price` integer null", None), ("`price` integer null", None))): if self.db.type == db.MYSQL: self.assertEqual( self.db._field_SQL(self.db["products"].name, field), sql1) if self.db.type == db.SQLITE: self.assertEqual( self.db._field_SQL(self.db["products"].name, field), sql2) # Assert TableError if table already exists. self.assertRaises(db.TableError, self.db.create, "products") # Assert remove table. self.db.drop("products") self.assertTrue(len(self.db) == 0) print "pattern.db.Database.create()"
def test_field(self): # Assert field() return value with different optional parameters. # NAME TYPE DEFAULT INDEX OPTIONAL for kwargs, f in ( (dict(name="id", type=db.INT), ("id", "integer", None, False, True)), (dict(name="id", type=db.INT, index=db.PRIMARY), ("id", "integer", None, "primary", True)), (dict(name="id", type=db.INT, index=db.UNIQUE), ("id", "integer", None, "unique", True)), (dict(name="id", type=db.INT, index="0"), ("id", "integer", None, False, True)), (dict(name="id", type=db.INT, index="1"), ("id", "integer", None, True, True)), (dict(name="id", type=db.INT, index=True), ("id", "integer", None, True, True)), (dict(name="id", type=db.INT, default=0), ("id", "integer", 0, False, True)), (dict(name="name", type=db.STRING), ("name", "varchar(100)", None, False, True)), (dict(name="name", type=db.STRING, optional=False), ("name", "varchar(100)", None, False, False)), (dict(name="name", type=db.STRING, optional="0"), ("name", "varchar(100)", None, False, False)), (dict(name="name", type=db.STRING(50)), ("name", "varchar(50)", None, False, True)), (dict(name="price", type=db.FLOAT, default=0), ("price", "real", 0, False, True)), (dict(name="show", type=db.BOOL), ("show", "tinyint(1)", None, False, True)), (dict(name="show", type=db.BOOL, default=True), ("show", "tinyint(1)", True, False, True)), (dict(name="show", type=db.BOOL, default=False), ("show", "tinyint(1)", False, False, True)), (dict(name="date", type=db.DATE), ("date", "timestamp", "now", False, True)), (dict(name="date", type=db.DATE, default=db.NOW), ("date", "timestamp", "now", False, True)), (dict(name="date", type=db.DATE, default="1999-12-31 23:59:59"), ("date", "timestamp", "1999-12-31 23:59:59", False, True))): self.assertEqual(db.field(**kwargs), f) # Assert primary_key() return value. self.assertTrue(db.primary_key() == db.pk() == ("id", "integer", None, "primary", False)) print "pattern.db.field()"
def setUp(self): # Define self.db in a subclass. # Create test tables. self.db.create("persons", fields=[ db.primary_key("id"), db.field("name", db.STRING) ]) self.db.create("products", fields=[ db.primary_key("id"), db.field("name", db.STRING), db.field("price", db.FLOAT, default=0.0) ]) self.db.create("orders", fields=[ db.primary_key("id"), db.field("person", db.INTEGER, index=True), db.field("product", db.INTEGER, index=True), ])
def test_create_table(self): # Assert Database.create() new table. v = self.db.create("products", fields=[ db.primary_key("pid"), db.field("name", db.STRING, index=True, optional=False), db.field("price", db.FLOAT) ]) # Assert that the last query executed is stored. if self.db.type == db.SQLITE: self.assertEqual(self.db.query, "pragma table_info(`products`);") if self.db.type == db.MYSQL: self.assertEqual(self.db.query, "show columns from `products`;") # Assert new Table exists in Database.tables. self.assertTrue(isinstance(v, db.Table)) self.assertTrue(len(self.db) == 1) self.assertTrue(v.pk == "pid") self.assertTrue(v.fields == ["pid", "name", "price"]) self.assertTrue(self.db[v.name] == v) self.assertTrue(self.db.tables[v.name] == v) self.assertTrue(getattr(self.db, v.name) == v) # Assert Database._field_SQL subroutine for Database.create(). for field, sql1, sql2 in ( (db.primary_key("pid"), ("`pid` integer not null primary key auto_increment", None), ("`pid` integer not null primary key autoincrement", None)), (db.field("name", db.STRING, index=True, optional=False), ("`name` varchar(100) not null", "create index `products_name` on `products` (`name`);"), ("`name` varchar(100) not null", "create index `products_name` on `products` (`name`);")), (db.field("price", db.INTEGER), ("`price` integer null", None), ("`price` integer null", None))): if self.db.type == db.MYSQL: self.assertEqual( self.db._field_SQL(self.db["products"].name, field), sql1) if self.db.type == db.SQLITE: self.assertEqual( self.db._field_SQL(self.db["products"].name, field), sql2) # Assert TableError if table already exists. self.assertRaises(db.TableError, self.db.create, "products") # Assert remove table. self.db.drop("products") self.assertTrue(len(self.db) == 0) print("pattern.db.Database.create()")
def setUp(self): # Define self.db in a subclass. # Create test tables. self.db.create("persons", fields=[ db.primary_key("id"), db.field("name", db.STRING), db.field("age", db.INTEGER), db.field("gender", db.INTEGER) ]) self.db.create("gender", fields=[ db.primary_key("id"), db.field("name", db.STRING) ]) # Create test data. self.db.persons.insert(name="john", age="30", gender=2) self.db.persons.insert(name="jack", age="20", gender=2) self.db.persons.insert(name="jane", age="30", gender=1) self.db.gender.insert(name="female") self.db.gender.insert(name="male")
def originalTable(self): if not "originaltable" in self.db: self.db.create("originaltable",fields=( pk(), # Auto-incremental id. field("sid",STRING(20),index=UNIQUE), field("uid",STRING(20)), field("createdAt",DATE), field("status",TEXT), field("mention",STRING(50)), field("link",STRING(50)), field("totalrw",INTEGER), field("rwnum",INTEGER) ))
def retweetTable(self): if not "retwtable" in self.db: self.db.create("retwtable",fields=( pk(), # Auto-incremental id. field("sid",STRING(20),index=UNIQUE), field("origin_sid",STRING(20)), field("uid",STRING(20)), field("createdAt",DATE), field("status",TEXT), field("mention",STRING(50)), field("rwfrom",STRING(50)), field("link",STRING(50)) ))
def setUp(self): # Delete all tables first for table in list(self.db): self.db.drop(table) # Create test tables. self.db.create("persons", fields=[ db.primary_key("id"), db.field("name", db.STRING) ]) self.db.create("products", fields=[ db.primary_key("id"), db.field("name", db.STRING), db.field("price", db.FLOAT, default=0.0) ]) self.db.create("orders", fields=[ db.primary_key("id"), db.field("person", db.INTEGER, index=True), db.field("product", db.INTEGER, index=True), ])
def createTables(self): errMsg = ',ommitting...' try: self.db.create('websites', fields=(pk('id'), field('address', index=UNIQUE), field('domain_id'), field('connected', BOOLEAN, default=True), field('lastVisited', DATE, default=NOW))) except TableError as err: print err, errMsg try: self.db.create('links', fields=(pk('id'), field('website', INTEGER, optional=False), field('reference', INTEGER, optional=False))) except TableError as err: print err, errMsg try: self.db.create('domains', fields=(pk('id'), field('name', STRING(80), index=UNIQUE))) except TableError as err: print err, errMsg try: self.db.create('session', fields=(pk('id'), field('website_id', INTEGER, optional=False, index=UNIQUE), field('depth', INTEGER, optional=False))) except TableError as err: print err, errMsg self.db.link(self.db.domains, 'domains.id', self.db.websites, 'websites.domain_id') self.db.link(self.db.session, 'session.website_id', self.db.websites, 'websites.id')
def setUp(self): # Delete all tables first for table in list(self.db): self.db.drop(table) # Create test tables. self.db.create("persons", fields=[ db.primary_key("id"), db.field("name", db.STRING), db.field("age", db.INTEGER), db.field("gender", db.INTEGER) ]) self.db.create("gender", fields=[ db.primary_key("id"), db.field("name", db.STRING) ]) # Create test data. self.db.persons.insert(name="john", age="30", gender=2) self.db.persons.insert(name="jack", age="20", gender=2) self.db.persons.insert(name="jane", age="30", gender=1) self.db.gender.insert(name="female") self.db.gender.insert(name="male")
def get_database(db_name, clean=False): if clean: if os.path.exists(db_name): os.remove(db_name) if os.path.exists(db_name + '-journal'): os.remove(db_name + '-journal') db = Database(db_name) if 'room' not in db.tables: db.create('room', fields=[ pk(), field('type', STRING(10)), field('email', STRING(100)), field('contact_name', STRING(100)), field('link', STRING(200)), field('rent', STRING(50)), field('available', STRING(100)), ]) return db
def userTable(self): if not "usertable" in self.db: self.db.create("usertable",fields=( pk(), # Auto-incremental id. field("mapid",INTEGER),#用户映射id field("userid",STRING(20),index=UNIQUE),#用户UID field("name",STRING(50)),#友好显示名称 field("province",STRING(20)),#用户所在省级ID field("city",STRING(20)),# int 用户所在城市ID field("location",STRING(50)),# string 用户所在地 field("description",TEXT),# string 用户个人描述 field("gender",STRING(10)),# string 性别,m:男、f:女、n:未知 field("followersCount",INTEGER),# int 粉丝数 field("friendsCount",INTEGER),# int 关注数 field("statusesCount",INTEGER),# int 微博数 field("createdAt",DATE),# string 用户创建(注册)时间 field("verified",STRING(10)),# STRING(10) 是否是微博认证用户,即加V用户,true:是,false:否 field("verifiedType",STRING(20)),# int 暂未支持 field("biFollowersCount",INTEGER)# int 用户的互粉数 ))
def relationTable(self): if not "relationtable" in self.db: self.db.create("relationtable",fields=( pk(), # Auto-incremental id. field("user_v1",INTEGER),#用户1UID field("user_v2",INTEGER),#用户2UID field("re_0",INTEGER,default=-1),#初始关系 field("re_1",INTEGER,default=-1),#关系变化 field("re_2",INTEGER,default=-1),#关系变化 field("re_3",INTEGER,default=-1),#关系变化 field("re_4",INTEGER,default=-1),#关系变化 field("re_5",INTEGER,default=-1),#关系变化 field("re_6",INTEGER,default=-1),#关系变化 field("re_7",INTEGER,default=-1),#关系变化 field("re_8",INTEGER,default=-1),#关系变化 field("re_9",INTEGER,default=-1),#关系变化 field("re_10",INTEGER,default=-1),#关系变化 field("re_11",INTEGER,default=-1),#关系变化 field("re_12",INTEGER,default=-1),#关系变化 field("re_13",INTEGER,default=-1),#关系变化 field("re_14",INTEGER,default=-1),#关系变化 field("re_15",INTEGER,default=-1),#关系变化 field("re_16",INTEGER,default=-1),#关系变化 field("re_17",INTEGER,default=-1),#关系变化 field("re_18",INTEGER,default=-1),#关系变化 field("re_19",INTEGER,default=-1),#关系变化 field("re_20",INTEGER,default=-1),#关系变化 field("re_21",INTEGER,default=-1),#关系变化 field("re_22",INTEGER,default=-1),#关系变化 field("re_23",INTEGER,default=-1),#关系变化 field("re_24",INTEGER,default=-1),#关系变化 field("re_25",INTEGER,default=-1),#关系变化 field("re_26",INTEGER,default=-1),#关系变化 field("re_27",INTEGER,default=-1),#关系变化 field("re_28",INTEGER,default=-1),#关系变化 field("re_29",INTEGER,default=-1),#关系变化 field("re_30",INTEGER,default=-1),#关系变化 field("re_31",INTEGER,default=-1),#关系变化 field("re_32",INTEGER,default=-1)#关系变化 ))
def test_fields(self): # Assert ALTER TABLE when column is inserted. v = self.db.products v.fields.append(db.field("description", db.TEXT)) self.assertEqual(v.fields, ["id", "name", "price", "description"]) print "pattern.db.Table.fields"
# coding: utf-8 from pattern.web import Twitter from pattern.db import Database, SQLITE from pattern.db import pd from pattern.db import field, pk, INTEGER, UNIQUE, STRING from sqlite3 import IntegrityError team = ['#galo', '#Galo', '#Atletico-MG', '#atletico mineiro'] twitter = Twitter() db = Database(pd('tweets.db')) if not "tweets" in db: db.create("tweets", fields = (pk(), field('code', INTEGER, UNIQUE), field('text', STRING(140)))) #query in Twitter for hashtag in team: for tweet in twitter.search(hashtag): try: db.tweets.append(code = tweet.id, text = tweet.text) except IntegrityError: pass #Separate tweets in database for data in db.tweets.filter(): print data[2] print '-'*30
# MySQL databases require a username and a password. # MySQL also requires that you install MySQLdb, see the installation instructions at: # http://www.clips.ua.ac.be/pages/pattern-db db = Database(pd("store.db"), type=SQLITE) #db._delete() # PRODUCTS # Create the products table if it doesn't exist yet. # An error will be raised if the table already exists. # Add sample data. if not "products" in db: # Note: in SQLite, the STRING type is mapped to TEXT (unlimited length). # In MySQL, the length matters. Smaller fields have faster lookup. schema = ( pk(), # Auto-incremental id. field("description", STRING(50)), field("price", INTEGER) ) db.create("products", schema) db.products.append(description="pizza", price=15) db.products.append(description="garlic bread", price=3) #db.products.append({"description": "garlic bread", "price": 3}) # CUSTOMERS # Create the customers table and add data. if not "customers" in db: schema = ( pk(), field("name", STRING(50)), field("address", STRING(200)) )
# http://www.clips.ua.ac.be/pages/pattern-db db = Database("store.db", type=SQLITE) #db._delete() # PRODUCTS # Create the products table if it doesn't exist yet. # An error will be raised if the table already exists. # Add sample data. if not "products" in db: # Note: in SQLite, the STRING type is mapped to TEXT (unlimited length). # In MySQL, the length matters. Smaller fields have faster lookup. db.create( "products", fields=( pk(), # Auto-incremental id. field("description", STRING(50)), field("price", INTEGER))) db.products.append(description="pizza", price=15) db.products.append(description="garlic bread", price=3) # CUSTOMERS # Create the customers table and add data. if not "customers" in db: db.create("customers", fields=(pk(), field("name", STRING(50)), field("address", STRING(200)))) db.customers.append(name=u"Schrödinger") # Unicode is supported. db.customers.append(name=u"Hofstadter") # ORDERS # Create the orders table if it doesn't exist yet and add data.