this is the second part of my article series dealing with the database access with Python. In the first part I showed how to generally connect and query databases. I explained all the stuff based on the SQLite database which is automatically delivered with each Python installation above version 2.5. In this part I want to show how to work with MySQL and PostgreSQL databases.
In both cases you have to install some kind of database driver first. After that you can directly connect and query the databases.
How to access MySQL databases?
First step is to install the database library MySQLdb. You can easily do this with the tool pip. Just type the following code in the linux or windows command line:
pip install MySQLdb
Normally this should download and install what is needed in order to use MySQLdb.
Now you only have to import this module and then you can work with a MySQL database the same way you did with SQLite. Check out the following code:
import MySQLdb
db = MySQLdb.connect(host=“localhost“, # your host, usually localhost
user=“root“, # your username
passwd=“test“, # your password
db=“test“) # name of the data base
# Creation of a cursor object in order to run SQLs
cur = db.cursor()
# Your SQL statements
cur.execute(„SELECT * FROM TEST“)
# Output
result=cur.fetchone()
print(“)
print(‚Results:‘)
print(‚===========‘)
print(result[0])
db.close()
The result looks like this:
It’s absolutely the same process as with SQLite:
Connect()
Creation of a cursor
Execution of your SQL
Fetching the data & output or whatever
Close()
For your database you can just place your SQL in the execute() function.
We will see the same process with a PostgreSQL database.
How to access PostgreSQL databases?
If you want to access PostgreSQL database you need the module psycopg2. Again you can install it with pip (pip = “pip installs packages”):
pip install psycopg2
This again downloads and installs what is needed for access to PostgreSQL databases. Now you can connect and query the database:
The result looks the same as for MySQL. You can just place your SQL in the execute() function.
I hope you find this article helpful and interesting. I will continue this with an article on Oracle & SQL Server.
If you want to learn more on SQL, databases and Python you can read my BLOG or my books. Furthermore I am offering classroom trainings (only in Germany) and also online training (in English). Just check it out!
Sign up my Newsletter and get informed on all my new articles. You receive an eBook “Analytical SQL” for free.
If you’re using Python 2.5 or higher the SQLite database is automatically delivered with it. If you want to work with SQLite you should install the SQLite3 command line tools as well. You can download them on the SQLite Download site: https://www.sqlite.org/download.html
Search for the SQLite3 Tools. They are available for several operating systems. The following explanation is based on Windows systems:
Download the zip file
Extract it to a location you prefer
Run SQLite3.exe
The command line then just looks like this:
Now you can type in SQL commands. If you are not familiar with SQL just check out my BLOG, my online training or my books. Or you could start with the free excerpt of my book which explains a lot of basic stuff.
Otherwise you can create an example table now. We type in the following commands:
CREATE TABLE customer (Customer_no NUMERIC,
Firstname VARCHAR(20),
Lastname VARCHAR(20)
);
INSERT INTO customer VALUES(1, ‘Thomas’, ‘Smith’);
SELECT * FROM customer;
That just creates a table and inserts one row into it. The last command shows all rows and all columns from that table. The result should look like this:
This tool is basically used to create tables and so on. The database only exists in memory right now. If you want to save it to disk permanently you have to save it. Type in:
.save test.db
Now the data is stored in the database test.db. You could of course use different names. Another way you could achieve that result is when you start SQLite3 and passing a name of a database. If the database already exists it’s opened. If it doesn’t exist then the database is created. Not in memory but on disk. Then you don’t have to save it.
Access database in Python
Now we have our database and now we want to access it in our Python program. Depending on the database we are using we need the corresponding python database module. For our SQLite database it is already available in Python (as from version 2.5 on). So we don’t have to do anything now. I’ll explain now how to access the SQLite database in Python. I will explain in a following article how to access other databases (Oracle, MySQL, MS SQL, Postgres). Now let’s start.
Our first example shows how to insert data into a table:
import sqlite3
con=sqlite3.connect(„C:/Daten/sqlite-tools-win32-x86-3210000/test.db“)
con.execute(„INSERT INTO customer VALUES(2, ‚Bill‘, ‚Meyers‘)“)
con.execute(„INSERT INTO customer VALUES(3, ‚Anne‘, ‚Meyers‘)“)
con.commit()
con.close()
If we run our select in SQLite3 command line we get the following result:
As you can see we have three rows in the table now. Let’s walk through the code line by line. First we have to import the SQLite Connector. We do that with the import line.
Now we are connecting to our database. We use connect(…) for that and have to pass the path and name of our database. You have to change it to your path and name. We get a connection object in con.
The next step is to insert two lines into the table. We want to run the SQL INSERT INTO statement as we did before in the SQLite3 command line. To run SQL commands you can use the method execute(…). We do that twice and in both cases we passed the INSERT INTO statement as a parameter.
After we inserted the two rows we have to commit() the data. That is needed in order to save it permanently to the database. If you leave that the rows are removed when the connection is closed.
Closing the connection is then the last step and you do that with close().
Now we want to query data in Python. That’s our second example:
import sqlite3
con=sqlite3.connect(„C:/Daten/sqlite-tools-win32-x86-3210000/test.db“)
print („\nRead data with FOR .. IN ..“)
cur=con.execute(„SELECT * FROM customer“)
for x in cur:
print(x)
print („\nRead data with WHILE and .fetchone()“)
cur=con.execute(„SELECT * FROM customer“)
while True:
y=cur.fetchone()
if y==None:
breakprint (y[0], ‚-‚, y[1])
print („\nRead data with .fetchall()“)
cur=con.execute(„SELECT * FROM customer“)
x=cur.fetchall()print (x)
print („\nRead data with .fetchmany(..)“)
cur=con.execute(„SELECT * FROM customer“)
x=cur.fetchmany(2)print (x)
con.close()
The results looks like that:
In this example we firstly connect to the database again. Then we use .execute() to run a SELECT statement. Now I provide four different ways to fetch and show the data:
Iteration with for … in ….
While loop with .fetchone()
.fetchall()
.fetchmany(…)
The iteration is probably the most elegant way to fetch the data. We are just iterating through the cursor picking one row with each iteration. Then we just print it.
Working with the while loop is a little more complicated. We define a indefinite loop. Then we fetch exactly one row with the method .fetchone(). Then we check if we really fetched another row. If not x is empty and that’s why we check for None. If so we break the loop. Otherwise we print the first two columns. You can access the columns with the [..] operator.
With the method .fetchall() we fetch all rows and put them into an array of tuples. You can directly print that.
The last option is .fetchmany(numRows). In comparision with .fetchall() you fetch only the number of rows you are passing to this method. In our case the first 2 rows. Again they are put into an array of tuples.
I hope you find this article helpful and interesting. I will continue this with an article on other databases. A lot of the stuff we did today is the same with other databases. But the connection is always a little bit different depending on the database.
If you want to learn more on SQL, databases and Python you can read my BLOG or my books. Or just start with the free excerpt of my book. Furthermore I am offering classroom trainings (only in Germany) and also online training(in English). Just check it out!
Sign up my Newsletter and get informed on all my new articles. You receive an eBook “Analytical SQL” for free.
Today I just want to give you 6 tips on how to use the Oracle SQL Deveoper more efficient. If you dive into the functions and menus you’ll find a lot of small functions which can really ease your developer life and makes coding much fast.
1) Show/Hide line numeration
For testing and error correction purpose you sometimes want to know the line numbers. You can easily show and hide them by just right clicking on the vertical white line on the very left hand side of the editor (better take a look at the screenshot). You then just click on the second entry of the context menu to switch it on or off.
2) The Model Tab
A useful new function from version 4.1 on is the model tab. This is available on all tables and shows the selected tables with all tables that have a foreign key relation with that table.
3) Resolve a SELECT * to all its columns
Sometimes it’s helpful if you could easily resolve a SELECT * to the column names of a table (also works for multiple tables). All you have to do is hover with the cursor on the * and wait for the popup. You then see all the columns that will be used instead of *. If you click on the popup the * is replaced by these columns.
4) Multi cursor editing
If you want to save time when copy and pasting the same stuff at several places you could use the feature multi cursor editing. You just press Ctrl+Shift and then you click where you need cursors. If you multiple tables starting with TBL_ and you want to remove that you would just place the cursor at the first TBL_ and then press Ctrl+Shift and place new cursors before all TBL_ Then you can just press delete to remove it and the action is done at all curors.
5) commenting on several lines at the same time
You can use comments to just deactivate single lines. You would normally do that with double minus and this comments the whole line. If you want to do that on multiple lines you can do it by marking the lines, then right click and then click comment rows on/off in the context menu. This automatically adds a double minus to each line. The standard shortcut is Ctrl + /
I hope you find these tips helpful. If you are interested in regular updates of my blog just subscribe my newsletter. All Subscribers receive my Analytical SQL Guide which helps you to use analytical SQL in your queries.
today’s article is about Cross Joins and the few cases where you can use it.
In general a cross join just takes all rows from table A and combines them with all rows from table B. Other terms for cross join are cross product and cartesian product. Let’s make a small example. We need two tables:
TBL_A
TBL_B
Now you can do the CROSS JOIN with the following SQL:
SELECT *
FROM TBL_A
CROSS JOIN TBL_B
Compared with an inner join you write CROSS JOIN and you leave the ON clause. That one is not needed as you don’t define any joining condition. That is because all rows from A are taken and combined with all rows of B. The result looks like this:
As you can see the row one from table A (1 / A) is taken and combined with all three rows from table B. The result are three rows where the first two columns (that come from table A) are the same and the last two columns (that come from table b) are changing. The same is repeated with row two from table A (2 / A).
So you can calculate the number of resulting rows with rows in table A * rows in table B. In our example it’s 2*3 = 6 rows.
This type of join is seldom used as it makes sense only in a few cases. For example if you have a value per week and you want to distribute it on each day of the week. I have another table for that:
Now we run the following query:
SELECT WEEK_START_DATE+DAY_OF_WEEK AS DAY_DATE,
WEEK_VALUE
FROM TBL_VALUE_WEEK
CROSS JOIN (
SELECT LEVEL-1 AS DAY_OF_WEEK
FROM dual
CONNECT BY LEVEL<=7
) week_days
ORDER BY DAY_DATE
And the result looks like this:
As you can see we have all seven days of the week starting with 2017-12-04 and each day has the WEEK_VALUE of this week. Then comes the next week.
Let’s take a closer look at the query. We have a subquery which is needed for generating the seven days of a week. This is database specific. In this case it is an opportunity on an Oracle database. You could also create a table with the numbers 0 to 7 and work with that instead of a subquery. If you are working on other databases you have to change that part.
Besides the subquery we are just accessing the weekly table cross joining it so in a result we get seven rows a week instead of one.
You find the example code for this article in the download section.
If you are interested in further information on SQL just check out my BLOG or take a look at my new SQL book. I’ve added a free excerpt on this BLOG as well. You can also attend training on my corporate homepage. At the moment my classroom trainings are only available in German. Inhouse training and online courses are also available in English language. So check out my trainings (German) or my online courses (English)!
today’s article is the fourth part of my tutorial on analytical functions. Today I will deal with the differences between RANGE and ROWS windows. We already learned about the ROWS windows in Part I of this tutorial. Today we will take closer look at the RANGE windows and how they differ from ROWS.
I’ve prepared some test data (you can download them in the download section). The data looks like this:
It contains DAYs from 01/01/2017 till 30/04/2017 and PRODUCT_NO from 1 to 3.
Now let’s take a look at the first example. We will create a query to calculate the monthly sums of the turnover. Then we want to compare the previous and the current month. We could do it for example with window functions.
SELECT MONTH,
TURNOVER AS CUR_MONTH,
SUM(TURNOVER) OVER (ORDER BY MONTH
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS PREV_MONTH
FROM (
SELECT DISTINCT
to_char(DAY, ‚YYYY-MM‘) AS MONTH,
SUM(TURNOVER) OVER (PARTITION BY to_char(DAY, ‚YYYY-MM‘)
) AS TURNOVER
from tbl_test
)
What we are doing here is calculating monthly values first. Then we are working with a ROWS window, which means that for each row (that is returned by the sub query) we calculate a separate window which is defined in this case as ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING. That means that we are just accessing the previous row. For that it is important that we first sort the data what is done by ORDER BY MONTH.
ROWS also means that the window definition is based on physical rows. That’s the reason why we need a sub query. First we have to create “physically” monthly rows. Then we can work with them. If we don’t do it we receive strange results as the physical level would still be on day level. So within the window daily data is accessed and shown.
Now if we use the RANGE instead of the ROWS we don’t need the subquery. I.e. we receive the same result with this:
SELECT DISTINCT
to_char(DAY, ‚YYYY-MM‘) AS MONTH,
SUM(TURNOVER) OVER (PARTITION BY to_char(DAY, ‚YYYY-MM‘)
) ASCUR_MONTH,
SUM(TURNOVER) OVER (ORDER BY to_number(to_char(DAY, ‚YYYYMM‘))
RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS PREV_MONTH
FROM tbl_test
Test it yourself. The results are the same. There are two differences. We are using RANGE instead of ROWS and we don’t have a sub query. We don’t need the sub query any longer because the RANGE windows are based on logical blocks or logical value changes. When you take a closer look at the ORDER BY clause in the last example you find that we are not using to_char(…..) but to_number(to_char(….)).
We do that as the range windows only works with dates or numbers in the ORDER BY clause. The expression you are using there is important for that logical value change. All rows with the same value in that expression are treated as one. They are summed up (because we are using SUM) first (that’s what we have done manually with the sub query in the first example) and afterwards the window is defined. If we say one block back we are stepping back one logical change. In our case it would mean that we step back one month and not one row.
I know that is a little difficult to understand. ROWS means we are defining our window based on physical row changes. RANGE means we are defining it by logical value change within the ORDER BY expression.
That also leads to another point. If you use ROWS and do your window on physical rows the database doesn’t really care if the previous month isn’t available. If we take April and March isn’t there the February is taken instead. With RANGE it is different. Here a NULL would be shown for March as the previous month of April doesn’t have a value. Which is correct then.
This was just a small introduction to the RANGE windows. If you are interested in further information on that just check out my BLOG. Maybe I will write another article on that with more examples. Or take a look at my new SQL book. I’ve added a free excerpt on this BLOG as well.
You find the example code for this article in the download section.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses is also available in English language. So check out my trainings (German) or my online courses (English)!
