Getting Started with PugSQL: A Step-by-Step Guide

PugSQL is a lightweight SQL library designed for use in Python. It allows developers to write SQL queries in a simple, readable syntax and provides a number of helpful features to make working with databases easier. Before going deep into this let's have a peek into the basics.

Basic Terminologies

Database: A database is an organized collection of data that can be easily accessed and managed. Data are organized in tables, rows, and columns. It is also indexed which makes managing easier.

SQL: Structured Query Language is used to operate on the data stored in a database.

Query: Queries are commands that are used to request data or to perform some actions on the data.

Why PugSQL…?

1. Simple syntax: PugSQL provides a simple syntax for writing SQL queries using named placeholders. This makes it easy to create complex queries without the risk of errors, as developers do not have to write SQL queries as strings.
2. Multiple database engine support: PugSQL supports multiple database engines, including PostgreSQL, MySQL, and SQLite. This makes it a versatile choice for developers who work with multiple databases.
3. Synchronous and asynchronous support: PugSQL supports both synchronous and asynchronous database access, allowing developers to choose the best approach for their specific project.
4. Built-in support for transactions: PugSQL includes built-in support for transactions, making it easy to ensure that multiple queries are executed atomically. This helps to maintain data consistency and avoid errors caused by partial updates.
5. Database migration support: PugSQL includes support for database migrations, allowing developers to easily make changes to their database schema over time. This helps to maintain data integrity and allows for database evolution over time, as the needs of the application change.

Getting Started With PugSQL

Installation

You can Install PugSQL using the command

pip install pugsql

Writing SQL Files

As mentioned earlier PugSQL is used to write simple, readable SQL Queries. Procedure to write SQL files for PugSQL:

1. It is compulsory to create a folder or directory named ‘queries’.
2. Create a file inside that directory with the extension “.sql ”.
3. You can define your queries in a .sql file.

PugSQL Queries

PugSQL Queries are similar to functions. A function is a set of codes that are used to perform a task. All functions have names and are called upon with that name whenever required. Similarly in the .sql file, all the queries are given a name and that name can be used in the python program whenever needed.

Syntax

-- :name <method_name> : <return_type>
    <SQL Query>

in the above-mentioned syntax,

• method_name → Query to be executed
• return_type → Specifies what the query returns

Example:

-- :name print_all :many
select * from TABLE

You can also send parameters to the queries by giving the variable name in parenthesis and prefixing it with a colon,

-- :name <method_name> : <return_type>
    <SQL Query (:variable)>

Example:

-- :name insert_one :insert
INSERT INTO TABLE (ROW) VALUES (:name);

PugSQL return types

• one: Return a single row
• many: Return many rows
• affected: Return the number of affected rows
• scalar: Returns the first value in the first row
• insert: Returns the ID of the row inserted

PugSQL and Python

Making a PugSQL Module

We can start by importing ‘Pugsql’. The SQL files we’ve created should be parsed into a module by PugSQL. The module object exposes all of your queries as functions, taking keyword parameters.

import pugsql
queries = pugsql.module('queries/')

Connecting to a Database

The easiest way to connect to a database is to just call the connect method on your PugSQL module and give it a SQL Alchemy-compatible connection string.

SQLite    -> queries.connect('sqlite:///test.db')
Postgres  -> queries.connect('postgresql://mcfunley@localhost/dbname') 

Running Queries

We can call the query with its name to run it, if it has attributes it can be passed in the parenthesis

Example:
    without parameter  : queries.print_all()
    with parameter     : queries.insert_one(name="test")

Example program

A simple example to create and add a few rows into a table

SQL File

-- :name create :insert
CREATE TABLE Entry_logs (
 name TEXT NOT NULL,
    enter TEXT NOT NULL,
    exit TEXT NOT NULL
);

-- :name insert :insert
INSERT INTO Entry_logs (name, enter, exit) VALUES(:name, :enter, :exit);

Python File

import pugsql
import pathlib
import sqlite3 


def startpy():

    queries = pugsql.module(f'{pathlib.Path(__file__).parent.resolve()}/queries/')
    queries.connect('sqlite:///test.db')
    queries.create()
    queries.insert(name="Ananthu", enter="7:00", exit="12:00")
    queries.insert(name="Santhosh", enter="8:00", exit="12:00")
    queries.insert(name="Baghya", enter="7:30", exit="11:00")
if __name__ == '__main__':
    startpy()

Output

Overall, PugSQL is a valuable tool for Python developers working with SQL databases and is worth considering for any project that requires reliable and efficient database access.