def test_escape(self):
# Assert str, unicode, int, long, float, bool and None field values.
for v, s in (
( "a", "'a'"),
( 1, "1"),
(int(1), "1"),
( 1.0, "1.0"),
( True, "1"),
( False, "0"),
( None, "null")):
self.assertEqual(db._escape(v), s)
# Assert date.
v = db.date("1707-04-15")
self.assertEqual(db._escape(v), "'1707-04-15 00:00:00'")
# Assert current date.
v = "current_timestamp"
self.assertEqual(db._escape(v), "current_timestamp")
# Assert subquery.
v = self.db.create("dummy", fields=[db.pk()])
v = v.query()
self.assertEqual(db._escape(v), "(select dummy.* from `dummy`)")
# Assert MySQL and SQLite quotes.
if self.db.type == db.MYSQL:
self.assertEqual(self.db.escape("'"), "'\\''")
if self.db.type == db.SQLITE:
self.assertEqual(self.db.escape("'"), "''''")
print("pattern.db._escape()")
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 test_escape(self):
# Assert str, unicode, int, long, float, bool and None field values.
for v, s in (
( "a", "'a'"),
( 1, "1"),
(int(1), "1"),
( 1.0, "1.0"),
( True, "1"),
( False, "0"),
( None, "null")):
self.assertEqual(db._escape(v), s)
# Assert date.
v = db.date("1707-04-15")
self.assertEqual(db._escape(v), "'1707-04-15 00:00:00'")
# Assert current date.
v = "current_timestamp"
self.assertEqual(db._escape(v), "current_timestamp")
# Assert subquery.
v = self.db.create("dummy", fields=[db.pk()])
v = v.query()
self.assertEqual(db._escape(v), "(select dummy.* from `dummy`)")
# Assert MySQL and SQLite quotes.
if self.db.type == db.MYSQL:
self.assertEqual(self.db.escape("'"), "'\\''")
if self.db.type == db.SQLITE:
self.assertEqual(self.db.escape("'"), "''''")
print("pattern.db._escape()")
class TestDatabase(unittest.TestCase):
def setUp(self):
# Define self.db and self.type in a subclass.
pass
def tearDown(self):
for table in self.db:
self.db.drop(table)
def test_escape(self):
# Assert str, unicode, int, long, float, bool and None field values.
for v, s in (("a", "'a'"), (u"a", "'a'"), (1, "1"), (1L, "1"),
(1.0, "1.0"), (True, "1"), (False, "0"), (None, "null")):
self.assertEqual(db._escape(v), s)
# Assert date.
v = db.date("1707-04-15")
self.assertEqual(db._escape(v), "'1707-04-15 00:00:00'")
# Assert current date.
v = "current_timestamp"
self.assertEqual(db._escape(v), "current_timestamp")
# Assert subquery.
v = self.db.create("dummy", fields=[db.pk()])
v = v.query()
self.assertEqual(db._escape(v), "(select dummy.* from `dummy`)")
# Assert MySQL and SQLite quotes.
if self.db.type == db.MYSQL:
self.assertEqual(self.db.escape("'"), "'\\''")
if self.db.type == db.SQLITE:
self.assertEqual(self.db.escape("'"), "''''")
print "pattern.db._escape()"
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 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 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 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 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 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)#关系变化
))
# MySQL also requires that you install MySQLdb, see the installation instructions at:
# 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
# SQLite will create the database file in the current folder.
# 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))
# SQLite will create the database file in the current folder.
# 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("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)))
db.create("customers", schema)
db.customers.append(name=u"Schrödinger") # Unicode is supported.
db.customers.append(name=u"Hofstadter")
# 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
