Querying the Database with Python

Python 3: Automating Your Job Tasks Superhero Level: Automate Database Tasks with Python 3
5 minutes
Share the link to this page
You need to have access to the item to view this lesson.
One-time Fee
List Price:  $139.99
You save:  $40
List Price:  €128.19
You save:  €36.62
List Price:  £110.21
You save:  £31.49
List Price:  CA$191.03
You save:  CA$54.58
List Price:  A$206.87
You save:  A$59.11
List Price:  S$187.82
You save:  S$53.66
List Price:  HK$1,093.05
You save:  HK$312.32
CHF 91.46
List Price:  CHF 128.05
You save:  CHF 36.59
NOK kr1,072.12
List Price:  NOK kr1,501.01
You save:  NOK kr428.89
DKK kr684.02
List Price:  DKK kr957.65
You save:  DKK kr273.63
List Price:  NZ$228.74
You save:  NZ$65.35
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  ৳16,401.70
You save:  ৳4,686.53
List Price:  ₹11,691.71
You save:  ₹3,340.72
List Price:  RM656.83
You save:  RM187.68
List Price:  ₦196,825.94
You save:  ₦56,240
List Price:  ₨38,860.80
You save:  ₨11,103.88
List Price:  ฿5,044.04
You save:  ฿1,441.25
List Price:  ₺4,631.91
You save:  ₺1,323.49
List Price:  B$760.04
You save:  B$217.17
List Price:  R2,513.37
You save:  R718.15
List Price:  Лв250.97
You save:  Лв71.71
List Price:  ₩192,500.24
You save:  ₩55,004
List Price:  ₪506.81
You save:  ₪144.81
List Price:  ₱8,177.30
You save:  ₱2,336.53
List Price:  ¥22,098.11
You save:  ¥6,314.19
List Price:  MX$2,466.73
You save:  MX$704.83
List Price:  QR509.73
You save:  QR145.65
List Price:  P1,879.99
You save:  P537.17
List Price:  KSh17,797.56
You save:  KSh5,085.38
List Price:  E£6,714.11
You save:  E£1,918.45
List Price:  ብር8,064.21
You save:  ብር2,304.22
List Price:  Kz122,561.24
You save:  Kz35,020
List Price:  CLP$127,638.73
You save:  CLP$36,470.81
List Price:  CN¥1,014.98
You save:  CN¥290.01
List Price:  RD$8,250.84
You save:  RD$2,357.55
List Price:  DA18,802.19
You save:  DA5,372.43
List Price:  FJ$310.88
You save:  FJ$88.83
List Price:  Q1,086.73
You save:  Q310.51
List Price:  GY$29,191.35
You save:  GY$8,340.98
ISK kr13,668.91
List Price:  ISK kr19,137.02
You save:  ISK kr5,468.11
List Price:  DH1,369.98
You save:  DH391.45
List Price:  L2,466.53
You save:  L704.77
List Price:  ден7,886.17
You save:  ден2,253.35
List Price:  MOP$1,122.54
You save:  MOP$320.75
List Price:  N$2,511.10
You save:  N$717.50
List Price:  C$5,138.67
You save:  C$1,468.29
List Price:  रु18,654.82
You save:  रु5,330.32
List Price:  S/524.91
You save:  S/149.98
List Price:  K546.16
You save:  K156.05
List Price:  SAR525.07
You save:  SAR150.03
List Price:  ZK3,569.89
You save:  ZK1,020.04
List Price:  L637.96
You save:  L182.28
List Price:  Kč3,250.65
You save:  Kč928.82
List Price:  Ft50,239.55
You save:  Ft14,355.18
SEK kr1,065.95
List Price:  SEK kr1,492.38
You save:  SEK kr426.42
List Price:  ARS$128,314.65
You save:  ARS$36,663.94
List Price:  Bs964.54
You save:  Bs275.60
List Price:  COP$554,874.41
You save:  COP$158,546.87
List Price:  ₡72,959.06
You save:  ₡20,846.93
List Price:  L3,457.35
You save:  L987.88
List Price:  ₲1,052,468.19
You save:  ₲300,726.67
List Price:  $U5,604.94
You save:  $U1,601.52
List Price:  zł546.01
You save:  zł156.01
Already have an account? Log In


OK, so we currently have these five employees in our database. And we want to perform multiple SQL queries using Python and up psycho PG two module. In order to do that, I'm going to open up the Python interpreter. And first of all, as always, I'm going to import psycho PG to next, we should establish the connection to the database using the same code as we did thus far in this section. So I'm going to just copy and paste this right into the interpreter. Okay, the connection to the database was successful.

Now as always, it is time to initialize the cursor. So let me paste this in as well. Okay, so at this point, we have the cursor ready to execute some SQL statements, some SQL commands, and in our case, we will use some select queries. In order to do that. Let's just type in cursor, dot execute and In between the parentheses have execute. And of course in between double quotes, select all, from my staff that employees, and now the condition where salary is greater than 50,000, let's say.

And of course, don't forget the semi colon at the end of each SQL statement. Now looking at our table, which are the employees whose salaries are over 50,000, that would be jack and Emily right with 55,050 9000. Okay, let's execute this. And now we will use a new method in order to fetch all the results of this query. So let's say records equals cursor, dot, and now the new method, fetch all this method, as I said, fetches all the results of the query and returns a list. Okay, records.

This is the list right here with the two records that were returned by the query. Let's iterate over this list of four records, in records, print record. And indeed, we have the two employees being returned, as we discussed over here. Great. Now let's perform it another SQL statement. And let's say that from our table, we would like to see which employees have a salary between 40,040 $5,000.

And of course, that would be Karen and Mr. Right here, right? So let's try this. We have cursors that execute, select all from my staff that employees were salary between this is a keyword 40,040 5000, of course, enter the semi colon. And now let's execute the query. Let's fetch the results using the fetch all method.

And finally, let's iterate over this list. And indeed, as we discussed, we have Karen and M up being returned. Okay, next, let's perform it another query using another type of condition. So let's use cursor dot execute. And let's say that we want to see the employees that work in the sales and IT departments. And of course, those would be john jack and Emily soulless.

Try this inside the Python interpreter, select all from my staff that employees were department in and open and close parentheses. First, we said sales inside single quotes because we already have the double quotes in closing the entire SQL statement, comma, and I T. Okay, this seems correct. Let's execute it. Let's fetch all the records. And let's iterate over the records. And indeed, we have Jeff, john and Emily returned by this SQL query.

And finally, the last query in this video is going to be the following Let's say that from our database, we would like to see the employees whose last names start with D. And that would be jack doe and Emily David's. So let me write the query for this cursor that execute select all from my staff that employees were last name. Now comes the SQL key word like, and in between single quotes, again, we would have D, and the percent sign, the percent sign, meaning 01 or more characters. So we would like to search for less names, starting with a capital D, and let's check our results. Let's fetch all the results. And let's iterate over the list.

And indeed, we have jack doe and Emily David's returned, which is correct. Okay, so this is the way in which you can query a database from within Python from within the Python interpreter or translating this code into a file from within a Python. Script. Also you have learned the fetch all method which fetches all the results from an SQL query and returns a list. In the next lecture, we are going to see two other related methods for fetching the results of a query. So I'll see you soon

Sign Up


Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.