Skip to content

msdss-base-database core

msdss-base-database

core¶

Database¶

class msdss_base_database.core.Database(driver='postgresql', user='msdss', password='msdss123', host='localhost', port='5432', database='msdss', load_env=True, env=<msdss_base_database.env.DatabaseDotEnv object>, *args, **kwargs)[source]¶

Class for MSDSS database management.

Parameters

driver (str) – The driver name of the database connection, which are commonly postgresql, sqlite, mysql, oracle or mssql (see SQLAlchemy supported databases).
user (str) – User name for the connection.
password (str) – Password for the user.
host (str) – Host address of the connection.
port (str) – Port number of the connection.
database (str) – Database name of the connection.
load_env (bool) – Whether to load the environmental variables using parameter env or not. The environment will only be loaded if the env_file exists.
env (msdss_base_database.env.DatabaseDotEnv or bool) –
An object to set environment variables related to database configuration. These environment variables will overwrite the parameters above if they exist.

By default, the parameters above are assigned to each of the environment variables below:
<parameter> = <environment variable> driver = MSDSS_DATABASE_DRIVER user = MSDSS_DATABASE_USER password = MSDSS_DATABASE_PASSWORD host = MSDSS_DATABASE_HOST port = MSDSS_DATABASE_PORT database = MSDSS_DATABASE_NAME
*args – Additional arguments passed to sqlalchemy.create_engine().
**kwargs – Additional arguments passed to sqlalchemy.create_engine().

_connection¶

The database engine object from sqlalchemy.

Type: sqlalchemy.engine.base.Engine

_inspector¶

The database inspector object from sqlalchemy.

Type: sqlalchemy.engine.reflection.Inspector

_metadata¶

The metadata object from sqlalchemy.

Type: sqlalchemy.schema.MetaData

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database import Database

# Initiate a connection with the default test user and database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Write sample data
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db.insert('test_table', data)

# Read the table to a pandas dataframe
df = db.select('test_table')

# Skip a row
df_skip = db.select('test_table', offset=1)

# Insert values into the table
new = {
    'id': [5, 6, 7],
    'column_one': ['e', 'f', 'g'],
    'column_two': [10, 12, 14]
}
db.insert('test_table', new)
df_insert = db.select('test_table')

# Delete rows from the table
db.delete(
    'test_table',
    where=('id', '=', 5)
)
df_delete = db.select('test_table')

# Update values in table
db.update(
    'test_table',
    where=('id', '>', 3),
    values={'column_one': 'AA'})
df_update = db.select('test_table')

# Display results
print('df:\n')
print(df)
print('\ndf_skip:\n')
print(df_skip)
print('\ndf_insert:\n')
print(df_insert)
print('\ndf_delete:\n')
print(df_delete)
print('\ndf_update:\n')
print(df_update)

df:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

df_skip:

   id column_one  column_two
0   2          b           4
1   3          c           6

df_insert:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6
3   5          e          10
4   6          f          12
5   7          g          14

df_delete:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6
3   6          f          12
4   7          g          14

df_update:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6
3   6         AA          12
4   7         AA          14

_build_query¶

Database._build_query(table, select='*', where=None, group_by=None, aggregate=None, aggregate_func='count', order_by=None, order_by_sort='asc', limit=None, offset=None, where_boolean='AND', update=False, delete=False, values=None, *args, **kwargs)[source]¶

Get a SQL select statement using sqlalchemy functions.

Parameters

table (str) – Name of the database table.
select (str or list(str) or list(sqlalchemy.schema.Column) or None) –
List of column names or a single column name to filter or select from the table.
- If None, columns will not be added to the select statement.
- If '*', then all columns will be selected
where (list of list or list of tuple or None) –
list of where statements the form of ['column_name', 'operator', value] to further filter individual values or rows.
- Operators are one of:
  = != > >= > < <= LIKE NOTLIKE ILIKE NOTILIKE CONTAINS STARTSWITH ENDSWITH
