Pythonic SQL: Using SQL via Python
SQL is a pretty old database language that is still actively used in the relational database types. The provided language set is definitely easy to understand and for the small use-cases can be also quite easy to apply. For more complex use-cases, the connection of the joins can make the whole thing less comprehensible, however, I believe if you give enough time for this language, you will probably write your scripts after a day. Nevertheless, the script can be more intuitive for the other programming languages. In [1] given object-relational-mapping models (ORM) such as peepe, sqlacademy, django ORM can provide a flexible approach while dealing the database, however, the learning duration is mostly longer and for operating a simple SQL query takes unnecessary complexity and time.
On the one hand, the provided API should be not be complex, on the other hand minimize the required effort for SQL language and decrease its usage as much as possible. The separation of the concepts can lead to more creativity rather than focusing on many areas at the same time.
In this tutorial, I attempted at creating some python classes to write SQL script without using it. Even though the current state of the code does not cover all features of SQL languages, the existing features are enough sufficient to realize many use-cases. For this reason, if anyone who does not interest in SQL and wishes to spend less time, can directly profit from the new wrapper classes.
The functions of the classes are quite intuitive, this is at least what I believe or want to believe. If you do not find them enough efficient or logical, just allow me to know what features can be improved. You can even fork it in GitHub, and then customize the functions names for your use case, as long as you keep the function body same.
In order to demonstrate the new concept, I followed all necessary steps in [1] nicely presented website that clearly show how to use mysql-connector to interact with the database. The implemented codes are constructed on top of the maturated mysql-connector library.
In the following of the tutorial, first the development environment is introduced, and then all crucial SQL commands with the new pythonic interpretation are presented.
Preparation of the development environment on Mac OSX
- Find a rich python editor: Pycharm
- Install a mysql server: “brew install mysql-server”
- For other OSes, there are a number of ways to do it.
- Install mysql-connector library on PyCharm by following the path: Settings>Python Interepreter> add library
- Create a database: [1] shows already how to create it, therefore I recommend applying the instruction there to create the database. However, I will later create another class that can modify the database structure
Create Database Tables
Figure 1: Schema Diagram for an Online Movie Rating System [1]
Our goal is to create a database that behaves like an online movie rating system as depicted in the Figure 1. The usual method of doing it is to either write the database tables via SQL commands or employ an ORM model. A simple approach by mixing two SQL and ORM worlds, at least their concepts, allow us to write the following code for creating the aforementioned database schema. Firstly, a Create object is generated with the database configuration. Afterwards all details of the table such as its name, required columns, their data types and limits, primary key as well as foreign keys are added through the related methods. Notice that the primary key can receive also auto_increment option, if it is not given, then the auto_increment will be disregarded. The same create object can be consumed multiple times for creating other tables as well, since it removes all entered data except the database configuration after the execution.
create = Create(“arduino”, “arduino”, “127.0.0.1”, “university”)
create.table(“reviewers”).\
column(“id”, DataTypes.int).set_pkey(“id”, auto_increment=True).\
column(“first_name”, DataTypes.varchar, 100).\
column(“last_name”, DataTypes.varchar, 100).\
apply()create.table(“movies”).\
column(“id”, DataTypes.int).set_pkey(“id”, auto_increment=True).\
column(“title”, DataTypes.varchar, 100).\
column(“release_year”, DataTypes.year, 4).\
column(“genre”, DataTypes.varchar,100).\
column(“collection_in_mil”, DataTypes.decimal,”4,1").\
apply()create.table(“ratings”). \
column(“movie_id”, DataTypes.int). \
column(“reviewer_id”, DataTypes.int). \
column(“rating”, DataTypes.decimal, “2,1”). \
add_fkey(“movie_id”,”movies”,”id”).\
add_fkey(“reviewer_id”,”reviewers”,”id”).\
set_pkeys([“movie_id”, “reviewer_id”]). \
apply()
The following SQL commands will be generated after the execution of the codes above;
CREATE TABLE reviewers (id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(100),last_name VARCHAR(100));CREATE TABLE movies (id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(100),release_year YEAR(4),genre VARCHAR(100),collection_in_mil DECIMAL(4,1));CREATE TABLE ratings (movie_id INT,reviewer_id INT,rating DECIMAL(2,1),FOREIGN KEY(movie_id) REFERENCES movies(id),FOREIGN KEY(reviewer_id) REFERENCES reviewers(id), PRIMARY KEY(movie_id,reviewer_id));
Insert Values in Table
After the creation of the tables, they should be filled with the data that are available in [1]. In order to receive the same results, they are copied one-to-one. The different part is again the Insert object created for performing this task. The insert object is again created with the database configurations and all above provided data is inserted. Initially, the table is selected and either all other columns along with their data or a single column and its data are inserted. Developer can prefer one of these approaches based on the requirements.
reviewers_records = [
(“Chaitanya”, “Baweja”),
(“Mary”, “Cooper”),
(“John”, “Wayne”),
(“Thomas”, “Stoneman”),
(“Penny”, “Hofstadter”),
(“Mitchell”, “Marsh”),
(“Wyatt”, “Skaggs”),
(“Andre”, “Veiga”),
(“Sheldon”, “Cooper”),
(“Kimbra”, “Masters”),
(“Kat”, “Dennings”),
(“Bruce”, “Wayne”),
(“Domingo”, “Cortes”),
(“Rajesh”, “Koothrappali”),
(“Ben”, “Glocker”),
(“Mahinder”, “Dhoni”),
(“Akbar”, “Khan”),
(“Howard”, “Wolowitz”),
(“Pinkie”, “Petit”),
(“Gurkaran”, “Singh”),
(“Amy”, “Farah Fowler”),
(“Marlon”, “Crafford”),
]
movies = [
(“Forrest Gump”, 1994, “Drama”, 330.2),
(“3 Idiots”, 2009, “Drama”, 2.4),
(“Eternal Sunshine of the Spotless Mind”, 2004, “Drama”, 34.5),
(“Good Will Hunting”, 1997, “Drama”, 138.1),
(“Skyfall”, 2012, “Action”, 304.6),
(“Gladiator”, 2000, “Action”, 188.7),
(“Black”, 2005, “Drama”, 3.0),
(“Titanic”, 1997, “Romance”, 659.2),
(“The Shawshank Redemption”, 1994, “Drama”, 28.4),
(“Udaan”, 2010, “Drama”, 1.5),
(“Home Alone”, 1990, “Comedy”, 286.9),
(“Casablanca”, 1942, “Romance”, 1.0),
(“Avengers: Endgame”, 2019, “Action”, 858.8),
(“Night of the Living Dead”, 1968, “Horror”, 2.5),
(“The Godfather”, 1972, “Crime”, 135.6),
(“Haider”, 2014, “Action”, 4.2),
(“Inception”, 2010, “Adventure”, 293.7),
(“Evil”, 2003, “Horror”, 1.3),
(“Toy Story 4”, 2019, “Animation”, 434.9),
(“Air Force One”, 1997, “Drama”, 138.1),
(“The Dark Knight”, 2008, “Action”, 535.4),
(“Bhaag Milkha Bhaag”, 2013, “Sport”, 4.1),
(“The Lion King”, 1994, “Animation”, 423.6),
(“Pulp Fiction”, 1994, “Crime”, 108.8),
(“Kai Po Che”, 2013, “Sport”, 6.0),
(“Beasts of No Nation”, 2015, “War”, 1.4),
(“Andadhun”, 2018, “Thriller”, 2.9),
(“The Silence of the Lambs”, 1991, “Crime”, 68.2),
(“Deadpool”, 2016, “Action”, 363.6),
(“Drishyam”, 2015, “Mystery”, 3.0)
]
ratings_records = [
(6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17),
(5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4),
(8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10),
(5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19),
(8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9),
(8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19),
(5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15),
(8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20),
(8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13),
(7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17),
(6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4),
(9.8, 13, 1)
]insert = Insert(“arduino”, “arduino”, “127.0.0.1”, “university”)
insert.table(“movies”).columns([“title”, “release_year”,”genre”,”collection_in_mil”], movies).apply()
insert.table(“reviewers”).columns([“first_name”, “last_name”], reviewers_records).apply()
insert.table(“ratings”).columns([“rating”, “movie_id”,”reviewer_id”], ratings_records).apply()
insert.table(“ratings”).column(“rating”, 5.0).apply()
The execution of the above codes is resulted in the following outputs. The inserted data and the used SQL insert command is printed out on the console.
[(‘Forrest Gump’, 1994, ‘Drama’, 330.2), (‘3 Idiots’, 2009, ‘Drama’, 2.4), (‘Eternal Sunshine of the Spotless Mind’, 2004, ‘Drama’, 34.5), (‘Good Will Hunting’, 1997, ‘Drama’, 138.1), (‘Skyfall’, 2012, ‘Action’, 304.6), (‘Gladiator’, 2000, ‘Action’, 188.7), (‘Black’, 2005, ‘Drama’, 3.0), (‘Titanic’, 1997, ‘Romance’, 659.2), (‘The Shawshank Redemption’, 1994, ‘Drama’, 28.4), (‘Udaan’, 2010, ‘Drama’, 1.5), (‘Home Alone’, 1990, ‘Comedy’, 286.9), (‘Casablanca’, 1942, ‘Romance’, 1.0), (‘Avengers: Endgame’, 2019, ‘Action’, 858.8), (‘Night of the Living Dead’, 1968, ‘Horror’, 2.5), (‘The Godfather’, 1972, ‘Crime’, 135.6), (‘Haider’, 2014, ‘Action’, 4.2), (‘Inception’, 2010, ‘Adventure’, 293.7), (‘Evil’, 2003, ‘Horror’, 1.3), (‘Toy Story 4’, 2019, ‘Animation’, 434.9), (‘Air Force One’, 1997, ‘Drama’, 138.1), (‘The Dark Knight’, 2008, ‘Action’, 535.4), (‘Bhaag Milkha Bhaag’, 2013, ‘Sport’, 4.1), (‘The Lion King’, 1994, ‘Animation’, 423.6), (‘Pulp Fiction’, 1994, ‘Crime’, 108.8), (‘Kai Po Che’, 2013, ‘Sport’, 6.0), (‘Beasts of No Nation’, 2015, ‘War’, 1.4), (‘Andadhun’, 2018, ‘Thriller’, 2.9), (‘The Silence of the Lambs’, 1991, ‘Crime’, 68.2), (‘Deadpool’, 2016, ‘Action’, 363.6), (‘Drishyam’, 2015, ‘Mystery’, 3.0)]INSERT INTO movies(title,release_year,genre,collection_in_mil) VALUES (%s,%s,%s,%s)[(‘Forrest Gump’, 1994, ‘Drama’, 330.2), (‘3 Idiots’, 2009, ‘Drama’, 2.4), (‘Eternal Sunshine of the Spotless Mind’, 2004, ‘Drama’, 34.5), (‘Good Will Hunting’, 1997, ‘Drama’, 138.1), (‘Skyfall’, 2012, ‘Action’, 304.6), (‘Gladiator’, 2000, ‘Action’, 188.7), (‘Black’, 2005, ‘Drama’, 3.0), (‘Titanic’, 1997, ‘Romance’, 659.2), (‘The Shawshank Redemption’, 1994, ‘Drama’, 28.4), (‘Udaan’, 2010, ‘Drama’, 1.5), (‘Home Alone’, 1990, ‘Comedy’, 286.9), (‘Casablanca’, 1942, ‘Romance’, 1.0), (‘Avengers: Endgame’, 2019, ‘Action’, 858.8), (‘Night of the Living Dead’, 1968, ‘Horror’, 2.5), (‘The Godfather’, 1972, ‘Crime’, 135.6), (‘Haider’, 2014, ‘Action’, 4.2), (‘Inception’, 2010, ‘Adventure’, 293.7), (‘Evil’, 2003, ‘Horror’, 1.3), (‘Toy Story 4’, 2019, ‘Animation’, 434.9), (‘Air Force One’, 1997, ‘Drama’, 138.1), (‘The Dark Knight’, 2008, ‘Action’, 535.4), (‘Bhaag Milkha Bhaag’, 2013, ‘Sport’, 4.1), (‘The Lion King’, 1994, ‘Animation’, 423.6), (‘Pulp Fiction’, 1994, ‘Crime’, 108.8), (‘Kai Po Che’, 2013, ‘Sport’, 6.0), (‘Beasts of No Nation’, 2015, ‘War’, 1.4), (‘Andadhun’, 2018, ‘Thriller’, 2.9), (‘The Silence of the Lambs’, 1991, ‘Crime’, 68.2), (‘Deadpool’, 2016, ‘Action’, 363.6), (‘Drishyam’, 2015, ‘Mystery’, 3.0)][(‘Chaitanya’, ‘Baweja’), (‘Mary’, ‘Cooper’), (‘John’, ‘Wayne’), (‘Thomas’, ‘Stoneman’), (‘Penny’, ‘Hofstadter’), (‘Mitchell’, ‘Marsh’), (‘Wyatt’, ‘Skaggs’), (‘Andre’, ‘Veiga’), (‘Sheldon’, ‘Cooper’), (‘Kimbra’, ‘Masters’), (‘Kat’, ‘Dennings’), (‘Bruce’, ‘Wayne’), (‘Domingo’, ‘Cortes’), (‘Rajesh’, ‘Koothrappali’), (‘Ben’, ‘Glocker’), (‘Mahinder’, ‘Dhoni’), (‘Akbar’, ‘Khan’), (‘Howard’, ‘Wolowitz’), (‘Pinkie’, ‘Petit’), (‘Gurkaran’, ‘Singh’), (‘Amy’, ‘Farah Fowler’), (‘Marlon’, ‘Crafford’)]INSERT INTO reviewers(first_name,last_name) VALUES (%s,%s)[(‘Chaitanya’, ‘Baweja’), (‘Mary’, ‘Cooper’), (‘John’, ‘Wayne’), (‘Thomas’, ‘Stoneman’), (‘Penny’, ‘Hofstadter’), (‘Mitchell’, ‘Marsh’), (‘Wyatt’, ‘Skaggs’), (‘Andre’, ‘Veiga’), (‘Sheldon’, ‘Cooper’), (‘Kimbra’, ‘Masters’), (‘Kat’, ‘Dennings’), (‘Bruce’, ‘Wayne’), (‘Domingo’, ‘Cortes’), (‘Rajesh’, ‘Koothrappali’), (‘Ben’, ‘Glocker’), (‘Mahinder’, ‘Dhoni’), (‘Akbar’, ‘Khan’), (‘Howard’, ‘Wolowitz’), (‘Pinkie’, ‘Petit’), (‘Gurkaran’, ‘Singh’), (‘Amy’, ‘Farah Fowler’), (‘Marlon’, ‘Crafford’)][(6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17), (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4), (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10), (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19), (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9), (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19), (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15), (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20), (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13), (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17), (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4), (9.8, 13, 1)]INSERT INTO ratings(rating,movie_id,reviewer_id) VALUES (%s,%s,%s)[(6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17), (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4), (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10), (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19), (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9), (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19), (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15), (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20), (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13), (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17), (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4), (9.8, 13, 1)]INSERT INTO ratings(rating) VALUES (%s)
Select Items in Table
The select method is probably the most difficult one due to its rich options and combination with other SQL clauses. Even though all SQL features are unfortunately not supported by this API, all essential requirements are covered in order to perform all SQL commands shown in [1]. In the following code, the parameters for the database are passed to Select object, then the all required column values with where, limit, or another SQL clause combined to retrieve the data from the database. The most complex queries are, of course, the ones that includes also joint connections. All join operations are supported by the Select class. It is noteworthy to mention that “where” clause is used here differently than other classes due to the combination variety of where with other clauses. Apart from that, some columns can also receive directly the SQL clause e.g. AVG, CONCAT, if these are suitable for the Select clause.
select = Select(“arduino”, “arduino”, “127.0.0.1”, “university”)
select.table(“movies”).columns([“title,release_year”]).limit(“5”).apply()select.table(“movies”).columns([“title,collection_in_mil”]).where([“collection_in_mil>300”]).orderby(“collection_in_mil”,”DESC”).limit(“5”).apply()select.table(“ratings”).columns([“title, AVG(rating) as average_rating”]).innerjoin(“movies”,”movies.id = ratings.movie_id”).group_by(“movie_id”).orderby(“average_rating”,”DESC”).limit(“5”).apply()select.table(“reviewers”).columns([“CONCAT(first_name, \” \”, last_name), COUNT(*) as num”]).innerjoin(“ratings”,”reviewers.id = ratings.reviewer_id”).group_by(“reviewer_id”).orderby(“num”,”DESC”).limit(“1”).apply()
The result of the execution along with SQL clauses:
SELECT title,release_year FROM movies LIMIT 5;[(‘Forrest Gump’, 1994), (‘3 Idiots’, 2009), (‘Eternal Sunshine of the Spotless Mind’, 2004), (‘Good Will Hunting’, 1997), (‘Skyfall’, 2012)]SELECT title,collection_in_mil FROM movies WHERE (collection_in_mil>300) ORDER BY collection_in_mil DESC LIMIT 5;[(‘Avengers: Endgame’, Decimal(‘858.8’)), (‘Titanic’, Decimal(‘659.2’)), (‘The Dark Knight’, Decimal(‘535.4’)), (‘Toy Story 4’, Decimal(‘434.9’)), (‘The Lion King’, Decimal(‘423.6’))]SELECT title, AVG(rating) as average_rating FROM ratings INNER JOIN movies ON movies.id = ratings.movie_id GROUP BY movie_id ORDER BY average_rating DESC LIMIT 5;[(‘The Godfather’, Decimal(‘9.90000’)), (‘Night of the Living Dead’, Decimal(‘9.90000’)), (‘Avengers: Endgame’, Decimal(‘9.75000’)), (‘Eternal Sunshine of the Spotless Mind’, Decimal(‘8.90000’)), (‘Beasts of No Nation’, Decimal(‘8.70000’))]SELECT CONCAT(first_name, “ “, last_name), COUNT(*) as num FROM reviewers INNER JOIN ratings ON reviewers.id = ratings.reviewer_id GROUP BY reviewer_id ORDER BY num DESC LIMIT 1;[(‘Mary Cooper’, 4)]
In order to simplify the operation of the SQL clauses with “where” clause, they are intuitively defined from the perspective of a developer. Their usages are quite easy, each of these operators along with the parameter given in the “where” clause.
By using this approach, we can easily extend the new words belonging to where clause. The following example show how they are utilized:
select.table(“price”).where([“10<=price=<20”,”price~10”,”price!~20”,”price=10”,”price=[10,20,30]”,”price=10 | price=20 | price=30”])Update Values in Table
The values in the table can be also updated, and the Update class fulfills this requirement. It supports multi-columns and single-columns update as shown below. Where clause can only be used with a single condition, that means if more than one condition exists, then for each a single column a where clause should be written.
update = Update(“arduino”, “arduino”, “127.0.0.1”, “university”)
update.table(“reviewers”).column(“last_name”,”Cooper”).where(“first_name=Amy”).apply()
update.table(“ratings”).column(“rating”,5.0).where(“movie_id=18”).where(“reviewer_id=15”).apply()
# The following is not executed, it only shows the multi-columns approach
#update.table(“reviewers”).columns([“name”,”surname”],[“”,”aren”]).where(“id=1”).apply()UPDATE reviewers SET last_name = “Cooper” WHERE first_name = “Amy”
UPDATE ratings SET rating = 5.0 WHERE movie_id = “18” AND reviewer_id = “15”
Delete Items from Table
Removing object with SQL is also easy, however, here we do not deal with the SQL or daabase connection settings, etc, because these are all handled by the Delete class as in all other classes defined in this tutorial. The column values can be removed in cooperation with the where clause, or the whole content of the table can be completely removed. Apart from that, dropping table can be also performed via a single drop() method after giving the name of the table.
delete = Delete(“arduino”, “arduino”, “127.0.0.1”, “university”)
delete.table(“ratings”).where(“reviewer_id=2”).apply()
# clean the table content of reviewers
# delete.table(“reviewers”)# drop the whole reviewer table, which will not exist after its deletion.# delete.table(“reviewers”).drop()
The generated SQL command is given as below:
DELETE FROM ratings WHERE reviewer_id=2;In this article, the main goal is to provide another way of thinking for SQL language and how it can be turned into a more understandable language via a simple API. I call it pythonic sql, on the one side it offers the simplicity and hides all other SQL or database connectivity details, on the othe side nearly all SQL features are applicable. If you like the idea, you may also extend it with your perspectives. Please remember that the function names can be changed as you wish, and can design your SQL with your own words. The whole source code can be accessed via this link [2]
References