# PostQL
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:
```bash
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:
```bash
# 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
```
### Python Library
```python
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
- [Methods Documentation](https://abeltavares.github.io/PostQL/methods.html)
- [CLI Documentation](https://abeltavares.github.io/PostQL/cli.html)
## 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](https://github.com/psf/black) code style. To ensure consistent formatting, the [pre-commit](https://pre-commit.com/) 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](LICENSE.txt) for details.
Raw data
{
"_id": null,
"home_page": "https://github.com/abeltavares/postql",
"name": "PostQL",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Abel Tavares",
"author_email": "abelst9@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/e1/b3/8b89bdbe1cc00c1f2fb9e0e53436087fa10b2fae98320446b620623c3d2f/PostQL-1.0.3.tar.gz",
"platform": null,
"description": "# PostQL\n\nPostQL 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.\n\n## Features\n\n- Connect to PostgreSQL databases and execute SQL queries interactively via the CLI.\n- Perform database management tasks such as creating, deleting databases, users, tables, etc.\n- Execute SQL queries directly from Python code using the `Postgres` class.\n- Export query results to CSV, Excel, JSON, and Parquet formats.\n- Upload exported files to Amazon S3 buckets.\n\n## Installation\n\nYou can install PostQL via pip:\n\n```bash\npip install postql\n```\n## Usage\n\n### Command Line Interface (CLI)\n\nTo use the PostQL CLI, simply run `postql` followed by the desired command. Here are some examples:\n\n```bash\n# Connect to a database and execute SQL queries interactively\npostql connect -H localhost -u postgres -P password -d my_database\n\n# Run query\nmy_database> Select * from my_table\n\n# Exit the CLI\nexit\n\n```\n### Python Library\n\n```python\nfrom postql import Postgres\n\n# Initialize the Postgres connection\ndb = Postgres(host=\"localhost\", port=\"5432\", user=\"postgres\", password=\"password\")\n\n# Connect to the 'bookstore' database\ndb.connect(database=\"market_data\")\n\n# Create the 'books' table\ndb.create_table(\"books\", {\n \"id\": \"SERIAL PRIMARY KEY\",\n \"title\": \"VARCHAR(255) NOT NULL\",\n \"author\": \"VARCHAR(255) NOT NULL\",\n \"price\": \"DECIMAL(10, 2) NOT NULL\",\n \"genre\": \"VARCHAR(255)\"\n})\n\n# Create the 'orders' table\ndb.create_table(\"orders\", {\n \"id\": \"SERIAL PRIMARY KEY\",\n \"book_id\": \"INTEGER REFERENCES books(id)\",\n \"quantity\": \"INTEGER NOT NULL\",\n \"customer_name\": \"VARCHAR(255) NOT NULL\",\n \"order_date\": \"DATE NOT NULL\"\n})\n\n# Insert sample books\ndb.insert(\"books\", {\"title\": \"The Great Gatsby\", \"author\": \"F. Scott Fitzgerald\", \"price\": 15.99, \"genre\": \"Fiction\"}).execute()\ndb.insert(\"books\", {\"title\": \"To Kill a Mockingbird\", \"author\": \"Harper Lee\", \"price\": 12.99, \"genre\": \"Fiction\"}).execute()\n\n# Insert a sample order\ndb.insert(\"orders\", {\"book_id\": 1, \"quantity\": 2, \"customer_name\": \"John Doe\", \"order_date\": \"2023-04-01\"}).execute()\n\n# Query all books\nprint(\"All books:\")\ndb.select(\"books\",[\"title\"]).execute()\n\n# Query all orders for a specific book\nprint(\"Orders for 'The Great Gatsby':\")\ndb.select(\"orders\").where({\"book_id\": 1}).execute()\n\n# Update the price of a book\ndb.update(\"books\").set({\"price\": 14.99}).where({\"id\": 1}).execute()\n\n# Export books to a CSV file\ndb.select(\"books\").to_csv(\"books.csv\")\n\n# Export orders to a CSV file\ndb.select(\"orders\").to_csv(\"orders.csv\")\n\n# Disconnect from the database\ndb.disconnect()\n```\n\n## Documentation\n\n- [Methods Documentation](https://abeltavares.github.io/PostQL/methods.html)\n- [CLI Documentation](https://abeltavares.github.io/PostQL/cli.html)\n\n## Contributing\n\nContributions are welcome! If you find any bugs or have suggestions for improvement, please open an issue or submit a pull request.\n\nThe codebase of this project follows the [black](https://github.com/psf/black) code style. To ensure consistent formatting, the [pre-commit](https://pre-commit.com/) hook is set up to run the black formatter before each commit.\n\nAdditionally, a GitHub Action is configured to automatically run the black formatter on every pull request, ensuring that the codebase remains formatted correctly.\n\nPlease make sure to run `pip install pre-commit` and `pre-commit install` to enable the pre-commit hook on your local development environment.\n\n## License \n\nThis project is licensed under the MIT License - see the [LICENSE](LICENSE.txt) for details.\n",
"bugtrack_url": null,
"license": "BSD",
"summary": "Python wrapper for Postgres",
"version": "1.0.3",
"project_urls": {
"Homepage": "https://abeltavares.github.io/PostQL/",
"Repository": "https://github.com/abeltavares/postql"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "9f08c6fb1c029ae3f1e7114d1c96bec77b3e53135f88dd0d26862a9bae386f47",
"md5": "8c8f802a20cbee8b40788c03555f5ba3",
"sha256": "dc702806a6b43b070451535a9c498da45cdda18ef659e2b737b51ac10b62d329"
},
"downloads": -1,
"filename": "PostQL-1.0.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "8c8f802a20cbee8b40788c03555f5ba3",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 10923,
"upload_time": "2024-02-29T07:57:52",
"upload_time_iso_8601": "2024-02-29T07:57:52.204610Z",
"url": "https://files.pythonhosted.org/packages/9f/08/c6fb1c029ae3f1e7114d1c96bec77b3e53135f88dd0d26862a9bae386f47/PostQL-1.0.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "e1b38b89bdbe1cc00c1f2fb9e0e53436087fa10b2fae98320446b620623c3d2f",
"md5": "a586560cb2f4ceed0e741740aad965d8",
"sha256": "8319025759e674881b8f6b002547743d34c725e176d158fe4db4ce013b905f63"
},
"downloads": -1,
"filename": "PostQL-1.0.3.tar.gz",
"has_sig": false,
"md5_digest": "a586560cb2f4ceed0e741740aad965d8",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 9312,
"upload_time": "2024-02-29T07:57:53",
"upload_time_iso_8601": "2024-02-29T07:57:53.262551Z",
"url": "https://files.pythonhosted.org/packages/e1/b3/8b89bdbe1cc00c1f2fb9e0e53436087fa10b2fae98320446b620623c3d2f/PostQL-1.0.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-02-29 07:57:53",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "abeltavares",
"github_project": "postql",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "postql"
}
JSON
