I use web2py and MS SQL with raw SQL queries often.
results = db.executesql("SELECT foo,bar FROM my_table WHERE blah = ?
and something = ?" , ('criteria 1', 'criteria 2'), True)
The two extra pieces at the end will let you safely bind in query
parameters and the final True arg makes it so that the results are a
list of dictionaries. Thus you can do
for row in results:
print row['foo']
print row['bar']
not quite as slick as regular DAL but very usable, at least for
me. :-)
Hint - if you're doing queries for dates, the pyodbc doesn't seem to
automatically convert python date type into the needed format. Use
something like
results = db.executesql("SELECT * FROM my_table WHERE date_created
BETWEEN ? and ?" , (start_date.strftime('%Y-%m-%d'),
end_date.strftime('%Y-%m-%d')), True)
~Brian
On Nov 13, 3:10 pm, Crim <[email protected]> wrote:
> Nice i like the idea of raw sql statements ... but when i execute a
> select statement would it return anything ex
>
> variable = db.executesql("select....")
>
> would that work? (would test when i get home but im at work right now)
>
> On Nov 13, 2:41 pm, mdipierro <[email protected]> wrote:
>
>
>
>
>
>
>
> > web2py DAL only sees tables that are defined via the DAL. For raw sql
> > you can do db.executesql("....")
>
> > On Nov 13, 2:39 pm, Crim <[email protected]> wrote:
>
> > > thanks for the in-depth reply ^ ^
>
> > > im using existing tables and i master and another one stuff setup to
> > > do the same thing but this is a school project so im not to picky at
> > > the moment xD but thanks for the info for future use.
>
> > > Can i do the db(db.user.email == s).select() if i dont instantiate it
> > > in web2py? do i still have to define it? Considering the tables exist
> > > in the MSSql db?
>
> > > On Nov 13, 2:05 pm, Niphlod <[email protected]> wrote:
>
> > > > uhm .... are you using existing tables or you are using web2py to
> > > > create them ??
>
> > > > dbo shouldn't affect at all the query, but a few hints nonetheless :
>
> > > > master is a really nasty place to create tables .... "master" db
> > > > should be left untouched .... in web2py you define a database when you
> > > > istantiate db object, and all the query done on that db won't be able
> > > > to "see" other databases
>
> > > > that stated, if you have your connection string as
> > > > "mssql://username:passw...@localhost/master"
>
> > > > the query "select * from [master].[dbo].[User] where [master].[dbo].
> > > > [User]=s" should be accomplished doing:
>
> > > > db.define_table('user',
> > > > .....
> > > > Field('email', 'string'),
> > > > .....
> > > > )
>
> > > > and db(db.user.email == s).select()
>
> > > > anyway, I'm not sure (I don't have MSSQL installed to try out, but I
> > > > can reply you on monday at work) that there are a few places where
> > > > this implementation could not work:
>
> > > > - web2py creates tables and fields with lowercase letters, so "User"
> > > > get selectable only if database is set to be case insensitive
> > > > - "dbo" stands for "dbowner" and it represent the schema which the
> > > > object (in this case, a table) belongs. if your user has not the
> > > > "db_owner" role on the db you won't be able to "see" it
>
> > > > A few words also on this. Having objects beloging to different schemas
> > > > under the same database it's definitely an option, but web2py
> > > > (actually the library it uses, and many others like that) can't
> > > > "choose" different schemas... they simply ignore it and the database
> > > > defaults to whatever the default is for the user querying the database
> > > > itself.
> > > > You can have different schemas to separate between permissions on
> > > > different objects in the same database, but to avoid headaches it's
> > > > always better to create a separate user that "owns" a single database
> > > > and put every data inside it.
>
> > > > If the user in the connection string is db_owner of the database,
> > > > "select * from dbo.table" is perfectly equivalent to "select * from
> > > > table"
>
> > > > If you have any other question please ask, I'll be glad to test it out
> > > > on Monday
