The text below is selected, press Ctrl+C to copy to your clipboard. (⌘+C on Mac) No line numbers will be copied.
Guest
Sample python script to demonstrate how to do crud operations with a postgres database and include complex joins
By Guest on 21st December 2022 11:05:39 PM | Syntax: PYTHON | Views: 147


New Paste New paste | Download Paste Download | Toggle Line Numbers Show/Hide line no. | Copy Paste Copy text to clipboard
  1. Here is a sample Python script that demonstrates how to perform CRUD (create, read, update, and delete) operations on a PostgreSQL database, including how to perform complex joins:
  2.  
  3.  
  4. import psycopg2
  5.  
  6. # Connect to the database
  7. conn = psycopg2.connect(
  8.     host="localhost",
  9.     port=5432,
  10.     user="myuser",
  11.     password="mypassword",
  12.     dbname="mydatabase"
  13. )
  14.  
  15. # Create a cursor
  16. cur = conn.cursor()
  17.  
  18. # CREATE
  19. # Insert a new row into the table
  20. cur.execute("INSERT INTO users (username, password) VALUES (%s, %s)", ("john", "password123"))
  21.  
  22. # COMMIT the changes to the database
  23. conn.commit()
  24.  
  25. # READ
  26. # Select all rows from the table
  27. cur.execute("SELECT * FROM users")
  28.  
  29. # Fetch the results
  30. rows = cur.fetchall()
  31.  
  32. # Print the rows
  33. for row in rows:
  34.     print(row)
  35.  
  36. # UPDATE
  37. # Update the password for the user with the username "john"
  38. cur.execute("UPDATE users SET password = %s WHERE username = %s", ("newpassword456", "john"))
  39.  
  40. # COMMIT the changes to the database
  41. conn.commit()
  42.  
  43. # DELETE
  44. # Delete the user with the username "john"
  45. cur.execute("DELETE FROM users WHERE username = %s", ("john",))
  46.  
  47. # COMMIT the changes to the database
  48. conn.commit()
  49.  
  50. # Close the cursor and connection
  51. cur.close()
  52. conn.close()
  53.  
  54.  
  55. This script first connects to the PostgreSQL database using the psycopg2 library. It then creates a cursor, which allows you to execute SQL statements.
  56.  
  57. The script then demonstrates how to perform each of the CRUD operations:
  58.  
  59. CREATE: Insert a new row into the users table using the INSERT statement.
  60. READ: Select all rows from the users table using the SELECT statement, and print the rows.
  61. UPDATE: Update the password for the user with the username "john" using the UPDATE statement.
  62. DELETE: Delete the user with the username "john" using the DELETE statement.
  63. Finally, the script closes the cursor and connection to the database.
  64.  
  65. To perform complex joins in this script, you can use the JOIN clause in your SELECT, UPDATE, or DELETE statements. For example, to select all rows from the users table and their corresponding rows in the orders table, you could use a JOIN clause like this:
  66.  
  67.  
  68. cur.execute("SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id")
  69.  
  70.  
  71. This will select all rows from the users and orders tables where the id column in the users table matches the user_id column in the orders table. You can use other types of joins (e.g. LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN) and specify conditions in the ON clause to further filter the results of the join.
















Python software and documentation are licensed under the PSF License Agreement.
Starting with Python 3.8.6, examples, recipes, and other code in the documentation are dual licensed under the PSF License Agreement and the Zero-Clause BSD license.
Some software incorporated into Python is under different licenses. The licenses are listed with code falling under that license. See Licenses and Acknowledgements for Incorporated Software for an incomplete list of these licenses.

Python and it's documentation is:
Copyright © 2001-2022 Python Software Foundation. All rights reserved.
Copyright © 2000 BeOpen.com. All rights reserved.
Copyright © 1995-2000 Corporation for National Research Initiatives. All rights reserved.
Copyright © 1991-1995 Stichting Mathematisch Centrum. All rights reserved.

See History and License for complete license and permissions information:
https://docs.python.org/3/license.html#psf-license
  • Recent Pastes