How to define a table?

[jet10000]

tbl = Table('posts')
a = Column("id", "INT")
b = Column("valid_from", "DATETIME")
c = Column("valid_to", "DATETIME")

I want to access a,b,c in tbl.a, tbl.b, tbl.c.

and ,how to make a index or foreign key for a field

[elrik75]

Pypika is not an ORM. To manipulate data through objects you should look at sqlalchemy. Pypika is a SQL builder, so you must know SQL to use it. For now, I think Pypika is only for querying, so building SQL SELECT, and not for creating a model.

[noambloom]

I don't think that pypika has support for CREATE queries although I did find this merged PR for Vertica - https://github.com/kayak/pypika/pull/345

[jet10000]

    author = Table('author')
    author.id = Column("id", "INT GENERATED BY DEFAULT AS IDENTITY")
    author.name = Column("name", "VARCHAR(200)", nullable=False)
    author.is_admin = Column(column_name="is_admin", column_type="boolean", nullable=False, default=False)

    book = Table('book')
    book.id = Column("id", "INT GENERATED BY DEFAULT AS IDENTITY")
    book.name = Column("name", "VARCHAR(200)")
    book.author_id = Column('author_id', "INTEGER REFERENCES author(id)")

    q = PostgreSQLQuery.create_table(
        author
    ).columns(
        author.id,
        author.name,
        author.is_admin
    ).primary_key(
        author.id
    ).unique(
        author.id
    )
    q1 = PostgreSQLQuery.create_table(
        book
    ).columns(
        book.id,
        book.name,
        book.author_id
    )
    print(q.get_sql())

I tested, it's work. How to check table exists?