JDatabase (JDB)

jdatabase package by Joshua Widrick

Documentation: docs.jwid.co
GitHub (source code): GitHub.com/JoshWIdrick/jdb
Version: 1.1.1-alpha3
License: MIT

Function / Overview:

The function of the jdatabase package is to allow easy and fluid connection, control, and use of MySQL, and PostgreSQL database systems through one easy to use, concurrent format. The package also allows for logging of data transactions, allowing for database roll-back.
The development of this package has been solely for use in many of my other projects. This package has a lot of default functionality that a normal user will not need. Any feature that you do not need, you can ignore (however understanding it is recommended).

Installation:

The jdatabase package is available publicly through PyPi / pip, so all you need to do is sudo pip install jdatabase. The package can be updated with sudo pip install jdatabase --upgrade.
From source, run sudo python setup.py install.

Instantiation:

The instantiation of the Jdatabase object requires a host, user, password(passwd), and database name(db). The optional arguments are charset, which defaults to "utf8"; port, which defaults to 3306; ssl, which defaults to True; and autocommit, which defaults to True.

from jdatabase import Jdatabase
jdb = Jdatabase(host="db_hostname", user="db_username", passwd="db_password", db="db_name" )

Table Methods:

get_table_names()

Method to get the names of all the tables in the connected database.
Returns a list of str table names.

jdb.get_table_names()

Use of this method also updates self.table_names and self.stable_names for self use.

output:

['SYSTEM_TABLE', 'table_name', 'table_name2', ...]

get_cleaned_table_names()

Method to get the names of all non-system tables in the connected database.
Returns a list of str table names.

jdb.get_cleaned_table_names()

This method DOES NOT update self.table_names and self.stable_names.

output:

['table_name', 'table_name2', ...]

check_for_table(name)

Method to check for a table, named name, in the database.
Returns True if table found, False if not.

jdb.check_for_table("table_name")

create_table(name, {column_name:[parms]})

create_table_if_false_check() is recommended for all table creation.

Creates a table in the database.
Returns the rowcount for the query, should be 0.

jdb.create_table("table_name", {"jd":["VARCHAR(128)", "PRIMARY KEY"], "column_name":["DATATYPE", "DEFAULT VALUE"]})

# with auto primary key insertion
jdb.create_table("table_name", {"column_name":["DATATYPE", "DEFAULT VALUE"], "column2_name":["DATATYPE", "DEFAULT VALUE"]})

The jdatabase package automatically adds a jd column as the primary key column (if a primary key column is not included).

The recommend DEFAULT VALUE is NOT NULL.

create_table_if_not_exists(name, {column_name:[parms]})

create_table_if_false_check() is recommended for all table creation.

Creates a table in the database, if the table name is not present in the database, with database level existence check.
Returns the rowcount for the query, should be 0.

jdb.create_table_if_not_exists("table_name", {"jd":["VARCHAR(128)", "PRIMARY KEY"], "column_name":["DATATYPE", "DEFAULT VALUE"]})

# with auto primary key insertion
jdb.create_table_if_not_exists("table_name", {"column_name":["DATATYPE", "DEFAULT VALUE"], "column2_name":["DATATYPE", "DEFAULT VALUE"]})

The jdatabase package automatically adds a jd column as the primary key column (if a primary key column is not included).

The recommend DEFAULT VALUE is NOT NULL.

create_table_if_false_check(name, {column_name:[parms]})

Creates a table in the database, if the table name is not present in the database, with a query call existence check.
Returns the rowcount for the query, should be 0.

jdb.create_table_if_false_check("table_name", {"jd":["VARCHAR(128)", "PRIMARY KEY"], "column_name":["DATATYPE", "DEFAULT VALUE"]})

# with auto primary key insertion
jdb.create_table_if_false_check("table_name", {"column_name":["DATATYPE", "DEFAULT VALUE"], "column2_name":["DATATYPE", "DEFAULT VALUE"]})

The jdatabase package automatically adds a jd column as the primary key column (if a primary key column is not included).

The recommend DEFAULT VALUE is NOT NULL.

Data Methods:

get_one(name, [fields], (where, [parms]), (order, parms))