- Values can be any single value such as int or str
- Examples: ['column_two', '>', 2], ['column_one', 'LIKE', 'a'], [['column_two', '>', 2], ['column_one', 'LIKE', 'a']]
group_by (str or list(str) or None) – Single or list of column names to group by. This should be used with aggregate and aggregate_func.
aggregate (str or list(str) or None) – Single or list of column names to aggregate using the aggregate_func. This should be used with aggregate_func.
aggregate_func (str or list(str)) – Function name (such as ‘count’ or ‘sum’) from sqlalchemy.sql.functions.Function for aggregating records from each aggregate column. If a list of str, then it must have the same number of elements as aggregate or else only the shortest length list will be used.
order_by (str or list(str) or None) – Single or list of column names to order or sort by.
order_by_sort (str or list(str)) – Sort the records in increasing or decreasing order by each column in order_by, where the value can be one of ‘asc’ for ascending or ‘desc’ for descending’. If a list of str, then it must have the same number of elements as order_by or else only the shortest length list will be used.
limit (int or None) – Integer number to limit the number of rows returned.
offset (int or None) – Number of rows to skip for the query.
where_boolean (str) – One of AND or OR to combine where statements with. Defaults to AND if not one of AND or OR.
update (bool) – Whether to update rows from the table matching the query or not. Overrides the select parameter.
delete (bool) – Whether to delete rows from the table matching the query or not. Overrides the select and update parameters.
values (dict) – A dictionary of values to use for update if the update parameter is True and not overridden.
*args – Additional arguments to accept any extra parameters passed through.
**kwargs – Additional arguments to accept any extra parameters passed through.

Returns

sqlalchemy object that represents a table in the database.

Return type

sqlalchemy.sql.expression.Select

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database.core import Database

# Setup database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Select columns and limit to 6 rows
sql_limit = db._build_query(
    'test_table',
    select = ['column_one', 'column_two'],
    limit=6
)

# Select columns with where statement
sql_where = db._build_query(
    'test_table',
    select='column_one',
    where=[('column_two', '<', 3), ('column_one', '=', 'b')],
    where_boolean='AND'
)

# Select columns and order each column
sql_order = db._build_query(
    'test_table',
    select=['column_one', 'column_two'],
    order_by=['column_one', 'column_two'],
    order_by_sort=['asc', 'desc']
)

# Select columns, group, and aggregate data
sql_agg = db._build_query(
    'test_table',
    select='column_one',
    group_by='column_one',
    aggregate=['column_two', 'column_one'],
    aggregate_func=['sum', 'count']
)

# Update rows
sql_update = db._build_query(
    'test_table',
    where=[('id', '=', 1)],
    values=dict(column_one='A'),
    update=True
)

# Delete rows
sql_delete = db._build_query(
    'test_table',
    where=[('column_two', '<', 2), ('column_one', '=', 'a')],
    where_boolean='OR',
    delete=True
)

# Display gen statements
print('sql_limit:\n\n' + str(sql_limit))
print('\nsql_where:\n\n' + str(sql_where))
print('\nsql_order:\n\n' + str(sql_order))
print('\nsql_agg:\n\n' + str(sql_agg))
print('\nsql_update:\n\n' + str(sql_update))
print('\nsql_delete:\n\n' + str(sql_delete))

sql_limit:

SELECT test_table.column_one, test_table.column_two 
FROM test_table 
 LIMIT %(param_1)s

sql_where:

SELECT test_table.column_one 
FROM test_table 
WHERE test_table.column_two < %(column_two_1)s AND test_table.column_one = %(column_one_1)s

sql_order:

SELECT test_table.column_one, test_table.column_two 
FROM test_table ORDER BY test_table.column_one ASC, test_table.column_two DESC

sql_agg:

SELECT test_table.column_one, sum(test_table.column_two) AS column_two_sum, count(test_table.column_one) AS column_one_count 
FROM test_table GROUP BY test_table.column_one

sql_update:

UPDATE test_table SET column_one=%(column_one)s WHERE test_table.id = %(id_1)s

sql_delete:

DELETE FROM test_table WHERE test_table.column_two < %(column_two_1)s OR test_table.column_one = %(column_one_1)s

_execute_query¶

Database._execute_query(sql, *args, **kwargs)[source]¶

Executes a query statement.

Parameters

sql (str) – SQL str representing the query statement to execute.
*args – Additional parameters passed to sqlalchemy.engine.Connection.execute.
**kwargs – Additional parameters passed to sqlalchemy.engine.Connection.execute.

Returns

Cursor result from query.

Return type

sqlalchemy.engine.CursorResult

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database.core import Database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Write sample data
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db.insert('test_table', data)

# Get data from query
cursor = db._execute_query('SELECT * FROM test_table LIMIT 5;')
cursor = db._execute_query('SELECT column_one, column_two FROM test_table WHERE column_two > 3;')

_get_table¶

Database._get_table(table, *args, **kwargs)[source]¶

Get a table object from the database.

Parameters

table (str) – Name of the table to remove.
*args – Additional arguments passed to sqlalchemy.schema.Table.
**kwargs – Additional arguments passed to sqlalchemy.schema.Table.

Returns

A Table object from sqlalchemy.

Return type

sqlalchemy.schema.Table

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database.core import Database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Get the table object
tb = db._get_table('test_table')
print(str(tb))

test_table

_list_to_columns¶

Database._list_to_columns(clist)[source]¶

Converts a list of dict or list to sqlalchemy columns.

Parameters

clist (list(dict) or list(list)) –
List of dict (kwargs) or lists (positional args) that are passed to sqlalchemy.schema.Column. Data types can also be specified as str.
- Example list(dict): [dict(name='id', type_='Integer', autoincrement=True, primary_key=True), dict(name='col', type='String')]
- Example list(list): [('id', 'Integer'), ('col', 'String')]
*args – Additional arguments passed to sqlalchemy.schema.Table.
**kwargs – Additional arguments passed to sqlalchemy.schema.Table.

Returns

A list of Column objects from sqlalchemy.

Return type

list(sqlalchemy.schema.Column)

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database.core import Database
db = Database()

# Create column definitions
clist = [
    dict(name='id', type_='Integer', primary_key=True),
    dict(name='column_one', type_='String'),
    dict(name='column_two', type_='Integer')
]

# Convert to sqlalchemy column objects
columns = db._list_to_columns(clist)
print(str(columns))

[Column('id', Integer(), table=None, primary_key=True, nullable=False), Column('column_one', String(), table=None), Column('column_two', Integer(), table=None)]

_write_data¶

Database._write_data(table, data, schema=None, if_exists='append', index=False, *args, **kwargs)[source]¶

Write data to the database.

Parameters

table (str) – Name of the table to write to.
data (dict or list or pandas.DataFrame) – Dataframe with the data to write to the database. If dict or list see pandas.DataFrame.
schema (str) – Name of the schema to for the table.
if_exists ('fail' or 'replace' or 'append') – String indicating whether to throw an error (fail), replace the data, or append the data to the table if it already exists.
index (bool) – Set to True to include the row indices as a column and False to omit them.
*args – Additional arguments passed to pandas.DataFrame if parameter data is dict or list.
**kwargs – Additional arguments passed to pandas.DataFrame if parameter data is dict or list.

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database import Database

# Setup database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Write data to table
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db._write_data('test_table', data, if_exists = 'replace')

columns¶

Database.columns(table)[source]¶

Get number of columns for a table.

Parameters: table (str) – Name of the table to get columns for.

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database import Database

# Setup database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Write data to table
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db.insert('test_table', data)
df = db.select('test_table')

# Get number of rows in table
columns = db.columns('test_table')

# Display results
print('df:\n')
print(df)
print(f'\ncolumns: {columns}')

df:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

columns: 3

create_table¶

Database.create_table(table, columns)[source]¶

Create a table in the database.

Parameters

table (str) – Name of the table to create.
columns (list(dict) or list(list)) –
List of dict (kwargs) or lists (positional args) that are passed to sqlalchemy.schema.Column. Data types can also be specified as str.
- Example list(dict): [dict(name='id', type_='Integer', autoincrement=True, primary_key=True), dict(name='col', type='String')]
- Example list(list): [('id', 'Integer'), ('col', 'String')]

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database import Database

# Setup database
db = Database()

# Drop table if exists
if db.has_table('test_table'):
    db.drop_table('test_table')

# Create column definitions
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    dict(name='column_one', type_='String'),
    dict(name='column_two', type_='Integer')
]

# Create the table
db.create_table('test_table', columns)

# View the table
df = db.select('test_table')
print(df)

Empty DataFrame
Columns: [id, column_one, column_two]
Index: []

delete¶

Database.delete(table, where, where_boolean='AND', *args, **kwargs)[source]¶

Remove rows from a table in the database.

Parameters

table (str) – Name of the table to remove rows from.
where (list of list or list of tuple or None) – list of where statements the form of ['column_name', 'operator', value] to further filter individual values or rows. See parameter where in msdss_base_database.core.Database._build_query().
where_boolean (str) –
One of AND or OR to combine where statements with. Defaults to AND if not one of AND or OR.
- Operators are one of:
  = != > >= > < <= LIKE NOTLIKE ILIKE NOTILIKE CONTAINS STARTSWITH ENDSWITH
- Values can be any single value such as int or str
- Examples: ['column_two', '>', 2], ['column_one', 'LIKE', 'a'], [['column_two', '>', 2], ['column_one', 'LIKE', 'a']]
*args – Additional arguments passed to msdss_base_database.core.Database._execute_query().
**kwargs – Additional arguments passed to msdss_base_database.core.Database._execute_query().

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database.core import Database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Write sample data
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db.insert('test_table', data)
df = db.select('test_table')

