>>>You may have noticed that the connect() method only specifies the database name. By default, it
logs in to the PostgreSQL server using the same user account with which you’re logged in to the Linux
system. Since you’re running the script logged in as the pi user account, the connect() method
uses the pi login role automatically.
You can also specify a separate user and password in the connect() method, as shown here:
Click here to view code image
>>> conn = psycopg2.connect('dbname=pytest user=pi password=mypass')Notice that the parameters are all part of one string value, not separate strings.
Now your Python script is connected to the pytest database, and you’re ready to start interacting
with the tables.
Inserting Data
After you’ve connected to the database, you can insert some new data records into your employees
table. The psycopg2 module provides a similar approach to what you used with the
mysql.connector module. Listing 21.3 shows the script2103.py program, which
demonstrates how to add new data elements to the database.
LISTING 21.3 The script2103.py Program Code
Click here to view code image
1: #!/usr/bin/python3
2:
3: import psycopg2
4: conn = psycopg2.connect('dbname=pytest')
5: cursor = conn.cursor()
6: new_employee = 'INSERT INTO employees VALUES (%s, %s, %s, %s)'
7: employee1 = ('1', 'Blum', 'Katie Jane', '55000.00')
8: employee2 = ('2', 'Blum', 'Jessica', '35000.00')
9: try:
10: cursor.execute(new_employee, employee1)
11: cursor.execute(new_employee, employee2)
12: conn.commit()
13: except:
14: print('Sorry, there was a problem adding the data')
15: else:
16: print('Data values added!')
17: cursor.close()
18: conn.close()The execute() method submits the INSERT statement template along with the data tuple to the
PostgreSQL server for processing. The data isn’t committed to the database, though, until you issue
the commit() method from the connection. You can run the script2103.py program and then
check the employees table for the data, as shown here:
Click here to view code image
pi@raspberrypi ~ $ python3 script2103.py
Data values added!