PySQLQuery

PySQLQuery is a simple Python package for generating SQL code. Made to help programmers without much knowledge of SQL, it offers a simple way to generate SQL code using Pythonic techniques.

Table of contents

• Installation
• Docs
• Examples
  • Creating a simple table
  • Creating a more complex Table
  • Saving all tables that you have been created
• Contributing
• Credits
• License

Installation

Using pip:

pip install pysqlquery

Docs

See our Docs for comprehensive and detailed documentation on PySQLQuery. In the documentation, you will find in-depth explanations, usage examples, and additional resources to help you maximize your experience with PySQLQuery.

Examples

Creating a simple table

from pysqlquery import (
    Table,
    Column,
    Integer,
    String,
    Float,
    ForeignKey
)

class TbEmployees(Table):
    id = Column(Integer, primary_key=True, auto_increment='mysql')
    name = Column(String(50))
    email = Column(String(255), unique=True)
    wage = Column(Float(7, 2), default=1250.38)
    id_department = Column(ForeignKey('t_department', 'id'))

tb_emplooyes = TbEmployees()

print(tb_emplooyes)

The printed string will be:

CREATE TABLE TBEMPLOYEES (
    id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    wage FLOAT(7, 2) NOT NULL DEFAULT 1250.38,

    PRIMARY KEY (id),
    FOREIGN KEY (id_department) REFERENCES T_DEPARTMENT(id)
);

The provided example demonstrates the process of creating a single table using PySQLQuery, showcasing the simplicity and expressiveness of the package. Let's break down the key elements of the example:

Importing Required Classes

from pysqlquery import (
    Table,
    Column,
    Integer,
    String,
    Float,
    ForeignKey
)

The necessary classes are imported from the pysqlquery package. The Column, Table and a data type classes are essential for defining the structure of the SQL table, the constraint ones are optional like ForeignKey.

Defining the Table

class TbEmployees(Table):
    id = Column(Integer, primary_key=True, auto_increment='mysql')
    name = Column(String(50))
    email = Column(String(255), unique=True)
    wage = Column(Float(7, 2), default=1250.38)
    id_department = Column(ForeignKey('t_department', 'id'))

A new table class, TbEmployees, is defined by inheriting from the Table class. Columns such as id, name, and wage are specified using the Column class, each with its respective data type and constraints.

Creating an Instance of the Table

tb_emplooyes = TbEmployees()

An instance of the table, tb_employees, is created.

Printing the SQL Code

print(tb_emplooyes)

The instance of the table is printed, resulting in the corresponding SQL code being generated.

Creating a more complex Table

from pysqlquery import (
    Table,
    Column,
    Integer,
    String,
    Float
)
from pysqlquery.constraints import (
    ForeignKeyConstraint,
    PrimaryKeyConstraint,
    UniqueConstraint,
)

class TbEmployees(Table):
    __tablename__ = 'T_EMPLOYEES'
    id = Column(Integer, auto_increment='mysql')
    name = Column(String(50))
    email = Column(String(255))
    wage = Column(Float(7, 2), default=1250.38)
    id_department = Column(Integer)

    __constraints__ = [
        PrimaryKeyConstraint('pk_t_emplooyes', 'id'),
        ForeignKeyConstraint(
            'fk_t_emplooyes_t_department',
            'id_department',
            't_department',
            'id'
        ),
        UniqueConstraint('un_t_emplooyes', 'email')
    ]

tb_emplooyes = TbEmployees(create_if_not_exists=True)

print(tb_emplooyes)

The printed string will be:

CREATE TABLE IF NOT EXISTS T_EMPLOYEES (
    id INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    wage FLOAT(7, 2) NOT NULL DEFAULT 1250.38
    id_department INTEGER NOT NULL
);

ALTER TABLE T_EMPLOOYES
    ADD CONSTRAINT pk_t_emplooyes PRIMARY KEY (id);

ALTER TABLE T_EMPLOOYES
    ADD CONSTRAINT fk_t_emplooyes_t_department FOREIGN KEY (id_department) REFERENCES T_DEPARTMENT(id);

ALTER TABLE T_EMPLOOYES
    ADD CONSTRAINT un_t_emplooyes UNIQUE (email);

In this extended example, a more complex table is created using PySQLQuery. Let's delve into the key elements specific to this example, without repeating the details covered in the previous one:

Importing Required Classes and Constraints

from pysqlquery import (
    Table,
    Column,
    Integer,
    String,
    Float
)
from pysqlquery.constraints import (
    ForeignKeyConstraint,
    PrimaryKeyConstraint,
    UniqueConstraint,
)

In addition to the basic classes, this example introduces named constraint classes from the pysqlquery.constraints module, including PrimaryKeyConstraint, ForeignKeyConstraint, and UniqueConstraint.

Defining the Table

class TbEmployees(Table):
    __tablename__ = 'T_EMPLOYEES'
    id = Column(Integer, auto_increment='mysql')
    name = Column(String(50))
    email = Column(String(255))
    wage = Column(Float(7, 2), default=1250.38)
    id_department = Column(Integer)

    __constraints__ = [
        PrimaryKeyConstraint('pk_t_emplooyes', 'id'),
        ForeignKeyConstraint(
            'fk_t_emplooyes_t_department',
            'id_department',
            't_department',
            'id'
        ),
        UniqueConstraint('un_t_emplooyes', 'email')
    ]

This more complex table includes additional specifications such as a table name (__tablename__) and named constraint list (__constraints__).

Saving all tables that you have been created

The Table class provides the save_all_tables method that will save all tables that you have been created in a file.

Table.save_all_tables('./my_tables.sql')

Contributing

You may contribute in several ways like creating new features, fixing bugs, improving documentation and examples or translating any document here to your language. Find more information in CONTRIBUTING.md.

Credits

PySQLQuery is heavily inspired in SQLAlchemy (for table class structure) and ArrayMixer (for README).

License

MIT - Alberto Frigatto, 2023