default NULL

[lestat]

How I can add default NULL for column?

class UserIp(db.Model, UnicodeMixin):
__tablename__ = 'user_ip'

user_id = db.Column(db.Integer, db.ForeignKey('user.id'),
primary_key=True, nullable=True, server_default=None)
ip = db.Column(postgres.CIDR, nullable=False,
server_default='127.0.0.1/32')
time_last = db.Column(db.DateTime, nullable=True,
server_default=None)

I tried server_default=None, default=None, server_default='NULL', and
it not works.

I need that sqlalchemy generate create table with
"time_last" TIMESTAMP NULL DEFAULT NULL
instead
"time_last" TIMESTAMP NULL

Thanks!

[Michael Bayer]

On Nov 10, 2011, at 2:09 AM, lestat wrote:

> How I can add default NULL for column?
>
> class UserIp(db.Model, UnicodeMixin):
> __tablename__ = 'user_ip'
>
> user_id = db.Column(db.Integer, db.ForeignKey('user.id'),
> primary_key=True, nullable=True, server_default=None)
> ip = db.Column(postgres.CIDR, nullable=False,
> server_default='127.0.0.1/32')
> time_last = db.Column(db.DateTime, nullable=True,
> server_default=None)
>
> I tried server_default=None, default=None, server_default='NULL', and
> it not works.

two things here:

1. time_last = Column(DateTime, nullable=True, server_default=text('NULL'))

2. all columns in a relational database default to NULL so not sure why you'd need to do this.

[Alex K]

Thanks, but if I need allow nullable primary_keys it not works.

I tried:

user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='SET NULL'), primary_key=True, nullable=True, server_default=text('NULL'))

it generates

CREATE TABLE user_ip (

user_id INTEGER DEFAULT NULL, 

PRIMARY KEY (user_id), 

FOREIGN KEY(user_id) REFERENCES "user" (id) ON DELETE SET NULL

)

and I see in pgsql modifiers "not null":

\d user_ip

    Table "public.user_ip"

 Column  |  Type   | Modifiers 

---------+---------+-----------

 user_id | integer | not null

Indexes:

    "user_ip_pkey" PRIMARY KEY, btree (user_id)

Foreign-key constraints:

    "user_ip_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) ON DELETE SET NULL

Can sqlalchemy allow nullable primary_keys?

Or table without primary key?

Thanks.

[Wichert Akkerman]

On 11/11/2011 11:20 AM, Alex K wrote:

Thanks, but if I need allow nullable primary_keys it not works.

I tried:

user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='SET NULL'), primary_key=True, nullable=True, server_default=text('NULL'))

A primary key can never be null. The PostgreSQL documentation describes this as follows: "Technically, a primary key constraint is simply a combination of a unique constraint and a not-null constraint.".

I suggest that you replace primary_key=True with unique=True.

Wichert.

[Alex K]

Oh, sorry, my mistake.

Can I create table in sqlalchemy without primary key?

class UserIp(db.Model, UnicodeMixin):

    __tablename__ = 'user_ip'

    user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='SET NULL'))

    ip = db.Column(postgres.CIDR, nullable=False, server_default='127.0.0.1/32')

and it raise error:

ArgumentError: Mapper Mapper|UserIp|user_ip could not assemble any primary key columns for mapped table 'user_ip'

Thanks!

[Gunnlaugur Briem]

You can create such a table, yes, if you are sure you should:

from sqlalchemy import *

e = create_engine('sqlite:///')

t = Table('foo', MetaData(e), Column('bar', Text))

t.create()

(or skip the last call if you are referencing an existing table in the DB)

The ORM part of SQLAlchemy does require a primary key to be specified. If the table has no primary key, you can define it in the mapper using the `primary_key` argument. Either:

class T(object):

    pass

from sqlalchemy.orm import mapper

mapper(T, t, primary_key=t.c.bar)

or using declarative:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base(e)

class T2(Base):

    __table__ = t

    __mapper_args__ = {'primary_key': t.c.bar}

(But unless you really know what you're doing, this is not what you should be doing.)

Regards,

- Gulli