Things Evolved
Since that first post things have evolved a bit.
After looking through the other pypi packages similar to this one i found two that peaked my interest:
The first of the two had a bunch of convenience functions that i was missing, so i added those.
The other one was more interesting however. SQLAlchemy is a large package, that can map sql-databases to classes. I have no intention on this package becoming anywhere near as comprehensive as that. The focus of sqlite-integrated is to provide provide a way to quickly write a script to alter an sqlite database. SQLAlchemy does however have a pretty cool way of doing queries with method chaining. I did not know about method chaining until i watched this video by mCoding which coincidentally also used SQLAlchemy queries as an example. The elegance of making queries like this was just amazing, and i wanted to implement something similar in sqlite-integrated.
This is what i am talking about:
query = SELECT(cols).FROM(table).WHERE(some condition)
This would provide a much more flexible way of extracting data from the database easily.
Therefore i created the Query class.
The Query Class
The idea was to have a query class with all the normal SQL commands as methods, that return itself. These methods should alter an internal SQL-string. To keep track of what methods had been called, each query keeps a history of the methods called on it. This history is also used to make sure that the methods are called in the correct order.
Running a Query on a Database
To run the query on a database, i created a method simply called run. To run the query and retrieve the results. This is how you would do it (db being the Database object):
Query().SELECT(cols).FROM(table).WHERE(condition).run(db)
Having to initialize a query like that is not that nice, especially if you are only working with one database. Therefore, i had the idea of attaching a database to a query, so it would know what database to run itself on. This turned out great and resulted in the syntax below.
db.SELECT(cols).FROM(table).WHERE(condition).run()
I am quite pleased with this. You can still use the other way of making an unattached query and running it on one or multiple databases.
Limitations of this Implementation
Ugly Database Methods
To be able to call query commands such as SELECT or UPDATE on a database, the database needs to include these methods. I don’t think there is a way around it, but i don’t like the idea of having query methods in the Database class.
Code Completion
Because all the SQL-commands are included in the Query class, code autocompletion engines show all the commands as methods. This is not the best as the methods must be called in a specific order, otherwise an exception will be thrown. You can for example not write a query like this:
Query().FROM(table).SELECT(cols).LIKE(something)
I may try to fix this in the future, maybe by splitting the Query class into a bunch of smaller ones that only include the valid methods, but this will do for now.
Missing JOIN
I am sure there are many more, but the most important one is the JOIN SQL command. I hope i get around to it at some point.