Gets one row of data from the table in the connected database, named name.
Returns the row of data.

row = jdb.get_one("table_name", ["field1", "field2"])

# hard-coded condition
row = jdb.get_one("table_name", where=("jd=a1"))

# condition
row = jdb.get_one("table_name", where=("jd=%s", ["jd_val"]))

# extended condition
row = jdb.get_one("table_name", where=("jd=%s and column1=%s", ["jd_val", "column1_val"]))

# ordered by DESC
row = jdb.get_one("table_name", order=("field", "DESC"))

Only the name value is required for get_one().

output:

("jd", "col1val", "col2val", ...)

get_all(name, [fields], (where, [parms]), (order, parms))

Gets all of the data from the table in the connected database, named name.
Returns the data.

row = jdb.get_all("table_name", ["field1", "field2"])

# hard-coded condition
row = jdb.get_all("table_name", where=("jd=a1"))

# condition
row = jdb.get_all("table_name", where=("jd=%s", ["jd_val"]))

# extended condition
row = jdb.get_all("table_name", where=("jd=%s and column1=%s", ["jd_val", "column1_val"]))

# ordered by DESC
row = jdb.get_all("table_name", order=("field", "DESC"))

Only the name value is required for get_all().

output:

(("jd", "col1val", "col2val", ...),
 ("jd", "col1val", "col2val", ...), 
 ...)

insert(name, {data})

Inserts a row of data into the table, named name, in the connected database.
Returns the rowcount for query.

jdb.insert("table_name", {"column1name": val, "column2name": xval})

vals should be the same type as the column in the table.

insert_batch(name, [{data1}, {data2}])

Inserts a batch of data into the table, named name, in the connected database.
Returns rowcount for query.

jdb.insert("table_name", [{"column1name": val, "column2name": xval}, {"column1name": val2, "column2name": xval2}])

vals should be the same type as the column in the table.

update(name, {data}, (where))

Updates data in the table, named name, in the connected database.
Returns rowcount for query.

jdb.update("table_name", {"column1name": val, "column2name": xval}, where=("column1name=%s", ["row_val"]))

vals should be the same type as the column in the table.

insert_or_update(name, {data}, key)

Insert data into or updates the data in the table, named name, in the connected database using a column, key, as a key for the comparision check between the parameter data and the data in the table.
Returns rowcount for query.

jdb.insert_or_update("table_name", {"column1name": val, "column2name": xval}, "column1name")

vals should be the same type as the column in the table.

delete(name, (where))

Delete row(s) in the table, named name, in the connected database, based on where condition.
Returns rowcount for query.

# delete entire table
jdb.delete("table_name")

# delete with where condition
jdb.delete("table_name", where=("jd=%s", ["val"]))

last_id()

Gets the last insert id.
Returns the last insert id.

jdb.last_id()

last_query()

Gets the last executed query.
Returns the last executed query.

jdb.last_query()

Class Methods:

connect()

Method to establish a connection to the database. Automatically run on instantiation.
Returns the database type of either MySQL or PostgreSQL in the form of a str.

jdb.connect()

is_open()

Method to check if the connection object's connection to the database is open.
Returns True if the connection is open, False if not.

jdb.is_open()

is_connected()

Method to check if the database is open and if not if there is a connection error.
Returns True if the connection is open, or if it was reestablished, or the connection error.

jdb.is_connected()

query(sql, [parms])

Method to execute a raw SQL query, with parms replacing %ss in the sql.
Returns the cursor object.

jdb.query("SELECT * FROM table_name WHERE %s=%s;", ['col1','select_me'])

parms are NOT required.

parms are required for any variable use, f"" strings DO NOT work.

commit()

Method to commit all current pending changes to the database. This method is only needed when autocommit is set to False in instantiation.

jdb.commit()

close()

Method to close the connection to the database.

jdb.close()

reconnect()

Method to close the connection to the database, if it is open, and then reopen the connection.

jdb.reconnect()

__str__

Returns the name of the database that the jdatabase object is connected to.

str(jdb)

output:

"database_name"

Footnote:

This package was inspired by my need for an easier way to interact with databases in Python, and the simplemysql package.