-
-
Save anilktechie/5f38f20ebb1506801b7c73bf69c77ee5 to your computer and use it in GitHub Desktop.
Python SQLite Wrapper Class
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import sqlite3 | |
| # A Self Contained Database Wrapper written for Admiral | |
| class Database: | |
| # Note: Primary Key ID's in SQLite start autoincrementing at 1 | |
| def __init__(self, name=None): | |
| self.conn = None | |
| self.cursor = None | |
| if name: | |
| self.open(name) | |
| def __enter__(self): | |
| return self | |
| def __exit__(self,exc_type,exc_value,traceback): | |
| self.close() | |
| def open(self,name): | |
| try: | |
| self.conn = sqlite3.connect(name); | |
| self.cursor = self.conn.cursor() | |
| except sqlite3.Error as e: | |
| print("Error connecting to database!") | |
| def close(self): | |
| '''Always remember to close properly for changes to be saved.''' | |
| if self.conn: | |
| self.conn.commit() | |
| self.cursor.close() | |
| self.conn.close() | |
| def init(self,schema): | |
| '''Initializes the sqlite database using a schema file''' | |
| with open(schema) as fp: | |
| self.cursor.executescript(fp.read()) | |
| def get_col(self,table,column,row): | |
| '''Gets and returns a single piece of data from the DB given: | |
| table = Name of the table | |
| column = Name of the column being read | |
| row = The number of the row (Primary Key ID) | |
| ''' | |
| query = "SELECT %s FROM %s WHERE id = %s;" % (column, table, row) | |
| self.cursor.execute(query) | |
| data = list(self.cursor.fetchone()) | |
| return data[0] | |
| def set_col(self,table,column,row,data): | |
| '''Sets a single piece of data from the DB given: | |
| table = Name of the table | |
| column = Name of the column being read | |
| row = The number of the row (Primary Key ID) | |
| data = The data to be written to this space''' | |
| query = "UPDATE %s SET %s = %s WHERE id = %s;" % (table, column, data, row) | |
| self.cursor.execute(query) | |
| def get_row(self,table,row): | |
| '''Gets and returns an entire row (in a list) of data from the DB given: | |
| table = Name of the table | |
| row = The number of the row (Primary Key ID)''' | |
| query = "SELECT * FROM %s WHERE id = %s;" % (table, row) | |
| self.cursor.execute(query) | |
| data = list(self.cursor.fetchone()) | |
| return data | |
| def set_row(self,table,cols,data): | |
| '''Sets a brand new row of data from the DB given: | |
| table = Name of the table | |
| cols = A list of the names of the columns in the row | |
| data = A list of the new values to be written''' | |
| query = "INSERT INTO %s " % table | |
| query += "(" | |
| for value in cols: | |
| query += ("%s, " % (value)) | |
| query = query[:-2] + ") VALUES (" | |
| for value in data: | |
| query += ("'%s', " % (value)) | |
| query = query[:-2] + ");" | |
| print(query) | |
| self.cursor.execute(query) | |
| def overwrite_row(self,table,cols,data,row): | |
| '''Overwrites a whole row of data from the DB given: | |
| table = Name of the table | |
| cols = A list of the names of the columns in the row | |
| data = A list of the new values to be written | |
| row = The number of the row (Primary Key ID)''' | |
| query = "UPDATE %s SET " % table | |
| for x in range(0, len(cols)): | |
| query += ("%s='%s', " % (cols[x],data[x])) | |
| query = query[:-2] + (" WHERE id = %s" % (row)) | |
| print(query) | |
| self.cursor.execute(query) | |
| def query(self,sql): | |
| '''Executes a query based on a passed SQL Query: | |
| sql = A properly formatted SQL Query String''' | |
| self.cursor.execute(sql) | |
| def queryWithReturn(self,sql): | |
| '''Executes a query and returns list of tuples based on a passed SQL Query: | |
| sql = A properly formatted SQL Query String''' | |
| self.cursor.execute(sql) | |
| data = list(self.cursor.fetchall()) | |
| return data |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment