SQLython is a Python library that allows you to write SQL queries in Python. It is designed to be a simple and easy-to-use tool for working with SQL databases in Python. Inspired by Eloquent in Laravel.
pip install sqlython
Create a .env file in the root of your project and add the following variables:
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=
DB_DATABASE=database
It will automatically create a connection to the database using the environment variables.
Alternatively, you can manually create a connection using the DatabaseConnection class.
Just call the initialize() in the main file of your project.
from sqlython.connection import DatabaseConnection
DatabaseConnection.initialize(
host='localhost',
port=3306,
user='root',
password='',
database='database'
)
i.e. users.py
from sqlython.model import Model
class User(Model):
table = 'users'
fillable = ['name', 'username', 'email', 'password']
hidden = ['password']
timestamps = True
soft_delete = True
casts = {
'is_active': 'boolean'
}
from users import User
user = User().find(1)
User = User()
users = User.where('is_active', True).get()
print(users)
The name of the table in the database.
The columns that are allowed to be assigned.
The columns that are not allowed to be assigned.
The columns that are hidden from the output.
Automatically set the created_at and updated_at columns.
When set to True, the deleted_at column will be set to the current timestamp when a record is deleted.
The default number of records to return per page when using the paginate() method.
The columns that should be cast to a specific data type. Available data types are: string, number, float,
boolean, date, json.
Retrieve all records from the database.
users = User.get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 0 }
# ...
# ]
Retrieve the first record from the database.
user = User.first()
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 }
Get a record by its ID.
user = User.find(1)
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 }
Get the number of records.
count = User.count()
# 2
Retrieve records paginated. Returns a dictionary with the following properties:
users = User.paginate(page=1, per_page=10)
# {
# data: [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 0 }
# ...
# ],
# total: 100,
# pages: 10,
# page: 1,
# perPage: 10,
# nextPage: 2,
# prevPage: null
# }
Insert a new record into the database.
user = User.insert({
'name': 'John Doe',
'username': 'john_doe',
'email': 'john@doe.com',
'password': 'password'
})
# { insert_id: 1 }
Update records in the database. Must be called after a where() method.
User.where('id', 1).update({
'name': 'Jane Doe',
'username': 'jane_doe'
})
# { affected_rows: 1 }
Delete records from the database. Must be called after a where() method.
If soft_delete is set to True, the record will be "soft deleted" by setting the deleted_at column to the current
timestamp.
User.where('id', 1).delete()
# { affected_rows: 1 }
Restore a "soft deleted" record by setting the deleted_at column to NULL. Must be called after a where() method.
User.where('id', 1).restore()
# { affected_rows: 1 }
Permanently delete records from the database whether soft_delete is set to True or False. Must be called after a
where() method.
User.where('id', 1).force_delete()
# { affected_rows: 1 }
Select specific columns from the database.
users = User.select('id', 'name').get()
# or
users = User.select(['id', 'name']).get()
# or
users = User.select('id, name').get()
# or
users = User.select('id').select('name').get()
# [
# { id: 1, name: 'John Doe' },
# { id: 2, name: 'Jane Doe' }
# ...
# ]
Filter records by a column value.
If column is a dictionary, the key-value pairs will be used to filter the records.
If column is a string and only has two arguments, the operator will default to = and the value will be the second
argument.
If all three arguments are provided, then treat it as it.
users = User.where('is_active', 1).get()
# or
users = User.where({'is_active': 1}).get()
# or
users = User.where('is_actiove', '=', 1).get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1 }
# ...
# ]
Same as where() method. Adds an OR condition to the query.
users = User.where('is_active', 1).or_where('username', 'john_doe').get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1 }
# ...
# ]
Filter records by a raw SQL query.
users = User.where_raw('is_active = 1').get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1 }
# ...
# ]
Same as where_raw() method. Adds an OR condition to the query.
users = User.where_raw('is_active = 1').or_where_raw('username = "john_doe"').get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1 }
# ...
# ]
Filter records by a column value that is in a list of values.
users = User.where_in('id', [1, 2]).get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1 }
# ...
# ]
Filter records by a column value that is not in a list of values.
users = User.where_not_in('id', [1, 2]).get()
# [
# { id: 3, name: 'John Smith', username: 'john_smith', is_active: 1 },
# { id: 4, name: 'Jane Smith', username: 'jane_smith', is_active: 1 }
# ...
# ]
Filter records by a column value that is NULL.
users = User.where_null('transferred_at').get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1, transferred_at: None },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1, transferred_at: None }
# ...
# ]
Filter records by a column value that is not NULL.
users = User.where_not_null('transferred_at').get()
# [
# { id: 3, name: 'John Smith', username: 'john_smith', is_active: 1, transferred_at: '2024-01-01 00:00:00' },
# { id: 4, name: 'Jane Smith', username: 'jane_smith', is_active: 1, transferred_at: '2024-03-05 00:00:00' }
# ...
# ]
Include "soft deleted" records in the query.
users = User.with_trashed().get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1, deleted_at: '2024-01-01 00:00:00' },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1, deleted_at: '2024-03-05 00:00:00' }
# ...
# ]
asc, desc.Order records by a column.
users = User.order_by('name', 'asc').get()
# [
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1 },
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 }
# ...
# ]
Group records by a column.
users = User.select('count(id) as total', 'is_active').group_by('is_active').get()
# [
# { total: 2, is_active: 1 },
# { total: 2, is_active: 0 }
# ]
Limit the number of records returned.
users = User.limit(2).get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1 }
# ]
inner, left, right,
full.Join another table to the query.
users = User.join('profiles', 'users.id', '=', 'profiles.user_id').get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1, user_id: 1, bio: 'Hello, World!' },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1, user_id: 2, bio: 'Goodbye, World!' }
# ...
# ]
Join another table to the query using a left join.
users = User.left_join('profiles', 'users.id', '=', 'profiles.user_id').get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1, user_id: 1, bio: 'Hello, World!' },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1, user_id: 2, bio: 'Goodbye, World!' }
# ...
# ]
Define a "has one" relationship between two tables.
from profiles import Profile
from sqlython.model import Model
class User(Model):
table = 'users'
...
def profile(self):
return self.has_one(Profile(), 'user_id', 'id', 'profile', lambda q: q.select('bio', 'user_id'))
Define a "has many" relationship between two tables.
from posts import Post
from sqlython.model import Model
class User(Model):
table = 'users'
...
def posts(self):
return self.has_many(Post(), 'user_id', 'id', 'posts', lambda q: q.select('title', 'user_id'))
Define a "belongs to" relationship between two tables.
from users import User
from sqlython.model import Model
class Profile(Model):
table = 'profiles'
...
def user(self):
return self.belongs_to(User(), 'user_id', 'id', 'user', lambda q: q.select('id', 'name', 'username'))
Include a relationship in the query.
users = User.with_relation('profile').get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1, profile: { bio: 'Hello, World!' } },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1, profile: { bio: 'Goodbye, World!' } }
# ...
# ]
Execute a raw SQL query.
users = User.raw_query('SELECT * FROM users WHERE is_active = 1').get()
# [
# { id: 1, name: 'John Doe', username: 'john_doe', is_active: 1 },
# { id: 2, name: 'Jane Doe', username: 'jane_doe', is_active: 1 }
# ...
# ]
FAQs
A lightweight and user-friendly SQL query builder for Python
We found that sqlython demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Research
The Socket Research Team breaks down a malicious wrapper package that uses obfuscation to harvest credentials and exfiltrate sensitive data.
Research
Security News
Attackers used a malicious npm package typosquatting a popular ESLint plugin to steal sensitive data, execute commands, and exploit developer systems.
Security News
The Ultralytics' PyPI Package was compromised four times in one weekend through GitHub Actions cache poisoning and failure to rotate previously compromised API tokens.