Creating a New Database, Schema and User

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.82
You save:  €36.80
List Price:  £109.74
You save:  £31.35
List Price:  CA$191.13
You save:  CA$54.61
List Price:  A$210.68
You save:  A$60.19
List Price:  S$188.83
You save:  S$53.95
List Price:  HK$1,092.95
You save:  HK$312.29
CHF 91.34
List Price:  CHF 127.88
You save:  CHF 36.54
NOK kr1,052.31
List Price:  NOK kr1,473.28
You save:  NOK kr420.96
DKK kr686.58
List Price:  DKK kr961.24
You save:  DKK kr274.66
List Price:  NZ$227.97
You save:  NZ$65.13
List Price:  د.إ514.17
You save:  د.إ146.91
List Price:  ৳16,408.23
You save:  ৳4,688.40
List Price:  ₹11,637.16
You save:  ₹3,325.14
List Price:  RM657.67
You save:  RM187.92
List Price:  ₦206,269.66
You save:  ₦58,938.40
List Price:  ₨38,916.02
You save:  ₨11,119.65
List Price:  ฿5,127.43
You save:  ฿1,465.08
List Price:  ₺4,505.30
You save:  ₺1,287.32
List Price:  B$723.91
You save:  B$206.84
List Price:  R2,571.91
You save:  R734.88
List Price:  Лв252.38
You save:  Лв72.11
List Price:  ₩190,499.14
You save:  ₩54,432.21
List Price:  ₪515.38
You save:  ₪147.26
List Price:  ₱8,140.48
You save:  ₱2,326.02
List Price:  ¥21,963.82
You save:  ¥6,275.82
List Price:  MX$2,336.09
You save:  MX$667.50
List Price:  QR510.10
You save:  QR145.75
List Price:  P1,900.64
You save:  P543.07
List Price:  KSh18,618.67
You save:  KSh5,320
List Price:  E£6,599.78
You save:  E£1,885.78
List Price:  ብር8,037.80
You save:  ብር2,296.67
List Price:  Kz118,908.34
You save:  Kz33,976.24
List Price:  CLP$126,117.11
You save:  CLP$36,036.03
List Price:  CN¥1,014.10
You save:  CN¥289.76
List Price:  RD$8,237.97
You save:  RD$2,353.87
List Price:  DA18,843.53
You save:  DA5,384.25
List Price:  FJ$311.92
You save:  FJ$89.12
List Price:  Q1,086.64
You save:  Q310.49
List Price:  GY$29,266.81
You save:  GY$8,362.54
ISK kr13,793.62
List Price:  ISK kr19,311.62
You save:  ISK kr5,518
List Price:  DH1,395.33
You save:  DH398.69
List Price:  L2,480.56
You save:  L708.78
List Price:  ден7,930.17
You save:  ден2,265.92
List Price:  MOP$1,125.70
You save:  MOP$321.65
List Price:  N$2,570.90
You save:  N$734.59
List Price:  C$5,148.73
You save:  C$1,471.17
List Price:  रु18,603.56
You save:  रु5,315.68
List Price:  S/522.95
You save:  S/149.42
List Price:  K543.64
You save:  K155.33
List Price:  SAR525.05
You save:  SAR150.02
List Price:  ZK3,733.66
You save:  ZK1,066.83
List Price:  L641.14
You save:  L183.19
List Price:  Kč3,187.01
You save:  Kč910.64
List Price:  Ft49,549.41
You save:  Ft14,157.98
SEK kr1,062.40
List Price:  SEK kr1,487.41
You save:  SEK kr425
List Price:  ARS$124,696.09
You save:  ARS$35,630
List Price:  Bs966.60
You save:  Bs276.19
List Price:  COP$540,370.17
You save:  COP$154,402.50
List Price:  ₡71,720.93
You save:  ₡20,493.15
List Price:  L3,457
You save:  L987.78
List Price:  ₲1,052,195.01
You save:  ₲300,648.62
List Price:  $U5,389.29
You save:  $U1,539.90
List Price:  zł547.82
You save:  zł156.53
Already have an account? Log In


First of all since in the previous lecture, we have installed the necessary module that we need for this section, it's time to also test that the module was successfully installed. And to do this, we are going to the Python interpreter and just entering import psycho PG to no errors are returned so the module is successfully installed and ready to use. Second of all, this section assumes that you already have some SQL or possibly SQL knowledge, and it's not meant to be an SQL tutorial. However, at the end of the section, I have attached a cheat sheet with many p SQL and SQL commands that are the most frequently used commands when dealing with databases. Also, after each of the videos in this section, I have attached a short notebook containing all the commands and code that we are using in the video right above each of these notebooks.

Oh, Having that set. Now it's time to go into SQL. And for that, you just have to go to your start menu and type in p SQL. I have p SQL already opened right here. First of all, let's see all the currently existing databases by using the command backslash L. Okay, so these are the databases right here. Let me adjust this a bit.

Okay, these are the default databases in Postgres SQL. However, we want to create our own database to, for example, store data about the employees at our company. And to do that, let's create our first database. And let's use the command create database. Let's call it stuff and also never forget to use the semi colon at the end of the command. This is an SQL command and should be followed by a semi colon.

However, p SQL specific commands like backslash shell and other similar commands that you will see in this lecture do not require wire, semi colon at the end. Okay, so this is the way in which to create a new database, create database staff. Okay, now let's use backslash l again. And you can see the newly created database right here. Great. Now let's also create a new user that we will use later on in this section to connect to our database using Python.

So for now, let's create a new user create user. Now the name of the user, in my case, Mihai, with encrypted password, and let's say the password is Python, you should enter the password in between quotes, of course at the end of the semi colon. Now in order to see the users backslash, D, U. And you can see the new user right here. This is the default Postgres SQL user. And this is our newly created user.

Great. Now it's time to grant all the necessary privileges for this user on the database that we have just created. In order to do that, we will use the following command and you can already Notice that this is almost plain English. So grant all privileges on database staff, to me Hi, semi colon. Okay. Now if we hit backslash again to see the databases, you can see that for the database stuff.

Now we have some access privileges for user me Hi, great. Now remember that database can contain one or more schemas, and the schema may contain one or more tables. To create a schema within our database, we must first connect to our database and to connect to the database because we are now currently connected to the default database, which is Postgres to connect to our database. We will use backslash C, a space and now the name of the database staff space and the name of the user Mihai. Okay. And you can see that we are now connected to the database stuff as user me Hi, great.

In order to create our first schema, we will use The following commands create schema. Let's call it my staff authorization. me Hi. So we are creating a new schema called my staff. And we are authorizing the user Mihai to be the owner of this schema. Let's hit Enter.

And now let's see the schemas inside this database. So backslash d n. And indeed, we have schema, my staff with owner, Mihai. In order to delete the schema, you will have to use the command drop schema and the name of the schema, let's say my staff, and of course the semicolon at the end. Furthermore, in order to delete a database or user, you can disconnect from the current database that you want to delete. For example, if you want to delete the staff database, you cannot be connected to it at the same time. So you would use backslash, C and Postgres to connect back to the default database and now you can use Drop user, Mihai, for example, and the semi colon to delete the user and to delete the database itself, drop database, staff, semi colon, and that's how you would delete the database itself.

Okay, having that set. In the next lecture, we are going to see how to connect Python to our newly created database. See you in the next lecture.

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.