Creating Database Tables with Python

Python 3: Automating Your Job Tasks Superhero Level: Automate Database Tasks with Python 3
4 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.67
You save:  €36.76
List Price:  £110.19
You save:  £31.48
List Price:  CA$190.60
You save:  CA$54.46
List Price:  A$208.90
You save:  A$59.69
List Price:  S$188.42
You save:  S$53.84
List Price:  HK$1,092.29
You save:  HK$312.10
CHF 90.89
List Price:  CHF 127.25
You save:  CHF 36.36
NOK kr1,073.95
List Price:  NOK kr1,503.58
You save:  NOK kr429.62
DKK kr686.39
List Price:  DKK kr960.97
You save:  DKK kr274.58
List Price:  NZ$228.20
You save:  NZ$65.20
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  ৳16,391.62
You save:  ৳4,683.65
List Price:  ₹11,661.24
You save:  ₹3,332.02
List Price:  RM656.20
You save:  RM187.50
List Price:  ₦205,785.30
You save:  ₦58,800
List Price:  ₨38,789.74
You save:  ₨11,083.57
List Price:  ฿5,074.35
You save:  ฿1,449.91
List Price:  ₺4,513.75
You save:  ₺1,289.73
List Price:  B$714.59
You save:  B$204.18
List Price:  R2,530.23
You save:  R722.97
List Price:  Лв251.81
You save:  Лв71.95
List Price:  ₩189,601.05
You save:  ₩54,175.60
List Price:  ₪518.60
You save:  ₪148.18
List Price:  ₱8,079.59
You save:  ₱2,308.62
List Price:  ¥21,792.94
You save:  ¥6,227
List Price:  MX$2,325.03
You save:  MX$664.34
List Price:  QR507.87
You save:  QR145.11
List Price:  P1,896.68
You save:  P541.94
List Price:  KSh18,338.69
You save:  KSh5,240
List Price:  E£6,566.93
You save:  E£1,876.40
List Price:  ብር8,018.62
You save:  ብር2,291.20
List Price:  Kz118,629.90
You save:  Kz33,896.68
List Price:  CLP$125,220.73
You save:  CLP$35,779.90
List Price:  CN¥1,011.17
You save:  CN¥288.92
List Price:  RD$8,157.46
You save:  RD$2,330.87
List Price:  DA18,810.98
You save:  DA5,374.95
List Price:  FJ$311.92
You save:  FJ$89.12
List Price:  Q1,087.56
You save:  Q310.75
List Price:  GY$29,285.87
You save:  GY$8,367.99
ISK kr13,827.61
List Price:  ISK kr19,359.21
You save:  ISK kr5,531.60
List Price:  DH1,386.51
You save:  DH396.17
List Price:  L2,464.07
You save:  L704.07
List Price:  ден7,916.55
You save:  ден2,262.03
List Price:  MOP$1,124.95
You save:  MOP$321.43
List Price:  N$2,547.77
You save:  N$727.98
List Price:  C$5,144.63
You save:  C$1,470
List Price:  रु18,661.67
You save:  रु5,332.28
List Price:  S/520.05
You save:  S/148.59
List Price:  K543.23
You save:  K155.22
List Price:  SAR524.96
You save:  SAR150
List Price:  ZK3,554.51
You save:  ZK1,015.64
List Price:  L640.62
You save:  L183.04
List Price:  Kč3,180.29
You save:  Kč908.72
List Price:  Ft49,855.26
You save:  Ft14,245.38
SEK kr1,071.59
List Price:  SEK kr1,500.27
You save:  SEK kr428.67
List Price:  ARS$124,136.13
You save:  ARS$35,470
List Price:  Bs967.19
You save:  Bs276.36
List Price:  COP$532,965.46
You save:  COP$152,286.72
List Price:  ₡71,639.71
You save:  ₡20,469.95
List Price:  L3,448
You save:  L985.21
List Price:  ₲1,048,812.96
You save:  ₲299,682.25
List Price:  $U5,395.05
You save:  $U1,541.55
List Price:  zł548.83
You save:  zł156.82
Already have an account? Log In


In this lecture, we are going to create our first table inside our database inside our schema that we just created earlier in the section. So our table will be a table of employees, where the first column is the ID of each employee. This is kind of a numbering system. And this column will also be the primary key for this table. Next, we have the first name and last name columns, the department where each employee works, the phone and address of each employee and also the salary. So having this table in mind, let's see what SQL commands we must use in order to create this table.

Okay, so this is the command right here in SQL syntax to create a new table. You have to use CREATE TABLE, my staff dot employees, where my staff is the name of the schema, and employees is the name of the new table being created. Then inside parentheses, we have the seven columns we just discussed, with the first column being the ID of type int, meaning integer, also as a primary key, and not no meaning it doesn't accept null values. Next, we have the first name, last name, department, phone and address, which are all of type var char, and also have various maximum length. For example, the address has a maximum of 50 characters, while the first name has a maximum of 25 characters. Notice also that the phone and address are optional, so they may or may not be present in the table.

And finally, we have the salary of each employee, which is of type int, meaning integer. Finally, don't forget to add the semicolon at the end of any SQL command. Now translating this over to the Python code, you can see that in the first part of this file, we have the code that we used previously to connect to the data database, this code is unchanged. But next we have this line right here, which creates a cursor. A cursor is a dedicated structure that allows us to traverse the records of a database. So using a variable called cursor, and the connection object we created above, we are using the cursor method in order to initialize this cursor.

Next, using the same cursor, we execute the SQL command that we've seen earlier. So in between the parentheses of the execute command, we have the SQL command right here in between triple quotes because this piece of code spans multiple lines of code. Finally, using the same connection object, we are committing the changes to the database, meaning we are saving the changes and finally we are closing the connection. Now let's open up the Python interpreter and paste in this code and see if we indeed create the new table in our database first Let's head over to P SQL and verify if there's any table in this schema at this point. So backslash, dt, the name of the schema, my staff, dot and an asterisk, and no tables were found. Okay.

Now, as I said, Let's open up the Python interpreter. First of all, let's import psycho PG two. Now let's initialize the connection. So I'm going to copy and paste in this try acceptance block. Okay, the connection is successful. Next, let's initialize the cursor as well.

Okay. And finally, let's execute the SQL command. I'm going to copy and paste this as well. Okay. Finally, we have connection dot commit, and also connection dot close. Now, let's get back into the Pl SQL command line and check the tables in the my staff schema once again and you can see that this We have our new table employees.

Also we can check out the contents of this table by using backslash D, my staff, dot employees. And indeed We have the seven columns that we defined, and the primary key, the type of each column. Now it's time to move on to the next lecture and see what else can we do with Python psycho PG two and our database

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.