Brian, Thanks ^ ^ this i understand. Thanks for taking time and give me a great example!
On Nov 13, 3:48 pm, Brian M <[email protected]> wrote: > Crim, > > Yes, the criteria are any python variables you might like substituted > into your query. I probably shouldn't have used only 'strings' in my > example, sorry. > > my_city = 'somewhere' > my_state = 'MN' > start_date = date(2010, 11, 13) > end_date = date(2010, 11, 15) > results = db.executesql("SELECT * FROM my_table WHERE city = ? AND > state = ? AND date BETWEEN ? AND ?", > (my_city, my_state, > start_date.strftime('%Y-%m-%d), end_date.strftime('%Y-%m-%d')), True) > > The resulting SQL query would be: > SELECT * FROM my_table WHERE city = 'somewhere' AND state = 'MN' AND > date BETWEEN '2010-11-13' AND '2010-11-15' > > ~Brian > > On Nov 13, 3:40 pm, Crim <[email protected]> wrote: > > > > > > > > > Ah thanks this i like too ^ ^ > > > my only question on it is when you say: > > > results = db.executesql("SELECT foo,bar FROM my_table WHERE blah = ? > > and something = ?" , ('criteria 1', 'criteria 2'), True) > > > what do you mean by 'criteria 1' ... what kind of criteria? is that > > where i put a python bit of code? > > > On Nov 13, 3:23 pm, Brian M <[email protected]> wrote: > > > > 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
