CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
Roman
Roman

Reputation: 3941

Turn python sqlalchemy class into raw SQL (create table with foreign key)

I have a python app and I use sqlalchemy and posgreSQL. I have already some data in the DB and do not want to recreate it, so I want to add a table to the DB and keep all data (earlier I used alembic for that, but I want to use raw SQL this time).

This is my new table, which I want to add to the DB in python:

# User payed data
class UserPayData(Base):
    __tablename__ = 'userspaydata'
    id = Column(Integer, primary_key=True) 
    account_owner = Column(Text, nullable=False)
    IBAN = Column(Text, nullable=False)
    # Foreign Keys
    belongs_to_user_id = Column(Integer, ForeignKey('users.id'))
    payment_belongs_to_user_relation = relationship("User", back_populates="payment_belongs_to_user_addresses")

    def __init__(self, account_owner=None, IBAN=None):
        self.account_owner = account_owner
        self.IBAN = IBAN

    def get(self, id):
        if self.id == id:
            return self
        else:
            return None

    def __repr__(self):
        return '<%s(%r, %r, %r)>' % (self.__class__.__name__, self.id, self.account_owner, self.IBAN)

Here is the relevant part from the users table:

# Project
class User(UserMixin, Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    # etc...
    # Foreign Key payment
    payment_belongs_to_user_addresses = relationship('UserPayData', back_populates="payment_belongs_to_user_relation")

This is the raw SQL which I want to execute:

CREATE TABLE 'userspaydata'
(
    account_owner TEXT NOT NULL,
    IBAN TEXT NOT NULL,
    belongs_to_user_id INT references users(id)
);

I have found many examples and some are different.

I have a few questions:

Do I have to create a primary key aswell? AFAIK, if the primary key is not defined by me it will be automatically ID INT

Relationships do not need to be defined on the DB level, they are defined on the app level or am I wrong? If I am wrong, how I define the relationship in raw SQL?

I have not submitted the SQL query yet, is the syntax correct?

Upvotes: 1

Views: 410

Answers (2)

Roman
Roman

Reputation: 3941

This is my solution and it seems to work (I also added datetime):

CREATE TABLE userspaydata (
    Id SERIAL primary key,
    account_owner TEXT NOT NULL,
    IBAN TEXT NOT NULL,
    date_added TIMESTAMP WITH TIME ZONE,
    belongs_to_user_id INT references users(id)
);

PK must be defined by me.

Relationships are handled on the app level.

Upvotes: 0

Haleemur Ali
Haleemur Ali

Reputation: 28313

Do I have to create a primary key aswell

Yes, if you want to work with the tables using sqlalchemy Declarative Mappings, and in general its a great idea to specify the primary key.

Relationships do not need to be defined on the DB level, they are defined on the app level or am I wrong?

Foreign Keys defined in your Sqlalchemy Declarative classes are mapped to database Foreign Key Constraints.

Again, relationships in relational databases are another great idea. Specify foreign key dependencies unless you have a specific reason not to.

If you don't specify constraints in the database you can end up with corrupted data.

If you are emitting DDL statements manually, you can specify the foreign key constraints. Foreign Keys in the Postgres Documentation

I have not submitted the SQL query yet, is the syntax correct?

No, Here's a corrected version of your DDL statement.

CREATE TABLE userspaydata (
  id INTEGER PRIMARY KEY,
  account_owner TEXT NOT NULL,
  IBAN TEXT NOT NULL,
  belongs_to_user_id INT references users(id) -- this is a FK constraint
);

Note that id doesn't auto-increment. For auto-increment id

change the id column definition to

-- postgrseql 10 & above
id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
-- postgresql 9.6 & below
id SERIAL PRIMARY KEY

and change the sqlalchemy mapping for id column to 

id = Column(Integer, primary_key=True, auto_increment=True)

Upvotes: 2

Related Questions