[web2py] Re: MSSQL Select statements
Thanks DenesL ^ ^ On Nov 13, 3:57 pm, DenesL wrote: > > rows=db(db.user.email=...@b.com).select() > > that did not paste properly, should be > > rows=db(db.User.Email=="a...@b.com").select()
[web2py] Re: MSSQL Select statements
> rows=db(db.user.email=...@b.com).select() that did not paste properly, should be rows=db(db.User.Email=="a...@b.com").select()
[web2py] Re: MSSQL Select statements
Brian,
Thanks ^ ^ this i understand. Thanks for taking time and give me a
great example!
On Nov 13, 3:48 pm, Brian M 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 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 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 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 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 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 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 dif
[web2py] Re: MSSQL Select statements
You have
CREATE TABLE [User](
Email varchar(50),
Name varchar(50),
PIN int,
Phone varchar(50),
Address varchar(50),
Mileage int,
MealPref varchar(50),
PRIMARY KEY (Email));
that translates into this web2py model
db.define_table('User',
Field('Email','string',50),
Field('Name','string',50),
Field('PIN','integer'),
Field('Phone','string',50),
Field('Address','string',50),
Field('Mileage','integer'),
Field('MealPref','string',50),
primarykey=['Email'],
migrate=False
)
after which you can use the table in your controllers e.g.
rows=db(db.User.Email=='a...@b.com).select()
For more info on keyed tables see
http://groups.google.com/group/web2py/browse_thread/thread/db150376b06d47fc
Denes
On Nov 13, 4:32 pm, Crim wrote:
> you can down load the sql code herehttp://www.megaupload.com/?d=ARORZKPF
>
> no i cant use it .. so i take it i have to define the table in
> web2py?
>
> thanks a lot for your help ^ ^
>
> On Nov 13, 3:09 pm, DenesL wrote:
>
> > Hi Crim,
>
> > if you can use db.user.email that means you have defined the table in
> > web2py.
> > As I understand it, as per your previous posts, that table was not
> > created with web2py so it must be a legacy table.
>
> > You can access it depending on two things:
> > 1) If the table has an auto incrementing field similar to web2py's id
> > field then you just define that field as type 'id' (don't forget to
> > set migrate=False as
> > perhttp://web2py.com/book/default/chapter/06#Legacy-Databases-and-Keyed-...).
> > 2) If there is no such field but the table has one or more fields that
> > are keys you can still access it using a primarykey definition (see
> > same section in the book as above).
>
> > There should be no need to use [dbo] or [master].[dbo] anywhere.
>
> > Posting your model would make it easier for others to help you.
>
> > Dens.
>
> > On Nov 13, 3:39 pm, Crim 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 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
>
>
[web2py] Re: MSSQL Select statements
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 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 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 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 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 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 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 c
[web2py] Re: MSSQL Select statements
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 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 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 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 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 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
[web2py] Re: MSSQL Select statements
you can down load the sql code here http://www.megaupload.com/?d=ARORZKPF
no i cant use it .. so i take it i have to define the table in
web2py?
thanks a lot for your help ^ ^
On Nov 13, 3:09 pm, DenesL wrote:
> Hi Crim,
>
> if you can use db.user.email that means you have defined the table in
> web2py.
> As I understand it, as per your previous posts, that table was not
> created with web2py so it must be a legacy table.
>
> You can access it depending on two things:
> 1) If the table has an auto incrementing field similar to web2py's id
> field then you just define that field as type 'id' (don't forget to
> set migrate=False as
> perhttp://web2py.com/book/default/chapter/06#Legacy-Databases-and-Keyed-...).
> 2) If there is no such field but the table has one or more fields that
> are keys you can still access it using a primarykey definition (see
> same section in the book as above).
>
> There should be no need to use [dbo] or [master].[dbo] anywhere.
>
> Posting your model would make it easier for others to help you.
>
> Dens.
>
> On Nov 13, 3:39 pm, Crim 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 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
[web2py] Re: MSSQL Select statements
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 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 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 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 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
[web2py] Re: MSSQL Select statements
yes
On Nov 13, 3:10 pm, Crim 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 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 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 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
>
>
[web2py] Re: MSSQL Select statements
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 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 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 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
[web2py] Re: MSSQL Select statements
Hi Crim,
if you can use db.user.email that means you have defined the table in
web2py.
As I understand it, as per your previous posts, that table was not
created with web2py so it must be a legacy table.
You can access it depending on two things:
1) If the table has an auto incrementing field similar to web2py's id
field then you just define that field as type 'id' (don't forget to
set migrate=False as per
http://web2py.com/book/default/chapter/06#Legacy-Databases-and-Keyed-Tables).
2) If there is no such field but the table has one or more fields that
are keys you can still access it using a primarykey definition (see
same section in the book as above).
There should be no need to use [dbo] or [master].[dbo] anywhere.
Posting your model would make it easier for others to help you.
Dens.
On Nov 13, 3:39 pm, Crim 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 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
>
>
[web2py] Re: MSSQL Select statements
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 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 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
>
>
[web2py] Re: MSSQL Select statements
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 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
[web2py] Re: MSSQL Select statements
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
[web2py] Re: MSSQL Select statements
im not using web2py to do the second select statement the one "FROM
[master].[dbo].[User]"
just the:
if db(db.User.Email == s).select():
print "success"
i connected to my db and want to select the table user and the column
email
On Nov 13, 1:05 pm, mdipierro wrote:
> There is no webpy API to generate
>
> "FROM [master].[dbo].[User]"
>
> only
>
> "FROM [User]"
>
> On Nov 13, 12:45 pm, Crim wrote:
>
>
>
>
>
>
>
> > what cant i use? the web2py statement?
>
> > On Nov 13, 12:26 pm, mdipierro wrote:
>
> > > You cannot this in web2py. perhaps you can create a database view.
>
> > > On Nov 13, 12:11 pm, Crim wrote:
>
> > > > alright so i got a connection between my MSSql server any web2py. that
> > > > has been all sorted out but now im having issues with how to select a
> > > > tuple from web2py
>
> > > > in web2py i have this code:
>
> > > > if db(db.User.Email == s).select():
> > > > print "success"
>
> > > > which should check if the users email is in the db
>
> > > > but when i write a select statement for my server its like so:
>
> > > > SELECT TOP 1000 [Email]
> > > > ,[Name]
> > > > ,[PIN]
> > > > ,[Phone]
> > > > ,[Address]
> > > > ,[Mileage]
> > > > ,[MealPref]
> > > > FROM [master].[dbo].[User]
>
> > > > notice the dbo? do i have to include this in my select .. (have tried
> > > > and it still gives errors)
>
> > > > the error snapshot:
> > > > ('user')
>
> > > > thanks,
> > > > Crim
[web2py] Re: MSSQL Select statements
There is no webpy API to generate
"FROM [master].[dbo].[User]"
only
"FROM [User]"
On Nov 13, 12:45 pm, Crim wrote:
> what cant i use? the web2py statement?
>
> On Nov 13, 12:26 pm, mdipierro wrote:
>
> > You cannot this in web2py. perhaps you can create a database view.
>
> > On Nov 13, 12:11 pm, Crim wrote:
>
> > > alright so i got a connection between my MSSql server any web2py. that
> > > has been all sorted out but now im having issues with how to select a
> > > tuple from web2py
>
> > > in web2py i have this code:
>
> > > if db(db.User.Email == s).select():
> > > print "success"
>
> > > which should check if the users email is in the db
>
> > > but when i write a select statement for my server its like so:
>
> > > SELECT TOP 1000 [Email]
> > > ,[Name]
> > > ,[PIN]
> > > ,[Phone]
> > > ,[Address]
> > > ,[Mileage]
> > > ,[MealPref]
> > > FROM [master].[dbo].[User]
>
> > > notice the dbo? do i have to include this in my select .. (have tried
> > > and it still gives errors)
>
> > > the error snapshot:
> > > ('user')
>
> > > thanks,
> > > Crim
>
>
[web2py] Re: MSSQL Select statements
what cant i use? the web2py statement?
On Nov 13, 12:26 pm, mdipierro wrote:
> You cannot this in web2py. perhaps you can create a database view.
>
> On Nov 13, 12:11 pm, Crim wrote:
>
>
>
>
>
>
>
> > alright so i got a connection between my MSSql server any web2py. that
> > has been all sorted out but now im having issues with how to select a
> > tuple from web2py
>
> > in web2py i have this code:
>
> > if db(db.User.Email == s).select():
> > print "success"
>
> > which should check if the users email is in the db
>
> > but when i write a select statement for my server its like so:
>
> > SELECT TOP 1000 [Email]
> > ,[Name]
> > ,[PIN]
> > ,[Phone]
> > ,[Address]
> > ,[Mileage]
> > ,[MealPref]
> > FROM [master].[dbo].[User]
>
> > notice the dbo? do i have to include this in my select .. (have tried
> > and it still gives errors)
>
> > the error snapshot:
> > ('user')
>
> > thanks,
> > Crim
[web2py] Re: MSSQL Select statements
You cannot this in web2py. perhaps you can create a database view.
On Nov 13, 12:11 pm, Crim wrote:
> alright so i got a connection between my MSSql server any web2py. that
> has been all sorted out but now im having issues with how to select a
> tuple from web2py
>
> in web2py i have this code:
>
> if db(db.User.Email == s).select():
> print "success"
>
> which should check if the users email is in the db
>
> but when i write a select statement for my server its like so:
>
> SELECT TOP 1000 [Email]
> ,[Name]
> ,[PIN]
> ,[Phone]
> ,[Address]
> ,[Mileage]
> ,[MealPref]
> FROM [master].[dbo].[User]
>
> notice the dbo? do i have to include this in my select .. (have tried
> and it still gives errors)
>
> the error snapshot:
> ('user')
>
> thanks,
> Crim
