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

Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.