def findone(cls, conditions='where 1=1 ', *args):
db.create_engine('root', 'root', 'dbwl')
sql = 'select * from %s %s ' % (cls.__table__, conditions)
rs = db.select(sql, *args)
if rs.__len__() != 0:
rs = rs[0]
return rs
def save(self):
fields = []
params = []
args = []
for k, v in self.__mappings__.iteritems():
fields.append(v.name)
params.append('?')
args.append(getattr(self, k, None))
sql = 'insert into %s(%s) values(%s)' % (self.__table__, ','.join(fields), ','.join(params))
print sql
print 'args: %s' % ','.join(args)
db.create_engine('root', 'root', 'dbwl')
return db.update(sql, *args)
def wrapper(*args, **kw):
global engine
if engine == None:
engine = db.create_engine('needu', 'Needu.app', 'NEEDU_DB', '192.168.1.200', '8003')
return func(*args, **kw)
def __init__(self, connect_path, *args, **kwargs):
self._session = connect(create_engine(connect_path))
self._metric_names = kwargs.get('metric_names', ['planning_time'])
self._method_names = kwargs.get('method_names', ['mean', 'median'])
self._tasks = []
self._loaded_tasks = []
self._metrics_store = {}
def test_database_operations(self):
#initialization of engine
db.create_engine('test.db')
#create Table
db.update('drop table if exists User')
db.update('create table User(id int primary key, name varchar(20),password varchar(20),gender varchar(8))')
#insert
r1 = db.insert('User',id=db.get_id(),name='user1',password='******',gender='male')
r2 = db.insert('User',id=db.get_id(),name='user2',password='******',gender='female')
r3 = db.insert('User',id=db.get_id(),name='user3',password='******',gender='male')
self.assertEquals(r1,1)
self.assertEquals(r2,1)
self.assertEquals(r3,1)
#test select
r4 = db.select_one('select name from User where gender=?','male')
r5 = db.select_all('select name from User where gender=?','male')
self.assertIsInstance(r4,dict)
self.assertIsInstance(r5,list)
r6 = db.select_one('select name from User where gender=?','asldfkj')
r7 = db.select_all('select name from User where gender=?','asldfkj')
self.assertIsNone(r6)
self.assertEquals(r7,[])
#test update
r8 = db.update('update User SET gender=? where name=?','male','user1')
r9 = db.update('update User SET gender=? where name=?','male','asdfas')
r10 = db.update('update User SET name =? where gender=?','haha','male')
self.assertEquals(r8,1)
self.assertEquals(r9,0)
self.assertEquals(r10,2)
#test transactions
with db.transaction():
db.insert('User',id=db.get_id(),name='user5',password='******',gender='female')
db.insert('User',id=db.get_id(),name='user5',password='******',gender='male')
r12 = db.select_all('select * from User where name=?','user5')
self.assertEquals(len(r12),2)
db.engine = None
def test_engine(self):
#check the initial value of engine
self.assertIsNone(db.engine)
#initial engine
db.create_engine('test.db')
with self.assertRaises(db.DBError):
db.create_engine('test.db')
#test initialization of _db_ctx object
self.assertFalse(db._db_ctx.is_init())
db._db_ctx.init()
self.assertTrue(db._db_ctx.is_init())
#test cursor
self.assertIsNotNone(db._db_ctx.cursor())
#test cleanup
db._db_ctx.cleanup()
self.assertFalse(db._db_ctx.is_init())
def test_set_pragma(self):
db = create_engine('sqlite://')
db.connect()
is_listening = sqlalchemy.event.contains(db, 'connect', set_sqlite_pragma)
if dbapi2.sqlite_version_info > (3, 7, 0):
self.assertTrue(is_listening)
else:
self.expectFailure("turn on pragma with sqlite version <= 3.7.0",
self.assertTrue, is_listening)
def test_set_pragma(self):
db = create_engine('sqlite://')
db.connect()
is_listening = sqlalchemy.event.contains(db, 'connect',
set_sqlite_pragma)
if dbapi2.sqlite_version_info > (3, 7, 0):
self.assertTrue(is_listening)
else:
self.expectFailure("turn on pragma with sqlite version <= 3.7.0",
self.assertTrue, is_listening)
def before(self):
super(TestAnswer, self).before()
self.db = create_engine('sqlite://')
metadata.create_all(self.db)
self.resource = Answer(self.db)
self.api.add_route('/answer/{game_id}/{question_id}', self.resource)
self.api.req_options.auto_parse_form_urlencoded = True
# test data
self.test_run_id = self.getUniqueString('run_id')
self.test_score = self.getUniqueInteger()
self.test_uid = self.getUniqueInteger()
self.test_selected = ','.join([str(self.getUniqueInteger()) for i in range(3)])
def get_import_citizens(import_id):
with create_engine().connect() as conn:
with conn.begin():
s = citizen_table.select().where(
citizen_table.c.import_id == import_id)
rows = conn.execute(s)
result = []
for citizen in rows:
result.append(
citizen_record_obj_to_dict(conn, import_id, citizen))
return Response(json.dumps({'data': result}),
status=200,
mimetype='application/json')
def before(self):
super(TestAnswer, self).before()
self.db = create_engine('sqlite://')
metadata.create_all(self.db)
self.resource = Answer(self.db)
self.api.add_route('/answer/{game_id}/{question_id}', self.resource)
self.api.req_options.auto_parse_form_urlencoded = True
# test data
self.test_run_id = self.getUniqueString('run_id')
self.test_score = self.getUniqueInteger()
self.test_uid = self.getUniqueInteger()
self.test_selected = ','.join(
[str(self.getUniqueInteger()) for i in range(3)])
def main():
app = Flask(__name__, static_url_path='/static')
app.config.from_object(os.environ['APP_SETTINGS'])
engine = db.create_engine(app.config)
@app.route('/', methods=('GET', 'POST'))
def home():
return render_template('search.html')
@app.route('/search', methods=['POST'])
def search():
results = do_the_search()
return render_template('search.html', {'results': results})
app.run(host='0.0.0.0', port=PORT, debug=True)
def patch_citizen(import_id, citizen_id):
data = request.get_json(force=True)
citizen_diff, error = validate_patch_citizens(data, citizen_id)
if error:
print(error)
raise BadRequest(error)
with create_engine().connect() as conn:
with conn.begin():
citizen_record = get_citizen_record(conn, import_id, citizen_id)
if citizen_record is None:
raise BadRequest(
'{}, {} import_id, citizen_id pair is incorrect'.format(
import_id, citizen_id))
relatives_new_list = citizen_diff.pop('relatives', None)
for field, field_value in list(citizen_diff.items()):
if citizen_record[field] == field_value:
del citizen_diff[field]
if citizen_diff:
update_q = citizen_table.update().where(
and_(citizen_table.c.import_id == import_id,
citizen_table.c.citizen_id == citizen_id)).values(
**citizen_diff)
conn.execute(update_q)
if relatives_new_list is not None:
update_relatives(conn, import_id, citizen_id,
relatives_new_list)
citizen_new_obj = conn.execute(citizen_table.select().where(
and_(citizen_table.c.import_id == import_id,
citizen_table.c.citizen_id == citizen_id))).fetchone()
return Response(json.dumps({
'data':
citizen_record_obj_to_dict(conn, import_id, citizen_new_obj)
}),
status=200,
mimetype='application/json')
def insert_citizens(all_citizens):
append_to_relatives = itertools.chain.from_iterable(
((citizen['citizen_id'], relative) for relative in citizen['relatives']
if citizen['citizen_id'] < relative) for citizen in all_citizens)
with create_engine().connect() as conn:
with conn.begin():
import_id = conn.execute(
import_id_table.insert()).inserted_primary_key[0]
to_insert_to_relatives = [{
'import_id': import_id,
'citizen_id': citizen,
'relative_id': relative
} for citizen, relative in append_to_relatives]
to_insert_to_citizens = []
for row in all_citizens:
to_insert_to_citizens.append({
'import_id': import_id,
'apartment': row['apartment'],
'birth_date': row['birth_date'],
'building': row['building'],
'citizen_id': row['citizen_id'],
'gender': row['gender'],
'name': row['name'],
'street': row['street'],
'town': row['town'],
})
conn.execute(citizen_table.insert(), *to_insert_to_citizens)
if to_insert_to_relatives:
conn.execute(relative_table.insert(), *to_insert_to_relatives)
return import_id
def init_app(self, app, **kwargs):
self._init_flask(app)
self._init_conf(app, **self._conf_args(**kwargs))
self._init_engine()
if app.secret_key is None:
app.secret_key = self._generate_secret_key()
app.extensions["wok"] = self.engine
db_path = os.path.join(self.engine.work_path, "server.db")
new_db = not os.path.exists(db_path)
engine = db.create_engine(uri="sqlite:///{}".format(db_path))
session = db.Session()
db_init(engine, session, new_db)
session.commit()
session.close()
self._initialized = True
if self._start_engine:
self.start_engine()
def init_app(self, app, **kwargs):
self._init_flask(app)
self._init_conf(app, **self._conf_args(**kwargs))
self._init_engine()
if app.secret_key is None:
app.secret_key = self._generate_secret_key()
app.extensions["wok"] = self.engine
db_path = os.path.join(self.engine.work_path, "server.db")
new_db = not os.path.exists(db_path)
engine = db.create_engine(uri="sqlite:///{}".format(db_path))
session = db.Session()
db_init(engine, session, new_db)
session.commit()
session.close()
self._initialized = True
if self._start_engine:
self.start_engine()
#coding=utf-8
from models import User, Blog, Comment
import db
db.create_engine(user='******', password='******', database='awesome')
u = User(name='Test', email='*****@*****.**', password='******', image='about:blank')
u.insert()
print 'new user id:', u.id
u1 = User.find_first('where email=?', '*****@*****.**')
print 'find user\'s name:', u1.name
u1.delete()
u2 = User.find_first('where email=?', '*****@*****.**')
print 'find user:', u2
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__=='__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'chendong', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
"""
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk),)
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
"""
通过db对象的insert接口执行SQL
SQL: insert into `user` (`passwd`, `last_modified`, `id`, `name`, `email`) values(%s, %s, %s, %s, %s),
ARGS: ('*******', 11111111, 'Michael', '*****@*****.**')
"""
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('lrq', '123456', 'test')
db.update('drop table if exists user')
db.update('create table user(id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
args = []
for k, v in self.__mappings__.iteritems():
if v.updatable:
if hasattr(self, k):
arg = getattr(self, k)
else:
arg = v.default
setattr(self, k, arg)
L.append('`%s`=?' % k)
args.append(arg)
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
if __name__ == "__main__":
logging.basicConfig(level=logging.DEBUG)
db.create_engine(user="******", password="", database='test')
class User(Model):
id = IntegerField(primary_key=True)
name = StringField()
email = StringField(updatable=False)
passwd = StringField(default=lambda: '******')
last_modified = FloatField()
def pre_insert(self):
self.last_modified = time.time()
User.save()
# 创建一个实例：
u = User(id=12345, name='ethan', email='*****@*****.**', password='******')
# 保存到数据库：
u.insert()
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk),
*args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'mysql', 'test')
db.update('drop table if exists user')
db.update(
'create table user (id int primary key, name text, email text, passwd text, last_modified real)'
)
import doctest
doctest.testmod()
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert(self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', '123456', 'awesome')
# db.update('drop table if exists user')
# db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
# import doctest
# doctest.testmod()
class Users(Model):
id = IntegerField(primary_key=True)
name = StringField()
email = StringField(updatable=False)
password = StringField(default=lambda: '123456')
u = Users(id=2, name='Michael', email='*****@*****.**')
r = u.insert()
print r
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk),
*args)
return self
def insert(self):
'''
通过 db 对象的 insert 接口执行 SQL
SQL: insert into `user` (`passwd`,`last_modified`,`id`,`name`,`email`) values (%s,%s,%s,%s,%s),
ARGS: ('******', 1508813773.294855, 300, u'Foo', '*****@*****.**')
'''
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('example', 'example', 'test')
db.update('drop table if exists user')
db.update(
'create table user (id int primary key, name text, email text, passwd text, last_modified real)'
)
import doctest
doctest.testmod()
通过db对象的insert接口执行SQL
SQL: insert into `user` (`passwd`,`last_modified`,`id`,`name`,`email`) values (%s,%s,%s,%s,%s),
ARGS: ('******', 1441878476.202391, 10190, 'Michael', '*****@*****.**')
"""
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root','qwerty','awesome-webapp')
class User(Model):
__table__ = 'users'
id = StringField(primary_key=True,ddl='varchar(50)')
email = StringField(updatable=False,ddl='varchar(50)')
password = StringField(ddl='varchar(50)')
admin = BooleanField()
name = StringField(ddl='varchar(50)')
image = StringField(ddl='varchar(500)')
created_at = FloatField(updatable=False,default=time.time)
import doctest
doctest.testmod()
#!/usr/bin/env python
#-*- coding:utf-8 -*-
import logging
logging.basicConfig(format='%(levelname)s:%(message)s',level='INFO')
from model import User,Blog,Comment
import db
db.create_engine(user='******',password='******',database='myblog')
u = User(name = 'Test',email='*****@*****.**',password='******',image='about:blank')
u.insert()
print 'new user id:',u.id
u1 = User.find_first('where email=?',['*****@*****.**'])
print 'find user\'s name:',u1.name
L.append('`%s`=?'%k)
args.append(arg)
pk=self.__primary_key__.name
args.append(getattr(self,pk))
db.update('update `%s` set %s where %s=?'%(self.__table__,','.join(L),pk),*args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk=self.__primary_key__.name
args=(getattr(self,pk),)
db.update('delete from `%s` where `%s`=?'%(self.__table__,pk),*args)
def insert(self):
self.pre_insert and self.pre_insert()
params={}
for k,v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self,k):
setattr(self,k,v.default)
params[v.name]=getattr(self,k)
db.insert('%s'%self.__table__,**params)
if __name__=='__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('kok','kok','test')
db.update('drop table if exists user')
db.update('create table user(id int primary key,name text,email text,passwd text,last_modified real)')
#import doctest
#doctest.testmod()
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where `%s`=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), getattr(self, pk))
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self,k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level = logging.DEBUG)
db.create_engine('root', '123456', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
args.append(arg)
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__=='__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('phplamp', '1234', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', '123456', 'test') # use your own mysql username and password here
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
#! /usr/bin/env python
# -*- utf-8 -*-
import sys
sys.path.append('transwarp')
from models import User, Blog, Comment
import db
db.create_engine('root', 'admin', 'awesome')
u = User(name='test',admin='1', email='*****@*****.**', password='******', image='about:blank')
u.insert()
print 'new user id:', u.id
u1 = User.find_first('where email=?', '*****@*****.**')
print 'find user\'s name:', u1.name
u1.delete()
u2 = User.find_first('where email=?', '*****@*****.**')
print 'find user:', u2
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('www-data', 'www-data', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
def insert(self):
# 在insert之前进行处理，例如赋值给时间类的字段
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
# 检查字段是否可插入，不可插入则设置默认值
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
# 执行插入
db.insert('%s' % self.__table__, **params)
return self
if __name__=='__main__':
class User(Model):
id = IntegerField(primary_key=True)
name = StringField()
email = StringField(updatable=False)
passwd = StringField(default=lambda:'******')
last_modified = FloatField()
def pre_insert(self):
last_modified = time.time()
db.create_engine('root', 'root', 'crose')
u = User(a=1,id=10193, name='Michaelin', email='*****@*****.**', passwd='12345678')
u.insert()
User.count_all()
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk),)
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', '456987410', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key,name text,email text ,passwd text,last_modifed real)')
import doctest
doctest.testmod()
return self
class User(Model):
id = IntegerField(primary_key=True)
name = StringField()
email = StringField(updatable=False)
passwd = StringField(default=lambda: '******')
last_modified = FloatField()
def pre_insert(self):
self.last_modified = time.time()
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'admin', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
class User(Model):
__table__ = 'user'
id = StringField(primary_key=True, default=db.next_id, ddl='varchar(50)')
email = StringField(updatable=False, ddl='varchar(50)')
passwd = StringField(ddl='varchar(50)')
# admin = BooleanField()
name = StringField(ddl='varchar(50)')
# image = StringField(ddl='varchar(500)')
last_modified = FloatField(default=time.time)
# FloatField(updatable=False, default=time.time)
if __name__ == '__main__':
# print db.next_id()
# print db.next_id
logging.getLogger('').handlers = []
logging.basicConfig(level=logging.DEBUG)
# print "debug"
db.create_engine('root', '355itu11', 'test')
# 创建实例:
user = User(name='Michael22', passwd="passwd1", email="*****@*****.**")
print hasattr(user, "id")
# print user.id
# 存入数据库:
# user.insert()
# db.update('drop table if exists user')
# db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
# import doctest
# doctest.testmod()
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk),
*args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'root', 'test')
db.update('drop table if exists user')
db.update(
'create table user (id int primary key, name text, email text, passwd text, last_modified real)'
)
import doctest
doctest.testmod()
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk),
*args)
return self
def insert(self):
"""
通过db对象的insert接口执行SQL
SQL: insert into `user` (`passwd`,`last_modified`,`id`,`name`,`email`) values (%s,%s,%s,%s,%s),
ARGS: ('******', 1441878476.202391, 10190, 'Michael', '*****@*****.**')
"""
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'root', 'test', '121.42.58.105')
db.update('drop table if exists user')
db.update(
'create table user (id int primary key, name text, email text, passwd text, last_modified real)'
)
import doctest
doctest.testmod()
测试ORM
'''
class radgroupreply(db.Model):
__table__ = 'radgroupreply'
id = db.IntegerField(primary_key=True)
groupname = db.StringField()
attribute = db.StringField()
op = db.StringField()
db.create_engine(user=USER,
password=PASSWORD,
database=DATABASE,
host=HOST,
port=PORT)
r = radgroupreply.find_all()
'''
测试多线程中数据库中数据查询
'''
class dataQueryThread(threading.Thread):
'''
'''
def __init__(self, *args):
super(dataQueryThread, self).__init__()
self.setDaemon(False)
"""
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
"""
通过db对象的insert接口执行SQL
SQL: insert into `user` (`passwd`,`last_modified`,`id`,`name`,`email`) values (%s,%s,%s,%s,%s),
ARGS: ('******', 1441878476.202391, 10190, 'Michael', '*****@*****.**')
"""
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('www-data', 'www-data', 'test', '127.0.0.1')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from %s where %s=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('chenyu', 'cy78102', 'test2')
db.update('drop table if exists testuser')
db.update(
'create table testuser (id int primary key,name text,email text,passwd text,last_modified real)'
)
import doctest
doctest.testmod()
args.append(arg)
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__=='__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root','123456','learningPythonDB','192.168.222.128',3306)
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
args.append(arg)
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__=='__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'xjzh256874', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
import redis
from resources import Start, End, Answer
from db import create_engine
from models import metadata
from configuration import config
# ### 初始化redis客户端
redis_config = config['REDIS']
# 当decode_responses参数为True时redis-py会自动将redis返回的字符菜转换为unicode。
# 默认不这么做的原因是：如果需要转换结果为数字，先转换为unicode就造成了额外花销。
# 节点本身不会从redis读取大量数字，但在归档数据时可能需要注意。
redis_config.update(decode_responses=True)
r = redis.StrictRedis(**redis_config)
# ###初始化数据库
db = create_engine(config['DATABASE_URL'])
# 程序在创建表格之前会检测是否存在
metadata.create_all(db)
"""
### API接口
按falcon的设计模式，每条url规则对应一个[资源]
* /api/start/{game_id} - [开始游戏]
* /api/end/{game_id} - [结束游戏]
* /api/answer/{game_id}/{question_id} - [记录答题选择]
[资源]: resources.html
[开始游戏]: resources.html#Start
[结束游戏]: resources.html#End
[记录答题选择]: resources.html#Answer
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk),
*args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'gongmh', 'test1')
db.update('drop table if exists user')
db.update(
'create table user (id int primary key, name text, email text, passwd text, last_modified real)'
)
import doctest
doctest.testmod()
def setUp(self):
if not db.engine:
db.create_engine("test.db")
db.update("drop table if exists user")
create_table_sql = "".join(User().__sql__.split("\n")[1:])
db.update(create_table_sql)
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import time,logging,db
_triggers = frozenset(['pre_insert','pre_update','pre_delete'])
# sql = """CREATE TABLE EMPLOYEE (
# FIRST_NAME CHAR(20) NOT NULL,
# LAST_NAME CHAR(20),
# AGE INT,
# SEX CHAR(1),
# INCOME FLOAT )"""
def _gen_sql(table_name,mappings):
pk = None
sql = ['-- generating SQL for %s:'% table_name,'create table %s ('% table_name]
for f in sorted(mappings.values(),lambda x,y:cmp(x._order,y._order)):
if not hasattr(f,'ddl'):
raise StandardError('no ddl in field %s '% f)
ddl = f.ddl
nullable = f.nullable
if f.primary_key:
pk = f.name
sql.append('%s %s'(f.name,ddl)if nullable else '%s %s not null,'%(f.name,ddl))
sql.append(' primary key(%s)'% pk)
sql.append(');')
return '\n'.join(sql)
class Field(object):
_count = 0
def __init__(self,**kw):
self.name = kw.get('name',None)
self._default = kw.get('default',None)
self.primary_key = kw.get('primary_key',False)
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__=='__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'Password', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
return delete_row
if __name__ == '__main__':
class Course(Model):
"""
主要xxxField的name属性为建表的属性,不然出错
"""
cid = StringField(primary_key=True)
cname = StringField()
chours = IntegerField()
credit = FloatField()
precid = StringField()
db.create_engine('root', 'password', 'university')
course = {'cid': 'a1'}
print Course.get(**course)
#with db.connection():
#db.execute_sql('create table Course(cid char(20) primary key,cname char(20),precid char(20))')
#db.execute_sql('create table Student(sid int primary key, sname char(20) not null,sex ENUM("m","w"),birthday date,department char(20))')
#db.execute_sql('create table Employ(sid int not null,cid char(20) not null,garde int,primary key(sid, cid),foreign key(sid) references Student(sid),foreign key(cid) references Course(cid))')
#db.execute_sql('drop table if exists Course')
#db.execute_sql('drop table if exists Student')
#db.execute_sql('drop table if exists Employ')
#print Course().__create_sql__()
#course=Course(cid='a7', cname='orm2fix', precid='c2')
#kw = {'cname': 'orm2fix', 'cid': 'a7', 'precid': 'c2'}
args.append(arg)
pk = self.__primary_key__.name
args.append(getattr(self, pk))
db.update('update `%s` set %s where %s=?' % (self.__table__, ','.join(L), pk), *args)
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root','0901ck6#112ss','test')
db.update('drop table if exists user')
db.update('create table user (id int primary key,name text,email text,passwd text,last_modified real)')
import doctest
doctest.testmod()
import db
from config import DB_CONFIG
# 需要预先调用，且只调用一次
db.create_engine(**DB_CONFIG)
def get_lab_info():
"""
获取国家重点实验室信息
:return:
"""
lab_info = []
sql = "select id,org,institution from main_lab"
results = db.select(sql)
for result in results:
lab_info.append((result['id'], result['org'], result['institution']))
return lab_info
def get_institution_info():
"""
获取院系信息
:return:
"""
institution_info = []
sql = "select ID,SCHOOL_NAME,NAME from es_institution"
results = db.select(sql)
for result in results:
institution_info.append(
(result['ID'], result['SCHOOL_NAME'], result['NAME']))
return self
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k,v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
'''
因为Model类有__getattr__方法，所以可以使用getattr获取属性值
'''
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'wxy6772102', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
class User(Model):
__table__ = "user"
id = StringField(primary_key=True, default=db.next_id, ddl="varchar(50)")
email = StringField(updatable=False, ddl="varchar(50)")
passwd = StringField(ddl="varchar(50)")
# admin = BooleanField()
name = StringField(ddl="varchar(50)")
# image = StringField(ddl='varchar(500)')
last_modified = FloatField(default=time.time)
# FloatField(updatable=False, default=time.time)
if __name__ == "__main__":
# print db.next_id()
# print db.next_id
logging.getLogger("").handlers = []
logging.basicConfig(level=logging.DEBUG)
# print "debug"
db.create_engine("root", "355itu11", "test")
# 创建实例:
user = User(name="Michael22", passwd="passwd1", email="*****@*****.**")
print hasattr(user, "id")
# print user.id
# 存入数据库:
# user.insert()
# db.update('drop table if exists user')
# db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
# import doctest
# doctest.testmod()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from tudou_site import settings
import db
kw = {"charset": "utf8"}
db.create_engine(
settings.mysql_option["user"],
settings.mysql_option["passwd"],
settings.mysql_option["db"],
settings.mysql_option["host"],
settings.mysql_option["port"],
**kw
)
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk),
*args)
return self
def insert(self):
"""
通过db对象的insert接口执行SQL
SQL: insert into `user` (`passwd`,`last_modified`,`id`,`name`,`email`) values (%s,%s,%s,%s,%s),
ARGS: ('******', 1441878476.202391, 10190, 'Michael', '*****@*****.**')
"""
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('www-data', 'www-data', 'test')
db.update('drop table if exists user')
db.update(
'create table user (id int primary key, name text, email text, passwd text, last_modified real)'
)
import doctest
doctest.testmod()
def delete(self):
self.pre_delete and self.pre_delete()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk),
*args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', '123456', 'test')
db.update('drop table if exists user')
db.update(
'create table user (id int primary key, name text, email text, passwd text, last_modified real)'
)
import doctest
doctest.testmod()
pk = self.__primary_key__.name
args = (getattr(self, pk), )
db.update('delete from %s where %s=?' % (self.__table__, pk), *args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
if not hasattr(self, k):
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', 'root', 'test')
db.update('drop table if exists user')
db.update('create table user (id int primary key, name text, email text, passwd text, last_modified real)')
import doctest
doctest.testmod()
args = (getattr(self, pk), )
db.update('delete from `%s` where `%s`=?' % (self.__table__, pk),
*args)
return self
def insert(self):
self.pre_insert and self.pre_insert()
params = {}
for k, v in self.__mappings__.iteritems():
if v.insertable:
print v.insertable
if not hasattr(self, k):
print self
print k
print v
setattr(self, k, v.default)
params[v.name] = getattr(self, k)
db.insert('%s' % self.__table__, **params)
return self
if __name__ == '__main__':
logging.basicConfig(level=logging.DEBUG)
db.create_engine('root', '83527116', 'test')
db.update('drop table if exists user')
db.update(
'create table user (id int primary key, name text, email text, passwd text, last_modified real)'
)
import doctest
doctest.testmod()