# square_database_structure
> 📌 versioning: see [CHANGELOG.md](./CHANGELOG.md).
## about
python module to define postgresql database schemas using sqlalchemy.
## goals
- clear database → schema → table hierarchy
- reusable (template) across multiple projects
- single source of truth for schema and data
## installation
```bash
pip install square_database_structure
```
## usage
this module organizes database schemas in a standardized folder structure where each top-level folder represents a
database, and subfolders within it represent schemas. all mandatory components, such as `__init__.py` and tables.py,
data.py, stored_procedures_and_functions need to follow this structure.
### folder structure
```
square_database_structure/
├───main.py # global definition file (mandatory)
├───create_database.py # global database creation file (mandatory)
└───database1/ # each folder corresponds to a separate database
├───__init__.py # contains the global name for the database (mandatory)
└───schema1/ # each subfolder corresponds to a schema within the database
├───__init__.py # contains the global name for the schema (mandatory)
├───data.py # contains the data for insertion for the schema (mandatory)
├───enums.py # defines enums to be used in the schema (optional)
├───tables.py # defines tables of the schema (mandatory)
└───stored_procedures_and_functions/ # contains stored procedures and functions for the schema (mandatory)
├───__init__.py # contains logic to discover sql files (mandatory)
└───function1.sql # function or stored procedure sql file (optional)
```
- top-level folders: represent individual databases (e.g., database1).
- subfolders: represent schemas within each database (e.g., public, schema1).
- mandatory files:
- `__init__.py` (both at the database and schema level).
- tables.py within each schema.
- data.py within each schema.
- stored_procedures_and_functions package within each schema.
### defining database and schema names in `__init__.py`
each database and schema folder must contain an `__init__.py` file where the database and schema names are defined
as global variables.
#### example for database:
```python
# database1/__init__.py
global_string_database_name = "database1" # mandatory: database name
```
#### example for schema:
```python
# database1/schema1/__init__.py
global_string_schema_name = "schema1" # mandatory: schema name
```
### defining tables in tables.py
each schema folder must contain a tables.py file where:
- you must declare a Base object tied to the schema.
- you can define table classes, extending the Base object.
#### example tables.py:
```python
# tables.py
from sqlalchemy import Column, Integer, String, MetaData
from sqlalchemy.ext.declarative import declarative_base
from square_database_structure.square.public import global_string_schema_name
# 1. mandatory: declare Base with metadata pointing to the schema
Base = declarative_base(metadata=MetaData(schema=global_string_schema_name))
# 2.optional: define table classes by extending Base
class App(Base):
__tablename__ = 'app'
id = Column(Integer, primary_key=True)
app_name = Column(String, nullable=False)
```
### defining data in data.py
- you must declare a data_to_insert list to store optional data that may be inserted into the schema's tables.
```python
from square_database_structure.square.public.tables import App
# 1. mandatory: initialize a list for optional data insertion
data_to_insert = []
# optional: append data to be inserted into the table
data_to_insert.append(App(app_name="example_app"))
```
### defining function or stored procedure in stored_procedures_and_functions package
- paste this logic in the `__init__.py` of this package to discover all sql files.
```python
from pathlib import Path
directory = Path(__file__).parent
stored_procedures_and_functions = []
for file_path in directory.iterdir():
if file_path.is_file() and file_path.suffix == ".sql":
with file_path.open("r") as file:
content = file.read()
stored_procedures_and_functions.append(content)
```
- you can keep raw sql files each containing ideally 1 stored procedure or function.
- the name of the file should ideally correspond to the function / procedure name.
- this raw sql should be compatible with postgres database.
```sql
CREATE OR REPLACE FUNCTION add_user(
p_username VARCHAR,
p_email VARCHAR
) RETURNS TEXT AS $$
BEGIN
-- Insert a new user into the users table
INSERT INTO users (username, email)
VALUES (p_username, p_email);
-- Return a success message
RETURN 'User added successfully!';
END;
$$ LANGUAGE plpgsql;
```
### centralized definitions in main.py
the main.py file is mandatory and contains a global list that maps databases to schemas and their corresponding table
definitions. this list is manually created by the user (for now).
#### example main.py:
```python
# main.py
from square_database_structure.square.public import global_string_schema_name as schema1_name
from square_database_structure.square.public.tables import Base as Schema1Base
from square_database_structure.square.public.data import data_to_insert as schema1_data
from square_database_structure.square.public.stored_procedures_and_functions import (
stored_procedures_and_functions as schema1_stored_procedures_and_functions)
from square_database_structure.square import global_string_database_name as database1_name
# global list that maps databases and schemas
global_list_create = [
{
"database": database1_name, # mandatory: database name
"schemas": [
{
"schema": schema1_name, # mandatory: schema name
"base": Schema1Base, # mandatory: base for this schema
"data_to_insert": schema1_data, # mandatory: data to insert (even if empty)
"stored_procedures_and_functions": schema1_stored_procedures_and_functions,
# mandatory: stored procedures and functions (even if empty)
},
],
}
]
```
this file centralizes the definition of each database and schema, including the associated Base and data_to_insert for
table definitions.
### creating tables
once you have defined your databases, schemas, and tables, you can create them in your PostgreSQL database by using the
`create_database_and_tables` function.
```python
from square_database_structure import create_database_and_tables
# define the database connection details
db_username = "your_username"
db_password = "your_password"
db_ip = "localhost"
db_port = 5432
# call the function to create the database and tables
create_database_and_tables(db_username, db_password, db_ip, db_port)
```
## env
- python>=3.12.0
- postgresql >= 13
> feedback is appreciated. thank you!
Raw data
{
"_id": null,
"home_page": null,
"name": "square-database-structure",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.12",
"maintainer_email": null,
"keywords": "database, sqlalchemy, utilities",
"author": "Parth Mukesh Mangtani",
"author_email": "Parth Mukesh Mangtani <thepmsquare@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/4c/b3/b0c6498958962a19ad08023377ac99f766951d8e1d01715ce45320a34ae2/square_database_structure-2.8.0.tar.gz",
"platform": null,
"description": "# square_database_structure\n\n> \ud83d\udccc versioning: see [CHANGELOG.md](./CHANGELOG.md).\n\n## about\n\npython module to define postgresql database schemas using sqlalchemy.\n\n## goals\n\n- clear database \u2192 schema \u2192 table hierarchy\n- reusable (template) across multiple projects\n- single source of truth for schema and data\n\n## installation\n\n```bash\npip install square_database_structure\n```\n\n## usage\n\nthis module organizes database schemas in a standardized folder structure where each top-level folder represents a\ndatabase, and subfolders within it represent schemas. all mandatory components, such as `__init__.py` and tables.py,\ndata.py, stored_procedures_and_functions need to follow this structure.\n\n### folder structure\n\n```\nsquare_database_structure/\n\u251c\u2500\u2500\u2500main.py # global definition file (mandatory)\n\u251c\u2500\u2500\u2500create_database.py # global database creation file (mandatory)\n\u2514\u2500\u2500\u2500database1/ # each folder corresponds to a separate database\n \u251c\u2500\u2500\u2500__init__.py # contains the global name for the database (mandatory)\n \u2514\u2500\u2500\u2500schema1/ # each subfolder corresponds to a schema within the database\n \u251c\u2500\u2500\u2500__init__.py # contains the global name for the schema (mandatory)\n \u251c\u2500\u2500\u2500data.py # contains the data for insertion for the schema (mandatory)\n \u251c\u2500\u2500\u2500enums.py # defines enums to be used in the schema (optional)\n \u251c\u2500\u2500\u2500tables.py # defines tables of the schema (mandatory)\n \u2514\u2500\u2500\u2500stored_procedures_and_functions/ # contains stored procedures and functions for the schema (mandatory)\n \u251c\u2500\u2500\u2500__init__.py # contains logic to discover sql files (mandatory)\n \u2514\u2500\u2500\u2500function1.sql # function or stored procedure sql file (optional)\n```\n\n- top-level folders: represent individual databases (e.g., database1).\n- subfolders: represent schemas within each database (e.g., public, schema1).\n- mandatory files:\n - `__init__.py` (both at the database and schema level).\n - tables.py within each schema.\n - data.py within each schema.\n - stored_procedures_and_functions package within each schema.\n\n### defining database and schema names in `__init__.py`\n\neach database and schema folder must contain an `__init__.py` file where the database and schema names are defined\nas global variables.\n\n#### example for database:\n\n```python\n# database1/__init__.py\n\nglobal_string_database_name = \"database1\" # mandatory: database name\n```\n\n#### example for schema:\n\n```python\n# database1/schema1/__init__.py\n\nglobal_string_schema_name = \"schema1\" # mandatory: schema name\n```\n\n### defining tables in tables.py\n\neach schema folder must contain a tables.py file where:\n\n- you must declare a Base object tied to the schema.\n- you can define table classes, extending the Base object.\n\n#### example tables.py:\n\n```python\n# tables.py\nfrom sqlalchemy import Column, Integer, String, MetaData\nfrom sqlalchemy.ext.declarative import declarative_base\nfrom square_database_structure.square.public import global_string_schema_name\n\n# 1. mandatory: declare Base with metadata pointing to the schema\n\nBase = declarative_base(metadata=MetaData(schema=global_string_schema_name))\n\n\n# 2.optional: define table classes by extending Base\n\nclass App(Base):\n __tablename__ = 'app'\n\n\nid = Column(Integer, primary_key=True)\napp_name = Column(String, nullable=False)\n```\n\n### defining data in data.py\n\n- you must declare a data_to_insert list to store optional data that may be inserted into the schema's tables.\n\n```python\nfrom square_database_structure.square.public.tables import App\n\n# 1. mandatory: initialize a list for optional data insertion\ndata_to_insert = []\n# optional: append data to be inserted into the table\ndata_to_insert.append(App(app_name=\"example_app\"))\n```\n\n### defining function or stored procedure in stored_procedures_and_functions package\n\n- paste this logic in the `__init__.py` of this package to discover all sql files.\n\n```python\nfrom pathlib import Path\n\ndirectory = Path(__file__).parent\nstored_procedures_and_functions = []\n\nfor file_path in directory.iterdir():\n if file_path.is_file() and file_path.suffix == \".sql\":\n with file_path.open(\"r\") as file:\n content = file.read()\n stored_procedures_and_functions.append(content)\n```\n\n- you can keep raw sql files each containing ideally 1 stored procedure or function.\n- the name of the file should ideally correspond to the function / procedure name.\n- this raw sql should be compatible with postgres database.\n\n```sql\nCREATE OR REPLACE FUNCTION add_user(\n p_username VARCHAR,\n p_email VARCHAR\n) RETURNS TEXT AS $$\nBEGIN\n -- Insert a new user into the users table\n INSERT INTO users (username, email)\n VALUES (p_username, p_email);\n\n -- Return a success message\n RETURN 'User added successfully!';\nEND;\n$$ LANGUAGE plpgsql;\n\n```\n\n### centralized definitions in main.py\n\nthe main.py file is mandatory and contains a global list that maps databases to schemas and their corresponding table\ndefinitions. this list is manually created by the user (for now).\n\n#### example main.py:\n\n```python\n# main.py\n\nfrom square_database_structure.square.public import global_string_schema_name as schema1_name\nfrom square_database_structure.square.public.tables import Base as Schema1Base\nfrom square_database_structure.square.public.data import data_to_insert as schema1_data\nfrom square_database_structure.square.public.stored_procedures_and_functions import (\n stored_procedures_and_functions as schema1_stored_procedures_and_functions)\nfrom square_database_structure.square import global_string_database_name as database1_name\n\n# global list that maps databases and schemas\n\nglobal_list_create = [\n {\n \"database\": database1_name, # mandatory: database name\n \"schemas\": [\n {\n \"schema\": schema1_name, # mandatory: schema name\n \"base\": Schema1Base, # mandatory: base for this schema\n \"data_to_insert\": schema1_data, # mandatory: data to insert (even if empty)\n \"stored_procedures_and_functions\": schema1_stored_procedures_and_functions,\n # mandatory: stored procedures and functions (even if empty)\n },\n ],\n }\n]\n```\n\nthis file centralizes the definition of each database and schema, including the associated Base and data_to_insert for\ntable definitions.\n\n### creating tables\n\nonce you have defined your databases, schemas, and tables, you can create them in your PostgreSQL database by using the\n`create_database_and_tables` function.\n\n```python\nfrom square_database_structure import create_database_and_tables\n\n# define the database connection details\ndb_username = \"your_username\"\ndb_password = \"your_password\"\ndb_ip = \"localhost\"\ndb_port = 5432\n\n# call the function to create the database and tables\ncreate_database_and_tables(db_username, db_password, db_ip, db_port)\n```\n\n## env\n\n- python>=3.12.0\n- postgresql >= 13\n\n> feedback is appreciated. thank you!",
"bugtrack_url": null,
"license": "GPLv3",
"summary": "database scheme for my personal server.",
"version": "2.8.0",
"project_urls": {
"homepage": "https://github.com/thepmsquare/square_database_structure"
},
"split_keywords": [
"database",
" sqlalchemy",
" utilities"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "2deca21bb5f2b06587ef948f858a9fa9cd2a758002fff8f0ef181ef92d42431d",
"md5": "432583b3a91abd99d4ce70fba8b67809",
"sha256": "ba340c769a6ed7fe790802898ab880d07ad6fdbc0f5c5dd4dc642f3614c713a1"
},
"downloads": -1,
"filename": "square_database_structure-2.8.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "432583b3a91abd99d4ce70fba8b67809",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.12",
"size": 19572,
"upload_time": "2025-10-24T01:27:27",
"upload_time_iso_8601": "2025-10-24T01:27:27.473941Z",
"url": "https://files.pythonhosted.org/packages/2d/ec/a21bb5f2b06587ef948f858a9fa9cd2a758002fff8f0ef181ef92d42431d/square_database_structure-2.8.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "4cb3b0c6498958962a19ad08023377ac99f766951d8e1d01715ce45320a34ae2",
"md5": "190eea50d532009b9c05919c7d2c8e76",
"sha256": "e78d5b49d633c6c17aa4663324393cc5b86ebb37bdba816882700a85507b7ee6"
},
"downloads": -1,
"filename": "square_database_structure-2.8.0.tar.gz",
"has_sig": false,
"md5_digest": "190eea50d532009b9c05919c7d2c8e76",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.12",
"size": 7902,
"upload_time": "2025-10-24T01:27:28",
"upload_time_iso_8601": "2025-10-24T01:27:28.933050Z",
"url": "https://files.pythonhosted.org/packages/4c/b3/b0c6498958962a19ad08023377ac99f766951d8e1d01715ce45320a34ae2/square_database_structure-2.8.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-24 01:27:28",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "thepmsquare",
"github_project": "square_database_structure",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "square-database-structure"
}
JSON
