Sinppets

Contents

• Sinppets
  • Create
  • Update
  • Read
  • Delete
  • Operators
  • Change database
  • Execute raw sql
  • Alias
  • Test instance in table
  • Expressions with priority
  • Count(distinct field)
  • Sub query
  • Having
  • Retrieve tuples data
  • Aggregators
  • Delete record from multiple tables

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='')

Create

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

Update

from models import User

User.at(1).update(name='tom')  # rows affected

user = User.at(1).getone()
user.save()  # rows affected

Read

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)

Delete

from models import User

User.at(1).delete()  # rows affected

user = User.at(2).getone()
user.destroy()  # rows affected

tip: Use destroy() wisely

Operators

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()

Change database

from models import Database

Database.change('db2')  # change to `db2`

Execute raw sql

from models import Database

cursor = Database.execute('show tables')

Alias

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()

Test instance in table

from models import User

user = User(name='jack')

if user in User:
    print 'Some one in table is named jack'

Expressions with priority

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)

Count(distinct field)

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]

Sub query

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()

Having

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

Retrieve tuples data

from models import User

query = User.select()
results = query.execute()
return results.tuples()  # tuple of rows, each row like: (1L, 'jack', 'jack@gmail.com')

Aggregators

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)

Delete record from multiple tables

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