SQLite is a lightweight database that is very useful anywhere if you're data wrangling or being a developer. The package for Python that interfaces with it is sqlite3, and you'll usually find it already installed in your Python 3.
I found myself recently doing a developer challenge where I had to do a lot of wrangling with data. I needed a little database to hold my info for a short while, with an easy interface. I was already in Windows and using Python to manipulate the source data, so a sqlite database and the Python sqlite3 package was the obvious solution. This guide is for people using Windows 10 (64 bit) and Python 64-bit also.
Install the SQLite database and tools
Go to the SQLite website and download the zipped files for: "sqlite-dll-win64-x64" and "sqlite-tools".
https://sqlite.org/download.html
Unzip both these once they've downloaded.
Make a new folder somewhere handy on your machine, e.g. C:\Sqlite.
Copy all the files from the two unzipped folders into your new folder.
Add the new folder with your SQLite files to your Environment Variable, Path.
Test that it works
Open a command shell (just plain "cmd")
type:
sqlite3
You should see the sqlite shell start.
Create a new database, specifying the path where it is to be stored and the database's name, e.g.
.open C:/Users/scott.davies/Documents/ws/py/team_loki_ml/data/ml_loki.db
- note, you will need to convert backslashes from Windows file paths to forward slashes
- afterwards, you should see the database file created if you look in Windows Explorer
Try some sqlite commands in the shell, like creating a new table and inserting a record into it:
create table config (
id integer primary key autoincrement not null,
item nvarchar(255),
value nvarchar(255)
);
insert into config
(item,
value)
values
('dummy',
'test');
Write some sqlite3 code in Python
Find a trusty text editor (I'm using Sublime) and create a new Python code file.
Try come CRUD queries on the database table you created earlier (Create, Update, Insert Delete). There are all sorts of features in the below example you can read about in tutorials on the web, like what a cursor is, and the bind parameters to put in query strings. Also note that:
- modify queries have "connection.commit()" afterwards, so their effect on the database will be immediate
- at then end, we need to close down the cursor and the database connection.
Below is the Python code file I am naming "sqlite_test1.py".
import sqlite3
# Created a sqlite DB file & table first
# create table config (
# id integer primary key autoincrement not null,
# item nvarchar(255),
# value nvarchar(255)
# );
def selectQuery(cursor):
q = "SELECT id, item, value FROM config WHERE item = ?"
params = ("my_config_item",)
rows = cursor.execute(q, params).fetchall()
print(rows)
if __name__ == "__main__":
connection = sqlite3.connect("C:/Users/scott.davies/Documents/ws/py/team_loki_ml/data/ml_loki.db")
print(connection.total_changes)
cursor = connection.cursor()
q = """insert into config
(item,
value)
values
(?,
?)""";
params = ("my_config_item", "a value",)
result = cursor.execute(q, params)
connection.commit()
print("cursor.lastrowid: " + str(cursor.lastrowid))
selectQuery(cursor)
q = "update config set value = ? where item = ?"
params = ("updated", "my_config_item")
cursor.execute(q, params)
connection.commit()
selectQuery(cursor)
q = "delete from config where id > 0"
cursor.execute(q, ())
connection.commit()
cursor.close()
connection.close()
Run the code file in a command shell (make sure you have changed directory into the right place first):
python sqlite_test1.py
You should see queries like the ones in my example above working and printing out output.
Make it object-oriented
Next, I found it might be a good idea to note write such a rough script from now on to try things out, especially since I needed to wrangle some data in a manageable fashion. I decided to (a) make a folder named "lib" to put modules in, (b) make a class called "DBConnector" to handle SQLite database query calls for me, and (c) make a script to create the queries, and use the DBConnector. My tiny project' structure looks like this:
├── lib
│ ├── DBConnector.py
│ └── __init__.py
└── sqlite_test2.py
Here is the code for DBConnector.py:
"""
A module for connecting to a SQLite database.
"""
import sqlite3
class DBConnector:
def __init__(self, dbLocation):
"""Constructor:
:param dbLocation: a string to specify the path to the database file.
"""
self.connection = sqlite3.connect(dbLocation)
self.cursor = self.connection.cursor()
def insert(self, q, params):
"""
Runs an insert query
:param q: the database query string.
:param params: a list of any bound parameters to be inserted into the query string.
:return result: a set of info from running the query
:rtype: dict
"""
result = {"data": [], "errors": ""}
try:
self.cursor.execute(q, params)
self.connection.commit()
result["data"].append("success")
except Exception as e:
result["errors"] = str(e)
return result
def selectMany(self, q, params):
"""
Runs a select query
:param q: the database query string.
:param params: a list of any bound parameters to be inserted into the query string.
:return result: a set of info from running the query
:rtype: dict
"""
result = {"data": [], "errors": ""}
try:
result["data"] = self.cursor.execute(q, params).fetchall()
except Exception as e:
result["errors"] = str(e)
return result
def update(self, q, params):
"""
Runs an update query.
:param q: the database query string.
:param params: a list of any bound parameters to be inserted into the query string.
:return result: a set of info from running the query
:rtype: dict
"""
result = {"data": [], "errors": ""}
# You could do some validating of the query and paramters in places like this...
try:
self.cursor.execute(q, params)
self.connection.commit()
result["data"].append("success")
except Exception as e:
result["errors"] = str(e)
return result
def delete(self, q, params):
"""
Runs a delete query
:param q: the database query string.
:param params: a list of any bound parameters to be inserted into the query string.
:return result: a set of info from running the query
:rtype: dict
"""
result = {"data": [], "errors": ""}
try:
self.cursor.execute(q, params)
self.connection.commit()
result["data"].append("success")
except Exception as e:
result["errors"] = str(e)
return result
And here is my script "sqlite_test2.py" for calling it:
from lib.DBConnector import DBConnector
if __name__ == "__main__":
dbFileLocation = "C:/Users/scott.davies/Documents/ws/py/team_loki_ml/data/ml_loki.db"
conn = DBConnector(dbFileLocation)
# Insert query
q = """insert into config
(item,
value)
values
(?,
?)""";
params = ("data_dir", "C:/Users/scott.davies/Documents/VBoxShared/202102/hackathon_data_2021",)
result = conn.insert(q, params)
print(result)
# Select query
q = "SELECT id, item, value FROM config WHERE item = ?"
params = ("data_dir",)
result = conn.selectMany(q, params)
print(result)
# Update query
q = "update config set value = ? where item = ?"
params = ("updated", "data_dir")
result = conn.update(q, params)
print(result)
# Delete query
q = "delete from config where id > ?"
params = (0,)
result = conn.delete(q, params)
print(result)
I will run the script like:
python "sqlite_test2.py"
And the output, as a result of the Python sqlite3 queries on my database will look like:
No comments:
Post a Comment