2

In order to learn more about python I have made the following simple script:

#!/usr/bin/env python
# coding=utf-8
# -*- Mode: python; c-basic-offset: 4 -*-

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import mapper

db=create_engine('postgresql://somepassword:someuser@127.0.0.1/hello')
db.echo = False
metadata = MetaData(db)

people=Table('people',metadata,autoload=True)

class People(object):
    pass

people_mapper=mapper(People,people)

db_session=sessionmaker()
db_session.configure(bind=db)

lewis_hamilton=People()
lewis_hamilton.name='Lewis'
lewis_hamilton.surname='Hamilton'
lewis_hamilton.age=44

db_session.save(lewis_hamilton)
db_session.flush()

And I use the following table that "Lewis Hamilton" will be inserted (directly from psql):

                                 Table "public.people"
 Column  |         Type          |                      Modifiers                      
---------+-----------------------+-----------------------------------------------------
 id      | integer               | not null default nextval('people_id_seq'::regclass)
 name    | character varying(60) | 
 surname | character varying(60) | 
 age     | smallint              |

But when I run the script I get the following error:

sqlalchemy.exc.ArgumentError: Mapper Mapper|People|people could not assemble any primary key columns for mapped table 'people'

I have also seen the following questions but do not provide the answers I want:

Because I use autoload in order to load the sql mapping.

Dimitrios Desyllas
  • 9,082
  • 15
  • 74
  • 164
  • What does \d people output? You have posted the columns but not indices displayed after these. Could you maybe modify your post to include this part as well? – Hannu May 25 '17 at 12:51

1 Answers1

5

  • About database URI format: user name goes before user password, so it should be 'postgresql://someuser:somepassword@127.0.0.1/hello' in your example.

  • We can pass echo flag in create_engine: it is better than setting attribute, moreover it is set to False by default, so we don't need it at all in particular case.

  • If you don't want to write your Table object schema with Column objects (more info at Describing Databases with MetaData) – just reflect your metadata and obtain 'people' table from tables field of metadata object.

  • sessionmaker is Session objects factory, not Session object, so you should use double call (looks ugly) or extract sessionmaker instance as separate object (in our case we will call it session_factory).

  • AFAIK Session objects doesn't have method save, but add.

  • If we want our changes (adding lewis_hamilton record to table) to be saved we should call commit method in the end since flush communicates with database but without committing changes record will be lost (in your case we can work without flush method calls, you can read more about flush vs commit here).

With all these comments something like this should work

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import mapper

db = create_engine('postgresql://someuser:somepassword@127.0.0.1/hello')
metadata = MetaData(db)

# reflecting 'people' table from database schema
metadata.reflect(only=['people'])
people = metadata.tables['people']


class People(object):
    pass


people_mapper = mapper(People, people)

session_factory = sessionmaker(bind=db)
db_session = session_factory()

lewis_hamilton = People()
lewis_hamilton.name = 'Lewis'
lewis_hamilton.surname = 'Hamilton'
lewis_hamilton.age = 44

db_session.add(lewis_hamilton)
db_session.commit()

but it will be better (since it is more explicit, second principle) to create people Table object using Column objects instead of reflecting like

people = Table('people', metadata,
               Column('id', Integer, primary_key=True),
               Column('name', String(60)),
               Column('surname', String(60)),
               Column('age', SmallInteger))

Also if you want to map class People with 'people' table following Object Relational Tutorial we will have something like

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

db = create_engine('postgresql://someuser:somepassword@127.0.0.1/hello')
metadata = MetaData(db)
people = Table('people', metadata,
               Column('id', Integer, primary_key=True),
               Column('name', String(60)),
               Column('surname', String(60)),
               Column('age', SmallInteger))

Base = declarative_base(metadata=metadata)


class People(Base):
    __table__ = people

or without people Table object:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

db = create_engine('postgresql://someuser:somepassword@127.0.0.1/hello')

metadata = MetaData(db)
Base = declarative_base(metadata=metadata)


class People(Base):
    __tablename__ = 'people'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(60))
    surname = Column('surname', String(60))
    age = Column('age', SmallInteger)

and we can add __init__ method as well so finally we will have

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

db = create_engine('postgresql://someuser:somepassword@127.0.0.1/hello')

metadata = MetaData(db)
Base = declarative_base(metadata=metadata)


class People(Base):
    __tablename__ = 'people'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(60))
    surname = Column('surname', String(60))
    age = Column('age', SmallInteger)

    def __init__(self, name, surname, age):
        self.name = name
        self.surname = surname
        self.age = age


session_factory = sessionmaker(bind=db)
db_session = session_factory()

lewis_hamilton = People(name='Lewis',
                        surname='Hamilton',
                        age=44)

db_session.add(lewis_hamilton)
db_session.commit()

Further improvements

As said in PEP-8:

Wildcard imports ( from import * ) should be avoided, as they make it unclear which names are present in the namespace, confusing both readers and many automated tools. There is one defensible use case for a wildcard import, which is to republish an internal interface as part of a public API (for example, overwriting a pure Python implementation of an interface with the definitions from an optional accelerator module and exactly which definitions will be overwritten isn't known in advance).

so your line

from sqlalchemy import *

should be more specific since there are a lot of things inside and you don't need them all.

Azat Ibrakov
  • 9,998
  • 9
  • 38
  • 50