Re: [GENERAL] How to insert either a value or the column default?
On Mon, Aug 25, 2014 at 3:26 PM, Adrian Klaver wrote: > On 08/25/2014 06:32 AM, W. Matthew Wilson wrote: >> First of all, thanks for showing this trick! >> >> But I am confused. Will this trick stop working in a future version >> of psycopg2? Should I avoid using it? > > > Here is the email laying out the issues: > > http://www.postgresql.org/message-id/AANLkTi=ym3SCQKCQBtp8RJHUswwAPOpjXYKTXS=ah...@mail.gmail.com That's correct, thank you Adrian. Matthew: no, this will correctly work in all the future psycopg2 versions. Even if we started supporting a new protocol, such as the ISQLParam referred in the mentioned email, it won't be the default in psycopg2 and it should be enabled on purpose. The new protocol should be the default in this mythical psycopg3 instead. If something can be imported as psycopg2 it will support the ISQLQuote protocol by default, hence the Default object as implemented in this thread will work. -- Daniele -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert either a value or the column default?
On 08/25/2014 06:32 AM, W. Matthew Wilson wrote: On Sun, Aug 24, 2014 at 2:50 PM, Daniele Varrazzo wrote: It should be added to the library (it was first discussed in 2003...), but it's one of these things that will stop working when psycopg will start using the "extended query protocol" (together with other nifty features such as string literals for table/columns names) so in my mind it can only be included when psycopg will be able to do both client-side parameter interpolation and server-side arguments passing, and when the distinction between the two strategies will be clear (this is planned for a future psycopg3 but there is no timeline for it yet). First of all, thanks for showing this trick! But I am confused. Will this trick stop working in a future version of psycopg2? Should I avoid using it? Here is the email laying out the issues: http://www.postgresql.org/message-id/AANLkTi=ym3SCQKCQBtp8RJHUswwAPOpjXYKTXS=ah...@mail.gmail.com Thanks again! -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert either a value or the column default?
On Sun, Aug 24, 2014 at 2:50 PM, Daniele Varrazzo wrote: > It should be added to the library (it was first discussed in 2003...), > but it's one of these things that will stop working when psycopg will > start using the "extended query protocol" (together with other nifty > features such as string literals for table/columns names) so in my > mind it can only be included when psycopg will be able to do both > client-side parameter interpolation and server-side arguments passing, > and when the distinction between the two strategies will be clear > (this is planned for a future psycopg3 but there is no timeline for it > yet). First of all, thanks for showing this trick! But I am confused. Will this trick stop working in a future version of psycopg2? Should I avoid using it? Thanks again! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert either a value or the column default?
On 08/24/2014 11:50 AM, Daniele Varrazzo wrote: On Sat, Aug 23, 2014 at 7:10 PM, W. Matthew Wilson wrote: I have a table that looks sort of like this: create table tasks ( task_id serial primary key, title text, status text not null default 'planned' ); In python, I have a function like this: def insert_task(title, status=None): and when status is passed in, I want to run a SQL insert statement like this: insert into tasks (title, status) values (%s, %s) but when status is not passed in, I want to run this SQL insert instead: insert into tasks (title, status) values (%s, default) You can "easily" do that in psycopg with: class Default(object): def __conform__(self, proto): if proto is psycopg2.extensions.ISQLQuote: return self def getquoted(self): return 'DEFAULT' DEFAULT = Default() >>> print cur.mogrify('insert into place values (%s, %s)', ['adsf', DEFAULT]) insert into place values ('adsf', DEFAULT) Well that is cool. So you could do: status = None In [17]: cur.execute("insert into tasks(title, status) values(%s, %s)", ["first", status or DEFAULT]) In [18]: con.commit() test=> select * from tasks; task_id | title | status -+---+- 1 | first | planned yet). -- Daniele -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert either a value or the column default?
On Sat, Aug 23, 2014 at 7:10 PM, W. Matthew Wilson wrote: > I have a table that looks sort of like this: > > create table tasks > ( > task_id serial primary key, > title text, > status text not null default 'planned' > ); > > In python, I have a function like this: > > def insert_task(title, status=None): > > > and when status is passed in, I want to run a SQL insert statement like this: > > insert into tasks > (title, status) > values > (%s, %s) > > but when status is not passed in, I want to run this SQL insert instead: > > insert into tasks > (title, status) > values > (%s, default) You can "easily" do that in psycopg with: class Default(object): def __conform__(self, proto): if proto is psycopg2.extensions.ISQLQuote: return self def getquoted(self): return 'DEFAULT' DEFAULT = Default() >>> print cur.mogrify('insert into place values (%s, %s)', ['adsf', DEFAULT]) insert into place values ('adsf', DEFAULT) You can find more details at http://initd.org/psycopg/docs/advanced.html#adapting-new-python-types-to-sql-syntax It should be added to the library (it was first discussed in 2003...), but it's one of these things that will stop working when psycopg will start using the "extended query protocol" (together with other nifty features such as string literals for table/columns names) so in my mind it can only be included when psycopg will be able to do both client-side parameter interpolation and server-side arguments passing, and when the distinction between the two strategies will be clear (this is planned for a future psycopg3 but there is no timeline for it yet). -- Daniele -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert either a value or the column default?
On 08/23/2014 11:10 AM, W. Matthew Wilson wrote: I have a table that looks sort of like this: create table tasks ( task_id serial primary key, title text, status text not null default 'planned' ); In python, I have a function like this: def insert_task(title, status=None): and when status is passed in, I want to run a SQL insert statement like this: insert into tasks (title, status) values (%s, %s) but when status is not passed in, I want to run this SQL insert instead: insert into tasks (title, status) values (%s, default) I know how to pick the query with an if-clause in python, but I wish it were possible to do something like this: insert into tasks (title, status) values (%s, coalesce(%s, default)) I have tried different variations, but I keep getting syntax errors. Is there any way to do say: "if the value is not null, insert the value. Otherwise, insert the default value for this column" entirely in SQL? Some playing around with indicates the DEFAULT keyword only has context when directly entered in the VALUES portion of the INSERT statement. Trying to use it COALESCE or in CASE fails. When there is just one optional column, it is not a big deal to use an if-clause in python. But there are numerous optional columns. I know I could build up lists of strings in python but I'm hoping there's a simpler way to do this in the query. But I have a hard time already getting other programmers to understand SQL injection attacks and if they see me building up SQL queries from strings, even though there's no risk of a SQL injection in this scenario, I still don't want to break my "no string interpolation" rule of thumb unless I absolutely have to. And I know I could switch to some gigantic library like SQLAlchemy, but I really don't want to. In the end ORMs build SQL queries from strings also. When I have done this I build the column/field string and then the VALUES string using %s or %(some_field)s. Assuming you are using psycopg2 and passing the parameters using a list or dictionary respectively then the data will be properly escaped. Any advice is welcome. Thanks in advance! Matt -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert either a value or the column default?
Would you mind a non-python solution? I think the following will work for you. It uses an INSERT trigger on the table "tasks". The only minus is keeping the default value in both the table definition and the trigger. Of course, it is not really needed in the definition of the row value in the table due to the INSERT trigger supplying it anyway. For multiple possible fields, just have another if sequence. drop table if exists tasks cascade; -- for redefinition drop function if exists tasks_status(); --for redefinition create table tasks ( task_id serial primary key, title text, status text not null default 'planned' ); -- name the function whatever you want. create function tasks_status() returns trigger as $task_status$ begin -- see if status is NULL and replace if so if NEW.status is NULL then NEW.status := 'planned'; end if; --if NEW.other is NULL then -- NEW.Other := --end if; -- repeat the if to end if some more it required. return NEW; end; $task_status$ LANGUAGE plpgsql; -- name the trigger whatever you want. I made the same -- as the procedure just to keep them "linked" in my -- mind. Make sure you change the procedure name -- referenced if you change the function name. create trigger tasks_status before insert or update on tasks for each row execute procedure tasks_status(); -- just some example insert commands to -- test the trigger. took me 10 tries to get -- it typed in correctly. Stupid fingers! [grin] insert into tasks(title,status) values('one','active'); insert into tasks(title) values('two'); insert into tasks(title,status) values('three',NULL); On Sat, Aug 23, 2014 at 1:10 PM, W. Matthew Wilson wrote: > I have a table that looks sort of like this: > > create table tasks > ( > task_id serial primary key, > title text, > status text not null default 'planned' > ); > > In python, I have a function like this: > > def insert_task(title, status=None): > > > and when status is passed in, I want to run a SQL insert statement like > this: > > insert into tasks > (title, status) > values > (%s, %s) > > but when status is not passed in, I want to run this SQL insert instead: > > insert into tasks > (title, status) > values > (%s, default) > > I know how to pick the query with an if-clause in python, but I wish > it were possible to do something like this: > > insert into tasks > (title, status) > values > (%s, coalesce(%s, default)) > > I have tried different variations, but I keep getting syntax errors. > Is there any way to do say: > > "if the value is not null, insert the value. Otherwise, insert > the default value for this column" > > entirely in SQL? > > When there is just one optional column, it is not a big deal to use an > if-clause in python. But there are numerous optional columns. > > I know I could build up lists of strings in python but I'm hoping > there's a simpler way to do this in the query. > > But I have a hard time already getting other programmers to understand > SQL injection attacks and if they see me building up SQL queries from > strings, even though there's no risk of a SQL injection in this > scenario, I still don't want to break my "no string interpolation" > rule of thumb unless I absolutely have to. > > And I know I could switch to some gigantic library like SQLAlchemy, > but I really don't want to. > > Any advice is welcome. Thanks in advance! > > > Matt > > -- > W. Matthew Wilson > m...@tplus1.com > http://tplus1.com > > -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! <>< John McKown
[GENERAL] How to insert either a value or the column default?
I have a table that looks sort of like this: create table tasks ( task_id serial primary key, title text, status text not null default 'planned' ); In python, I have a function like this: def insert_task(title, status=None): and when status is passed in, I want to run a SQL insert statement like this: insert into tasks (title, status) values (%s, %s) but when status is not passed in, I want to run this SQL insert instead: insert into tasks (title, status) values (%s, default) I know how to pick the query with an if-clause in python, but I wish it were possible to do something like this: insert into tasks (title, status) values (%s, coalesce(%s, default)) I have tried different variations, but I keep getting syntax errors. Is there any way to do say: "if the value is not null, insert the value. Otherwise, insert the default value for this column" entirely in SQL? When there is just one optional column, it is not a big deal to use an if-clause in python. But there are numerous optional columns. I know I could build up lists of strings in python but I'm hoping there's a simpler way to do this in the query. But I have a hard time already getting other programmers to understand SQL injection attacks and if they see me building up SQL queries from strings, even though there's no risk of a SQL injection in this scenario, I still don't want to break my "no string interpolation" rule of thumb unless I absolutely have to. And I know I could switch to some gigantic library like SQLAlchemy, but I really don't want to. Any advice is welcome. Thanks in advance! Matt -- W. Matthew Wilson m...@tplus1.com http://tplus1.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general