Wiki

Clone wiki

Flunt Sql Data Access Layer / Home

Welcome

Welcome to use FsDal to make your database access more easy and comfortable.

FsDal features

  1. Map Python Interface to sql script
  2. Single Object map to sql script(update,delete)
  3. Auto optimization for sql

How to use

First,we need to create some tables. => show create sql script fsdal_test_db

It's an user system of a big project,we use this 3 tables to show you how easy it would be.

Before use,Install it

$ hg clone http://bitbucket.org/alexander_lee/flunt-sql-data-access-layerwiki/
$ cd flunt-sql-data-access-layerwiki
$ python setup.py install

First, import something

import datetime #why import this? we need it in next code
from fsdal.dal import db,field as f,Sum,Count,Avg,Connection

set_conn(Connection(host="127.0.0.1",user="root",password="123456",database="test_db"))

then we need to set connection,we recommend you to use long connection for better performance

Insert Data

db.auth_user.insert(
                     username='alexander',
                     password='123456',
                     email='superpowerlee@gmail.com',
                     nick_name='justsoso',
                     join_date=datetime.datetime.now(),
                     last_login=datetime.datetime.now(),
                     is_active=1,
                     state=1,
                   )

this code will create sql script like

INSERT INTO `auth_user` (`username`,`nick_name`,`is_active`,`state`,`join_date`,`last_login`,`password`,`email`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)

QuerySet

this object is the base object for select,update and delete action. it will create where clause in SELECT,UPDATE and DELETE script

create QuerySet
last_day=datetime.datetime.now()-datetime.timedelta(days=1)
q=db.auth_user(f.is_active==0,f.join_date>=last_day)

it will create sql clause

WHERE `auth_user`.`is_active`=%s AND `auth_user`.`join_date`>=%s
Query data

Get one row by email:

user = db.auth_user(f.email=='superpowerlee').one()

SQL:

SELECT * FROM `auth_user` WHERE `auth_user`.`email`= %s

then you can access attribute use dot like user.nick_name,if you modify attribute value,just use user._save().it will create update for the columns you changed and execute.

Get a list of user

 users=[user for user in db.auth_user(f.state==1).select]
 #then we update all state to 0
 for user in users:
     user.state=0
     user._save()

SQL:

SELECT `auth_user`.* FROM `auth_user` WHERE `auth_user`.`state`= %s

Update Queryset

But we can got it use an effective way

db.auth_user(f.state==1).update(f.state==0)

SQL:

UPDATE `auth_user` SET `state`=%s WHERE `auth_user`.`state`= %s

also we can increase or decrease the column value,as:

db.auth_user(f.state==1).update(f.state+1)

SQL:

UPDATE `auth_user` SET `state`=`state` + %s WHERE `auth_user`.`state`= %s

Delete QuerySet

so,delete from QuerySet would be very easy:

db.auth_user(f.state==1).delete()

SQL:

DELETE FROM `auth_user`  WHERE `auth_user`.`state`= %s

Have fun!

Updated