Contents
All snippets based on these 2 models:
import MySQLdb
from skylark import Database, Model, Field, PrimaryKey, ForeignKey
class User(Model):
name = Field()
email = Field()
class Post(Model):
name = Field()
post_id = PrimaryKey()
user_id = ForeignKey(User.id)
Database.set_dbapi(MySQLdb)
Database.config(db='mydb', user='root', passwd='')
from models import User
user = User.create(name='jack', email='jack@gmail.com') # model instance
user = User(name='Kate', email='kate@gmail.com')
user.save() # last insert id
from models import User
User.at(1).update(name='tom') # rows affected
user = User.at(1).getone()
user.save() # rows affected
from models import User
# user at id=1
user = User.at(1).getone()
user = User.findone(id=1)
# all users
users = User.getall()
# user 2 < id < 5
users = User.findall(User.id > 2, User.id < 5)
from models import User
User.at(1).delete() # rows affected
user = User.at(2).getone()
user.destroy() # rows affected
tip: Use destroy() wisely
from models import User
# select user.id, user.name, user.email from user where user.id < '4'
query = User.where(User.id < 4).select()
# select user.id, user.name, user.email from user where user.id <= '4'
query = User.where(User.id <= 4).select()
# select user.id, user.name, user.email from user where user.id > '4'
query = User.where(User.id > 4).select()
# select user.id, user.name, user.email from user where user.id >= '4'
query = User.where(User.id >= 4).select()
# select user.id, user.name, user.email from user where user.id = '4'
query = User.where(User.id == 4).select()
# select user.id, user.name, user.email from user where user.id <> '4'
query = User.where(User.id != 4).select()
# select user.id, user.name, user.email from user where (user.id > '4'
# and user.id < '7')
User.where((User.id > 4) & (User.id < 7)).select()
# select user.id, user.name, user.email from user where (user.id = '4'
# or user.id = '7')
User.where((User.id == 4) | (User.id == 7)).select()
# select user.id, user.name, user.email from user where user.name like '%abc'
User.where(User.name.like('%abc')).select()
# select user.id, user.name, user.email from user where user.id
# between '4' and '7'
User.where(User.id.between(4, 7)).select()
# select user.id, user.name, user.email from user where user.id in ('5', '6')
User.where(User.id._in(5, 6)).select()
# select user.id, user.name, user.email from user where user.id
# not in ('5', '6')
User.where(User.id.not_in(5, 6)).select()
from models import Database
Database.change('db2') # change to `db2`
from models import Database
cursor = Database.execute('show tables')
from models import User
from skylark import fn
query = User.at(1).select(User.name.alias('un'))
result = query.execute()
user = result.one()
user.un # retrieve `name` by user.un
query = User.select(fn.count(User.name))
result = query.execute()
result.tuples()[0][0] # retrieve count result by result.tuples()
from models import User
user = User(name='jack')
if user in User:
print 'Some one in table is named jack'
from models import User
# select user.id from user where (user.id > '1' and (user.name = 'jack' or user.email = 'abc@abc.com'))
User.where(
(User.id > 1) & ((User.name == 'jack') | (User.email == 'abc@abc.com'))
).select(User.id)
from skylark import fn, distinct
from models import User
# select count(distinct(user.name)) from user
query = User.select(fn.count(distinct(User.name)))
result = query.execute()
return result.tuples()[0][0]
from models import User, Post
# select user.id, user.name, user.email from user where user.id in (select post.user_id from post)
query = User.where(User.id._in(Post.select(Post.user_id))).select()
from models import User
from skylark import fn, sql
# create data..
User.create(name='jack')
User.create(name='jack')
User.create(name='foo')
# select count(user.id) as count_id, user.name from user group by user.name having count_id >= '2'
query = User.groupby(User.name).having(
sql('count_id') >= 2
).select(fn.count(User.id).alias('count_id'), User.name)
result = query.execute()
for row in result.tuples():
print row[0] # count of id
print row[1] # user's name
from models import User
query = User.select()
results = query.execute()
return results.tuples() # tuple of rows, each row like: (1L, 'jack', 'jack@gmail.com')
from models import User
count = User.count()
max_id = User.max(User.id)
min_id = User.min(User.id)
sum_of_ids = User.sum(User.id)
avg_of_ids = User.avg(User.id)
from models import User, Post
# delete user from post, user where post.user_id = user.id
query = (Post & User).delete(User) # mysql supports; sqlite3 dosenot support