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 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 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.
