As you may have guessed the DEFAULT keyword is used to "even" the size of
the tuples in the VALUES clause and explicitly asks the RDBMS to replace
itself with the currently defined default value for the corresponding
column.
The RDBMS in question is Postgres (>=11).
The context is we want to insert, in batches, data that comes for a message
queue. Data comes in as (flat) JSON documents, but where some keys may be
omitted. We'd like to insert the batches without the need to know the
server defaults. We don't use the ORM layer, only the core layer (though I
don't think that would make any difference).
I attached an example that shows why using the DEFAULT keyword seems
useful: it avoids errors or data losses. A short comment explains what's
wrong at the top of each case. The example, to be run successfully, assumes
a Postgres server is running, accessible through its unix socket, and the
current user as access to a default database (generally one which name
matches is login name) and can create a table in the public schema. You can
change the server address if need be (see l. 48), change the schema (l. 42)
or table name (l. 36).
The important thing in the previous example was that we have tuples of
different sizes: a 3-tuple and a 2-tuple (when DEFAULT is omitted).
Something that, if fed as is to your SQL server, it will probably choke on:
(1,'one',
'I') and (1000000, 'one million'). If I do what you suggest (omit the roman
column) then I cannot pass an explicit value for that column when I have
one.
A detail I forgot in the previous message is that the `roman` column in the
`numbers` table is *not nullable*.
CREATE TABLE number (
i INTEGER,
letters STRING,
roman STRING DEFAULT 'inexpressible' NOT NULL,
) ;
I mention this because I found old posts that mention in some case tuples
may be complete with NULL(s), but that is not what I observed and would not
work anyway in our context. You might argue why not drop the NOT NULL and
make NULL equivalent to 'inexpressible'. That would make perfect sense, I a
perfect world. But assume the database is ages old, hence has lots of
quirks I cannot get rid of (yet).
Of course, I may be missing something, probably obvious, that would explain
why nobody asked about this before. Hope it is a bit clearer.
Regards,
Nicolas
Le mardi 14 avril 2020 18:35:48 UTC+2, Mike Bayer a écrit :
>
>
>
> On Tue, Apr 14, 2020, at 10:41 AM, Nicolas Caniart wrote:
>
> Hi !
>
> I've been looking into the documentation but could not find it.
> What is the proper way to insert a DEFAULT keywork in the tuples of the
> VALUES clause in an INSERT statement ? As in
>
> CREATE TABLE number (
> i INTEGER,
> letters STRING,
> roman STRING DEFAULT '',
> ) ;
>
> INSERT INTO number (c1, c2, c3) VALUES (1, 'one', 'I'), (1000000, 'one
> million', DEFAULT) ; -- NOTE THE DEFAULT HERE
>
>
> I'm not familiar with this keyword but to my knowledge there is no need
> for it on any backend I'm familiar with, you instead omit the "roman"
> column from the insert construct itself by only specifying values for the
> "i" and "letters" columns. The "roman" column will not be present and
> the SQL side default will fire off.
>
> If you can share the reason that this special DEFAULT keyword is otherwise
> needed as well as what kind of database this is feel free, literal_column()
> would be the only way to go.
>
>
> (yes this example is silly)
>
> The best I came up with, is using the `literal_column('DEFAULT',
> type_=...)` construct.
>
> I wondered if there is a better way, cause I was worried about the keyword
> possibly getting quoted at some point, though I am not 100% sure that could
> happen.
>
> Thanks in advance !
>
> Regards,
> Nicolas.
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlal...@googlegroups.com <javascript:>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/9c133c97-a5c6-4863-8f40-d8174949f4d7%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/9c133c97-a5c6-4863-8f40-d8174949f4d7%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/93a9c888-976c-4fcf-a739-dffbf62172bd%40googlegroups.com.
# -*- coding: utf-8; -*-
import logging
from sqlalchemy import (Column,
create_engine,
exc,
insert,
Integer,
literal,
literal_column,
MetaData,
String,
Table,)
def _fix(defaults, *records):
for record in records:
for k in defaults:
record[k] = record.get(k, defaults[k])
print(record)
yield record
def tabulize(result):
print(' id | i | letter | roman')
print('-------+-----------+---------------------------+---------')
for row in result:
print(' {: 5d} | {: 9d} | {:25s} | {:7s}'.format(*row))
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.getLogger('sqlalchemy').setLevel(logging.INFO)
Numbers = Table('numbers',
MetaData(),
Column('id', Integer, primary_key=True),
Column('i', Integer, nullable=False),
Column('letter', String, nullable=False),
Column('roman', String, nullable=False, server_default=literal('not set')),
schema='public',
)
DEFAULTS = {
'roman': literal_column('DEFAULT', type_=Numbers.c.roman),
}
engine = create_engine('postgresql+psycopg2:///')
Numbers.create(engine, checkfirst=True)
engine.execute('TRUNCATE TABLE numbers;')
# Fails: expects 3-tuples, needs 3 attributes per tuple first only has 2
try:
insert_stmt1 = (Numbers
.insert([Numbers.c.i, Numbers.c.letter, Numbers.c.roman])
.values([{'i': 1000001, 'letter': 'one million and one'},
{'i': 1, 'letter': 'one', 'roman': 'I'},
]))
except exc.ArgumentError as err1: # Can't mix single-values and multiple values formats in one statement
print('1. ', err1)
# Fails: (same) expects 3-tuples, needs 3 attributes per tuple first only has 2
try:
insert_stmt2 = (Numbers
.insert([Numbers.c.i, Numbers.c.letter, Numbers.c.roman])
.values([{'i': 2, 'letter': 'two', 'roman': 'II'},
{'i': 1000002, 'letter': 'one million and 2'},
]))
except exc.ArgumentError as err2: # Can't mix single-values and multiple values formats in one statement
print('2. ', err2)
# Fails: second "tuple" does not have as many attributes as first one.
insert_stmt3 = (Numbers
.insert()
.values([{'i': 3, 'letter': 'three', 'roman': 'III'},
{'i': 1000003, 'letter': 'one million and 3'},
]))
with engine.begin() as t:
try:
t.execute(insert_stmt3)
except exc.CompileError as err3:
print('3. ', err3)
# Works as expected
insert_stmt4 = (Numbers
.insert()
.values(list(_fix(DEFAULTS,
*[{'i': 1000004, 'letter': 'one million and four'},
{'i': 4, 'letter': 'four', 'roman': 'IV'},
],
))))
with engine.begin() as t:
try:
t.execute(insert_stmt4)
except exc.IntegrityError as err4:
print('4. ', err4)
# Incorrect: value for the roman (V) is ignored default inserted
insert_stmt5 = (Numbers
.insert()
.values([{'i': 1000005, 'letter': 'one million and five'},
{'i': 5, 'letter': 'five', 'roman': 'V'},
],
))
with engine.begin() as t:
try:
t.execute(insert_stmt5)
except exc.IntegrityError as err5:
print('5. ', err5)
# Incorrect: value for the roman (VI) is ignored default inserted
insert_stmt6 = (insert(Numbers)
.values([{'i': 1000006, 'letter': 'one million and six'},
{'i': 6, 'letter': 'six', 'roman': 'VI'},
],
))
with engine.begin() as t:
try:
t.execute(insert_stmt6)
except exc.IntegrityError as err6:
print('6. ', err6)
tabulize(engine.execute(Numbers.select()))
# Numbers.drop(engine)
# vim: et:sw=4:syntax=python:ts=4:
