Creating Queries

The main purpose of py-dbcn is to create some kind of database queries to read from or manipulate one or more database. The syntax is meant to be as intuitive as possible, with the package taking care of as much work as it can, behind the scenes.

For example, when providing quotes to a value, the quote type does not matter. Py-dbcn will automatically convert the quotes to the expected type, depending on the database used. All of [”, ‘, `] will be treated equally.

For now, the py-dbcn package only provides basic database calls, and these are provided on the database, table, and record levels.

Note

When applicable, these interface methods provide a result that makes sense for the given query type. The intention is to allow further Pythonic logic to be run, if desired. If nothing else, it allows for verifying the query result in whatever way desired.

For methods that return None, it is safe to assume the query will either succeed, or return a Python error.

Important

While not fully implemented yet, the intention is to also have full, dynamic validation of all values provided to these interface methods.

In the future, the py-dbcn package will do what it can to auto-format and auto-correct common mistakes in provided syntax. When it can’t correct, it will return a descriptive error that explains the problem, before the query ever hits the database itself.

Database Querying

All database-level querying is done via the connector.database interface.

SHOW Existing Databases

connector.database.show()

return

A list of all found databases.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
list_of_databases = connector.database.show()

# Print list of databases.
for database in list_of_databases:
    print(database)

Display current Database

connector.database.select()

connector.database.current()

return

Str of currently selected database name.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
current_db = connector.database.select()

# Print name of current database.
print(current_db)

USE a different Database

connector.database.use(db_name)

param db_name

Name of database to change to.

return

None

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
connector.database.use('test_db')

CREATE a new Database

connector.database.create(db_name)

param db_name

Name of database to create.

return

None

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
connector.database.create('a_new_database')

DELETE an existing Database

connector.database.delete(db_name)

connector.database.drop(db_name)

param db_name

Name of database to delete.

return

None

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
connector.database.drop('an_old_database')

Table Querying

All table-level querying is done via the connector.table interface.

SHOW Existing Tables

connector.tables.show()

return

A list of all found tables in currently selected database.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
list_of_tables = connector.tables.show()

# Print list of tables.
for table in list_of_tables:
    print(table)

DESCRIBE existing Table

connector.tables.describe(table_name)

param table_name

Name of table to describe.

return

A list of all columns in table, including several useful attributes.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
list_of_columns = connector.tables.describe('my_table')

# Print list of table columns.
for column in columns:
    print(column)

CREATE a new Table

connector.tables.create(table_name, table_columns)

param table_name

Name of table to create.

param table_columns

The columns to provide the new table.

return

None

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
connector.tables.create(
    'a_new_table',
    """
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    description VARCHAR(100),
    PRIMARY KEY ( id )
    """,
)

UPDATE an existing Table

connector.tables.update(table_name, table_columns)

connector.tables.modify(table_name, table_columns)

param table_name

Name of table to create.

param modify_clause

The type of modification to make. IE: One of [ADD, DROP, MODIFY].

param column_clause

The columns to modify.

return

None

TODO: Create examples and expand modify logic. Maybe not fully implemented?

DROP an existing Table

connector.tables.drop(table_name)

connector.tables.delete(table_name)

param table_name

Name of table to remove.

return

None

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
connector.tables.drop('old_table')

COUNT records present in table

connector.tables.count(table_name)

param table_name

Name of table to count records of.

return

A count of records present in table.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
record_count = connector.tables.count('my_table')

# Print number of records found.
print(record_count)

Record Querying

All record-level querying is done via the connector.record interface.

SELECT a set of Records

connector.records.select(table_name, select_clause=None, where_clause=None, order_by_clause=None, limit_clause=None)

param table_name

Name of table to select records from.

param select_clause

Optional clause to limit the number of columns that return for each record. If not provided, * wildcard selector is used.

Can be in format of a list, or comma separated str.

param where_clause

Optional clause to limit number of records selected. If not provided, then all records are selected.

param order_by_clause

Optional clause to indicate the desired ordering of returned records.

param limit_clause

Optional clause to limit query scope via number of records returned.

return

A list of all returned records.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
# In this case, we only pull "id", "name", and "description" columns from
# all records with an "id" less than 500. We then sort by descending name,
# and then ascending id. Finally, we limit to the first 100 records.
results = connector.records.select(
    'my_table',
    select_clause='id, name, description',
    where_clause='id < 500',
    order_by_clause='name DESC, id',
    limit_clause=100,
)

# Print list of pulled records.
for record in results:
    print(record)

INSERT new Records

INSERT single Record

connector.records.insert(table_name, values_clause, columns_clause=None)

param table_name

Name of table to select records from.

param values_clause

Clause to provide values for new record.

param columns_clause

Optional clause to indicate what columns are being provided, as well as what order they’re in. If not present, then query will assume all columns are being provided, in the order they were originally added to the table.

return

TODO: Honestly unsure of what this provides. Probably empty array? Double check.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
# In this case, we insert a record by giving a "name" and "description".
# We omit "id" so it will be auto-provided (assuming it's a field with
# some kind of reliable default value).
connector.records.insert(
    'my_table',
    "Regal Red Towel", "Red towel with yellow embroidery."',
    columns_clause='name, description',
)

INSERT multiple Records

connector.records.insert_many(table_name, values_clause, columns_clause=None)

param table_name

Name of table to select records from.

param values_clause

Clause to provide values for new record(s).

param columns_clause

Optional clause to indicate what columns are being provided, as well as what order they’re in. If not present, then query will assume all columns are being provided, in the order they were originally added to the table.

return

None

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Generate new record values.
rows = [
    ('Blue Towel', 'Soft blue towel.'),
    ('Red Towel', 'Soft red towel.'),
    ('Regal Red Towel', 'Red towel with yellow embroidery.'),
]

# Run query.
# In this case, we insert records by giving a set of "name"s and "description"s.
# We omit "id" so it will be auto-provided (assuming it's a field with
# some kind of reliable default value).
connector.records.insert_many(
    'my_table',
    rows,
    columns_clause='name, description',
)

UPDATE existing Records

UPDATE Single Record

connector.records.update(table_name, values_clause, where_clause)

param table_name

Name of table to update records within.

param values_clause

Clause to provide values for updated record.

param where_clause

Clause to limit number of records selected.

Due to the nature of this query, where clause is mandatory BUT if an empty string is provided, then the query will still select all records.

return

Returns set of all updated values.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
# In this case, we insert a record by giving a "name" and "description".
# We omit "id" so it will be auto-provided (assuming it's a field with
# some kind of reliable default value).
connector.records.update(
    'my_table',
    'description = "Refurbished item"',
    'name = "Refurbished"',
)

UPDATE Multiple Records

connector.records.update_many(table_name, columns_clause, values_clause, where_columns_clause)

param table_name

Name of table to update records within.

param columns_clause

Clause to indicate what columns are being provided, as well as what order they’re in.

param values_clause

Clause to provide values for updated record.

Columns indicated in the below where_columns_clause should match values already present in the database.

All other columns will update to the new values provided here.

param where_columns_clause

NOT the standard WHERE clause used in other queries.

Clause to indicate what columns are being filtered on. All columns present here should also be present in the columns_clause param.

param column_types_clause

Optional clause to provide type hinting to column types.

Can be skipped when all columns are basic types, such as text or integer.

return

None

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Columns to update with/on.
columns_clause = ['id', 'name']

# Type hinting for columns.
# Note: This is NOT required in this instance, and only shown here for
# example. But this IS required in instances with more complicated data
# types, such as dates or timestamps.
column_types_clause = ['integer', 'varchar(200)']

# Desired values after query runs.
# Note: In this case, we match on "id" by including it in the
# WHERE clause. Meanwhile, the "name" field is not included in the
# WHERE, so this field is set to the desired update value. Any fields
# that are not used to match/update should be excluded.
values_clause = [
    # (id, name)
    (1599, 'Refurbished Item 1001'),
    (16782, 'Refurbished Item 1002'),
    (20909, 'Refurbished Item 1003'),
]

# Columns to use to find existing record data.
where_columns_clause = ['id']

# Run query.
# In this case, we update record "name" values, using the id to match
# against existing records.
connector.records.update(
    'my_table',
    columns_clause,
    values_clause,
    where_columns_clause,
    column_types_clause=column_types_clause,
)

DELETE existing Records

connector.records.delete(table_name, where_clause)

param table_name

Name of table to delete records from.

param where_clause

Clause to limit number of records selected.

Due to the nature of this query, where clause is mandatory BUT if an empty string is provided, then the query will still select all records.

return

TODO: Honestly unsure of what this provides. Double check.

Example:

# Import MySQL connector.
from py_dbcn.connectors import MysqlDbConnector

...

# Initialize MySQL database connection.
connector = MysqlDbConnector(host, port, user, password, db_name)

# Run query.
connector.records.delete(
    'my_table',
    where_clause='name = "Refurbished"',
)