Skip to content

Python library and command-line interface (CLI) tool for interacting with PostgreSQL databases, providing simplified database management, query execution, and result export functionalities.

License

Notifications You must be signed in to change notification settings

abeltavares/PostQL

Repository files navigation

PostQL

GitHub Repository PyPI License


PostQL is a Python library and command-line interface (CLI) tool for managing PostgreSQL databases, executing queries, exporting data and interacting with PostgreSQL databases from the command line.

Features

  • Connect to PostgreSQL databases and execute SQL queries interactively via the CLI.
  • Perform database management tasks such as creating, deleting databases, users, tables, etc.
  • Execute SQL queries directly from Python code using the Postgres class.
  • Export query results to CSV, Excel, JSON, and Parquet formats.
  • Upload exported files to Amazon S3 buckets.

Installation

You can install PostQL via pip:

pip install postql

Usage

Command Line Interface (CLI)

To use the PostQL CLI, simply run postql followed by the desired command. Here are some examples:

# Connect to a database and execute SQL queries interactively
postql connect -H localhost -u postgres -P password -d my_database

# Run query
my_database> Select * from my_table

# Exit the CLI
exit

demo

Python Library

from postql import Postgres

# Initialize the Postgres connection
db = Postgres(host="localhost", port="5432", user="postgres", password="password")

# Connect to the 'bookstore' database
db.connect(database="market_data")

# Create the 'books' table
db.create_table("books", {
    "id": "SERIAL PRIMARY KEY",
    "title": "VARCHAR(255) NOT NULL",
    "author": "VARCHAR(255) NOT NULL",
    "price": "DECIMAL(10, 2) NOT NULL",
    "genre": "VARCHAR(255)"
})

# Create the 'orders' table
db.create_table("orders", {
    "id": "SERIAL PRIMARY KEY",
    "book_id": "INTEGER REFERENCES books(id)",
    "quantity": "INTEGER NOT NULL",
    "customer_name": "VARCHAR(255) NOT NULL",
    "order_date": "DATE NOT NULL"
})

# Insert sample books
db.insert("books", {"title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "price": 15.99, "genre": "Fiction"}).execute()
db.insert("books", {"title": "To Kill a Mockingbird", "author": "Harper Lee", "price": 12.99, "genre": "Fiction"}).execute()

# Insert a sample order
db.insert("orders", {"book_id": 1, "quantity": 2, "customer_name": "John Doe", "order_date": "2023-04-01"}).execute()

# Query all books
print("All books:")
db.select("books",["title"]).execute()

# Query all orders for a specific book
print("Orders for 'The Great Gatsby':")
db.select("orders").where({"book_id": 1}).execute()

# Update the price of a book
db.update("books").set({"price": 14.99}).where({"id": 1}).execute()

# Export books to a CSV file
db.select("books").to_csv("books.csv")

# Export orders to a CSV file
db.select("orders").to_csv("orders.csv")

# Disconnect from the database
db.disconnect()

Documentation

Contributing

Contributions are welcome! If you find any bugs or have suggestions for improvement, please open an issue or submit a pull request.

The codebase of this project follows the black code style. To ensure consistent formatting, the pre-commit hook is set up to run the black formatter before each commit.

Additionally, a GitHub Action is configured to automatically run the black formatter on every pull request, ensuring that the codebase remains formatted correctly.

Please make sure to run pip install pre-commit and pre-commit install to enable the pre-commit hook on your local development environment.

License

This project is licensed under the MIT License - see the LICENSE for details.

Releases

No releases published

Packages

No packages published

Languages