# Delete a row with matching id
db.delete(
    'test_table',
    where=('id', '=', 1)
)
df_delete_id = db.select('test_table')

# Delete a row with matching query
db.delete(
    'test_table',
    where=[('id', '<', 3), ('column_one', '=', 'c')],
    where_boolean='OR'
)
df_delete_where = db.select('test_table')

# Print results
print('df:\n')
print(df)
print('\ndf_delete_id:\n')
print(df_delete_id)
print('\ndf_delete_where:\n')
print(df_delete_where)

df:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

df_delete_id:

   id column_one  column_two
0   2          b           4
1   3          c           6

df_delete_where:

Empty DataFrame
Columns: [id, column_one, column_two]
Index: []

drop_table¶

Database.drop_table(table, *args, **kwargs)[source]¶

Remove a table from the database.

Parameters

table (str) – Name of the table to remove.
*args – Additional arguments passed to sqlalchemy.schema.Table.drop().
**kwargs – Additional arguments passed to sqlalchemy.schema.Table.drop().

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database.core import Database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)
before_drop = db.has_table('test_table')

# Drop the table
db.drop_table('test_table')
after_drop = db.has_table('test_table')

# Print results
print('before_drop: ' + str(before_drop))
print('after_drop: ' + str(after_drop))

before_drop: True
after_drop: False

has_table¶

Database.has_table(table, *args, **kwargs)[source]¶

Check if a table exists.

Parameters

table (str) – Name of the table to check.
*args – Additional arguments passed to sqlalchemy.engine.reflection.Inspector.has_table().
**kwargs – Additional arguments passed to sqlalchemy.engine.reflection.Inspector.has_table().

Returns

Returns True if the table exists and False otherwise.

Return type

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database.core import Database
db = Database()

# Drop the table if it exists
if db.has_table('test_table'):
    db.drop_table('test_table')
before_create = db.has_table('test_table') # False

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Check if table exists again
after_create = db.has_table('test_table') # True

# Print results
print('before_create: ' + str(before_create))
print('after_create: ' + str(after_create))

before_create: False
after_create: True

insert¶

Database.insert(table, data, *args, **kwargs)[source]¶

Insert additional data to the database.

Parameters

table (str) – Name of the table to insert additional data to.
data (dict or list or pandas.DataFrame) – Dataframe with the data to write to the database. If dict or list see pandas.DataFrame.
*args – Additional arguments passed to msdss_base_database.core.Database._write(). Except that if_exists is always set to append.
**kwargs – Additional arguments passed to msdss_base_database.core.Database._write(). Except that if_exists is always set to append.

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database import Database

# Setup database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)
df = db.select('test_table')

# Write data to table
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db.insert('test_table', data)
df_insert = db.select('test_table')

# Insert more new values to table
more_new = {
    'id': [5, 6, 7],
    'column_one': ['e', 'f', 'g'],
    'column_two': [10, 12, 14]
}
db.insert('test_table', more_new)
df_insert_more = db.select('test_table')

# Display results
print('df:\n')
print(df)
print('\ndf_insert:\n')
print(df_insert)
print('\ndf_insert_more:\n')
print(df_insert_more)

df:

Empty DataFrame
Columns: [id, column_one, column_two]
Index: []

df_insert:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

df_insert_more:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6
3   5          e          10
4   6          f          12
5   7          g          14

rows¶

Database.rows(table)[source]¶

Get number of rows for a table.

Parameters: table (str) – Name of the table to get rows for.

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database import Database

# Setup database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Write data to table
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db.insert('test_table', data)
df = db.select('test_table')

# Get number of columns in table
rows = db.rows('test_table')

# Display results
print('df:\n')
print(df)
print(f'\nrows: {rows}')

df:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

rows: 3

select¶

Database.select(table, select='*', where=None, group_by=None, aggregate=None, aggregate_func='count', order_by=None, order_by_sort='asc', limit=None, offset=None, where_boolean='AND', *args, **kwargs)[source]¶

Query data from a table in the database.

Parameters

table (str) – Name of the database table to query from.
select (str or list(str) or list(sqlalchemy.schema.Column) or None) –
List of column names or a single column name to filter or select from the table.
- If None, columns will not be added to the select statement.
- If '*', then all columns will be selected
where (list of list or list of tuple or None) –
list of where statements the form of ['column_name', 'operator', value] to further filter individual values or rows.
- Operators are one of:
  = != > >= > < <= LIKE NOTLIKE ILIKE NOTILIKE CONTAINS STARTSWITH ENDSWITH
