You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
In this section, I'll explain how to connect your PostgreSQL database to Python and query it.
3
+
This tutorial explains how to connect your PostgreSQL database to Python and query it.
4
4
5
-
## Install "Psycopg2"
5
+
## Prerequisites
6
6
7
-
To access the PostgreSQL database, Python needs a PostgreSQL driver. You must first install the `Psycopg2` package on your computer.
7
+
Before you begin, make sure you have the following installed:
8
+
9
+
- Python: You can download and install Python from the official website (https://www.python.org).
10
+
- PostgreSQL: Install PostgreSQL on your computer by following the instructions from the official website (https://www.postgresql.org).
11
+
12
+
## Install the "psycopg2" Package
13
+
14
+
To access the PostgreSQL database, Python needs a PostgreSQL driver. You can install the `psycopg2` package using pip, the Python package installer. Open your command prompt or terminal and run the following command:
8
15
9
16
```bash
10
-
python -m pip install psycopg2
17
+
pip install psycopg2
11
18
```
12
19
13
-
## Import Psycopg2"
20
+
## Import the "psycopg2" Library
14
21
15
-
To access the PostgreSQL database, import `psycopg2`library into your Python code.
22
+
To use the `psycopg2`package in your Python code, import the `psycopg2` module:
16
23
17
24
```python
18
25
import psycopg2
19
26
```
20
27
21
-
## Creating a Database:
28
+
## Creating a Database
22
29
23
-
For the purpose of example, we will need a sample database. To do so, follow the below steps:
30
+
For the purpose of this example, we will need a sample database. Follow the steps below to create it:
24
31
25
-
- First, open a PostgreSQL client tool like `pgadmin4` or `psql`.
26
-
- Second login to the database using your credentials.
27
-
- Finally, run the following command to create a database (for example, `company`).
32
+
1. Open a PostgreSQL client tool like `pgadmin4` or `psql`.
33
+
2. Log in to the database using your credentials.
34
+
3. Run the following command to create a database, for example, `company`:
28
35
29
36
```sql
30
37
CREATEDATABASEcompany;
31
38
```
32
39
33
-
## Create Connection:
40
+
## Create Connection
34
41
35
-
To connect to the above created database (i.e., `company`), we use the `connect()` function.
36
-
So, establish a connection to the database. Use your PostgreSQL database's username and password in the Python code.
42
+
To connect to the previously created database (`company`), we use the `connect()` function. Establish a connection to the database by providing your PostgreSQL database's username, password, and database name in the Python code:
37
43
38
44
```python
39
45
mydb = psycopg2.connect(
@@ -44,29 +50,29 @@ mydb = psycopg2.connect(
44
50
)
45
51
```
46
52
47
-
## Make Cursor:
53
+
## Create a Cursor
48
54
49
-
You must create or include a `cursor()` in your Python code in order to run SQL queries.
55
+
You need to create a cursor to execute SQL queries in your Python code:
50
56
51
57
```python
52
58
mycursor = mydb.cursor()
53
59
```
54
60
55
61
Now you can use SQL commands to query the database.
56
62
57
-
## Commit Changes:
63
+
## Commit Changes
58
64
59
-
You also have to include the commit function and set automatic commit to be `true`.
65
+
You also need to include the commit function and set the automatic commit to be `True`:
60
66
61
67
```python
62
68
mydb.set_session(autocommit=True)
63
69
```
64
70
65
-
So that each action is committed or saved without having to call `mydb.commit()` after each command.
71
+
This ensures that each action is committed or saved without having to call `mydb.commit()` after each command.
66
72
67
-
## Query the Database:
73
+
## Query the Database
68
74
69
-
Nowcreate a table in the database using the cursor we created.
75
+
Now, let's create a table in the database using the cursor we created:
Now we are inserting the values into the database.
87
+
Now, let's insert values into the database:
82
88
83
89
```python
84
90
mycursor.execute('''
@@ -89,74 +95,83 @@ INSERT INTO employee (EmployeeID, Name, Email)
89
95
''')
90
96
```
91
97
92
-
## Retrive the Data:
98
+
## Retrieve the Data
93
99
94
-
Now let’s query the database.
100
+
Let's query the database:
95
101
96
102
```python
97
103
mycursor.execute("SELECT * FROM employee")
98
104
```
99
105
100
-
It’s important to note that after the query executes, you still need to use one of the `psycopg2` functions to retrieve data rows.
106
+
After executing the query, you can use one of the `psycopg2` functions to retrieve data rows:
101
107
102
-
- fetchone()
103
-
- fetchall()
104
-
- fetchmany()
108
+
-`fetchone()`: Retrieves exactly one row (the first row) after executing the SQL query.
109
+
-`fetchall()`: Retrieves all the rows.
110
+
-`fetchmany()`: Retrieves a specific number of rows.
105
111
106
-
For example,
112
+
For example:
107
113
108
114
```python
109
115
print(mycursor.fetchone())
110
116
```
111
117
118
+
Output:
119
+
112
120
| EmployeeID | Name | Email |
113
121
| :--------- | :--- | :--------------- |
114
122
| 101 | Mark |mark@company.com|
115
123
116
-
The most basic way to fetch data from your database is to use the `fetchone()` function. This function will return exactly one row (the first row) after executing the SQL query.
124
+
The most basic way to fetch data from your database is to use the `fetchone()` function. It returns exactly one row (the first row) after executing the SQL query.
117
125
118
126
```python
127
+
128
+
119
129
print(mycursor.fetchall())
120
130
```
121
131
132
+
Output:
133
+
122
134
| EmployeeID | Name | Email |
123
135
| :--------- | :------ | :------------------ |
124
136
| 101 | Mark |mark@company.com|
125
137
| 102 | Robert |robert@company.com|
126
138
| 103 | Spencer |spencer@company.com|
127
139
128
-
If you need more than one row from your database, you can use `fetchall()`, which works the same as `fetchone()` except that it returns all the rows.
140
+
If you need more than one row from your database, you can use `fetchall()`, which returns all the rows.
129
141
130
142
```python
131
143
print(mycursor.fetchmany(2))
132
144
```
133
145
146
+
Output:
147
+
134
148
| EmployeeID | Name | Email |
135
149
| :--------- | :----- | :----------------- |
136
150
| 101 | Mark |mark@company.com|
137
151
| 102 | Robert |robert@company.com|
138
152
139
-
With `fetchmany()`, you have another option to retrieve multiple records from the database and have more control over the exact number of rows retrieved.
153
+
With `fetchmany()`, you have another option to retrieve a specific number of rows from the database.
140
154
141
-
You can use any of them depending on your needs.
155
+
Choose the appropriate function based on your needs.
142
156
143
-
## Close the Connection:
157
+
## Close the Connection
144
158
145
-
In the end, you must close the cursor and the connection.
159
+
Finally, remember to close the cursor and the connection:
146
160
147
161
```python
148
162
mycursor.close()
149
163
mydb.close()
150
164
```
151
165
152
-
You can also get code snippet from[here](https://github.com/kishlayjeet/Connect-PostgreSQL-database-to-Python/blob/d7c5cae0a809ec0714cf193c5db6a77f30e70502/code-snippet.py).
166
+
You can also find the code snippet [here](https://github.com/kishlayjeet/Connect-PostgreSQL-database-to-Python/blob/d7c5cae0a809ec0714cf193c5db6a77f30e70502/code-snippet.py).
153
167
154
168
## Authors
155
169
156
-
I am [Kishlay](https://www.github.com/kishlayjeet), and I have written this tutorial, but other people have also helped me with it.
157
-
If you have any trouble with this tutorial, please tell me about it, and I will make it better.
158
-
If you like this tutorial and this helped you out, then please give it a star.
170
+
This tutorial was written by [Kishlay
171
+
Jeet](https://www.github.com/kishlayjeet), with contributions from other people.
172
+
If you encounter any issues with this tutorial, please let me know, and I will make improvements.
173
+
If you found this tutorial helpful, please consider giving it a star.
159
174
160
175
## Feedback
161
176
162
-
If you have any feedback, please reach out to me at contact.kishlayjeet@gmail.com
177
+
If you have any feedback, please reach out to me at contact.kishlayjeet@gmail.com.
0 commit comments