- Values can be any single value such as int or str
- Examples: ['column_two', '>', 2], ['column_one', 'LIKE', 'a'], [['column_two', '>', 2], ['column_one', 'LIKE', 'a']]
group_by (str or list(str) or None) – Single or list of column names to group by. This should be used with aggregate and aggregate_func.
aggregate (str or list(str) or None) – Single or list of column names to aggregate using the aggregate_func. This should be used with aggregate_func.
aggregate_func (str or list(str)) – Function name (such as ‘count’ or ‘sum’) from sqlalchemy.sql.functions.Function for aggregating records from each aggregate column. If a list of str, then it must have the same number of elements as aggregate or else only the shortest length list will be used.
order_by (str or list(str) or None) – Single or list of column names to order or sort by.
order_by_sort (str or list(str)) – Sort the records in increasing or decreasing order by each column in order_by, where the value can be one of ‘asc’ for ascending or ‘desc’ for descending’. If a list of str, then it must have the same number of elements as order_by or else only the shortest length list will be used.
limit (int or None) – Integer number to limit the number of rows returned.
offset (int or None) – Number of rows to skip for the query.
where_boolean (str) – One of AND or OR to combine where statements with. Defaults to AND if not one of AND or OR.
*args – Additional parameters passed to pandas.read_sql.
**kwargs – Additional parameters passed to pandas.read_sql.

Returns

pandas dataframe containing the queried data.

Return type

pandas.DataFrame

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database.core import Database

# Setup database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Write sample data
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db.insert('test_table', data)
df = db.select('test_table')

# Read data from table using select and limit
data = db.select(
    'test_table',
    select = ['column_one', 'column_two'],
    limit = 5
)
df_select = db.select('test_table')

# Use where statements
data = db.select(
    'test_table',
    select = 'column_one',
    where = [('column_two', '<', '15'), ('column_one', '=', 'b')],
    where_boolean = 'AND'
)
df_where = db.select('test_table')

# Order results
data = db.select(
    'test_table',
    select = ['column_one', 'column_two'],
    order_by = ['column_one', 'column_two'],
    order_by_sort = ['asc', 'desc']
)
df_order = db.select('test_table')

# Aggregate read
data = db.select(
    'test_table',
    select = 'column_one',
    group_by = 'column_one',
    aggregate = 'column_two',
    aggregate_func = ['count', 'sum']
)
df_agg = db.select('test_table')

# Display results
print('df:\n')
print(df)
print('\ndf_select:\n')
print(df_select)
print('\ndf_where:\n')
print(df_where)
print('\ndf_order:\n')
print(df_order)
print('\ndf_agg:\n')
print(df_agg)

df:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

df_select:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

df_where:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

df_order:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

df_agg:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

update¶

Database.update(table, where, values, *args, **kwargs)[source]¶

Update a table from the database.

Parameters

table (str) – Name of the table to update.
where (list of list or list of tuple or None) –
list of where statements the form of ['column_name', 'operator', value] to update individual values or rows.
- Operators are one of:
  = != > >= > < <= LIKE NOTLIKE ILIKE NOTILIKE CONTAINS STARTSWITH ENDSWITH
- Values can be any single value such as int or str
- Examples: ['column_two', '>', 2], ['column_one', 'LIKE', 'a'], [['column_two', '>', 2], ['column_one', 'LIKE', 'a']]
values (dict) – Dictionary representing values to update if they match the where parameter requirements.
*args – Additional arguments passed to msdss_base_database.core.Database._execute_query().
**kwargs – Additional arguments passed to msdss_base_database.core.Database._execute_query().

Author

Richard Wen <rrwen.dev@gmail.com>

Example

from msdss_base_database import Database

# Setup database
db = Database()

# Check if the table exists and drop if it does
if db.has_table("test_table"):
    db.drop_table("test_table")

# Create sample table
columns = [
    dict(name='id', type_='Integer', primary_key=True),
    ('column_one', 'String'),
    ('column_two', 'Integer')
]
db.create_table('test_table', columns)

# Write sample data
data = {
    'id': [1, 2, 3],
    'column_one': ['a', 'b', 'c'],
    'column_two': [2, 4, 6]
}
db.insert('test_table', data)
df = db.select('test_table')

# Update values in table
db.update(
    'test_table',
    where=('id', '>', 1),
    values={'column_one': 'AA'})
df_update = db.select('test_table')

# Print results
print('df:\n')
print(df)
print('\ndf_update:\n')
print(df_update)

df:

   id column_one  column_two
0   1          a           2
1   2          b           4
2   3          c           6

df_update:

   id column_one  column_two
0   1          a           2
1   2         AA           4
2   3         AA           6