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 that demonstrates how to perform CRUD (create, read, update, and delete) operations on a PostgreSQL database using SQLAlchemy
By Guest on 21st December 2022 11:09:40 PM | Syntax: PYTHON | Views: 194


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 using the SQLAlchemy library, including how to perform complex joins:
  2.  
  3.  
  4. from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
  5. from sqlalchemy.ext.declarative import declarative_base
  6. from sqlalchemy.orm import sessionmaker, relationship
  7.  
  8. # Connect to the database
  9. engine = create_engine("postgresql://myuser:mypassword@localhost:5432/mydatabase")
  10.  
  11. # Create the base class for declarative class definitions
  12. Base = declarative_base()
  13.  
  14. # Define the ORM models for the tables
  15. class User(Base):
  16.     __tablename__ = "users"
  17.     id = Column(Integer, primary_key=True)
  18.     username = Column(String)
  19.     password = Column(String)
  20.  
  21. class Order(Base):
  22.     __tablename__ = "orders"
  23.     id = Column(Integer, primary_key=True)
  24.     user_id = Column(Integer, ForeignKey("users.id"))
  25.     item = Column(String)
  26.     user = relationship("User", back_populates="orders")
  27.  
  28. # Create the tables in the database
  29. Base.metadata.create_all(engine)
  30.  
  31. # Create a session to manage the database interactions
  32. Session = sessionmaker(bind=engine)
  33. session = Session()
  34.  
  35. # CREATE
  36. # Insert a new user into the table
  37. user = User(username="john", password="password123")
  38. session.add(user)
  39. session.commit()
  40.  
  41. # Insert a new order for the user
  42. order = Order(user=user, item="phone")
  43. session.add(order)
  44. session.commit()
  45.  
  46. # READ
  47. # Select all users and their orders from the tables
  48. users = session.query(User).outerjoin(User.orders).all()
  49.  
  50. # Print the users and their orders
  51. for user in users:
  52.     print(f"Username: {user.username}")
  53.     for order in user.orders:
  54.         print(f" - Order: {order.item}")
  55.  
  56. # UPDATE
  57. # Update the password for the user with the username "john"
  58. user = session.query(User).filter(User.username == "john").one()
  59. user.password = "newpassword456"
  60. session.commit()
  61.  
  62. # DELETE
  63. # Delete the order with the item "phone"
  64. order = session.query(Order).filter(Order.item == "phone").one()
  65. session.delete(order)
  66. session.commit()
  67.  
  68. # Close the session
  69. session.close()
  70.  
  71.  
  72. This script first connects to the PostgreSQL database using the SQLAlchemy create_engine function. It then defines the ORM (Object-Relational Mapping) models for the users and orders tables using the declarative_base and Column classes. The relationship function is used to define the relationship between the User and Order models.
  73.  
  74. The script then creates the tables in the database using the create_all function. Next, it creates a session using the sessionmaker function, which allows you to manage the database interactions using the ORM models.
  75.  
  76.  
  77. # CREATE
  78. # Insert a new user into the table
  79. user = User(username="john", password="password123")
  80. session.add(user)
  81. session.commit()
  82.  
  83. # Insert a new order for the user
  84. order = Order(user=user, item="phone")
  85. session.add(order)
  86. session.commit()
  87.  
  88. # READ
  89. # Select all users and their orders from the tables
  90. users = session.query(User).outerjoin(User.orders).all()
  91.  
  92. # Print the users and their orders
  93. for user in users:
  94.     print(f"Username: {user.username}")
  95.     for order in user.orders:
  96.         print(f" - Order: {order.item}")
  97.  
  98. # UPDATE
  99. # Update the password for the user with the username "john"
  100. user = session.query(User).filter(User.username == "john").one()
  101. user.password = "newpassword456"
  102. session.commit()
  103.  
  104. # DELETE
  105. # Delete the order with the item "phone"
  106. order = session.query(Order).filter(Order.item == "phone").one()
  107. session.delete(order)
  108. session.commit()
  109.  
  110. # Close the session
  111. session.close()
  112.  
  113.  
  114. This script demonstrates how to perform each of the CRUD operations using the ORM models and the session object:
  115.  
  116. CREATE: Insert a new user and order into the users and orders tables by creating new User and Order objects and adding them to the session using the add method.
  117. READ: Select all users and their orders from the users and orders tables using the query method and the outerjoin function, and print the results.
  118. UPDATE: Update the password for the user with the username "john" by querying for the user using the query method and the filter function, updating the password attribute of the User object, and committing the changes to the database using the commit method.
  119. DELETE: Delete the order with the item "phone" by querying for the order using the query method and the filter function, deleting the Order object using the delete method, and committing the changes to the database using the commit method.
  120. Finally, the script closes the session using the close method.
  121.  
  122. To perform complex joins in this script, you can use the join function in your query method. For example, to select all users and their corresponding orders from the users and orders tables, you could use a join function like this:
  123.  
  124.  
  125. users = session.query(User).join(User.orders).all()
  126.  
  127.  
  128.  
  129. This will select all users and their orders 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. outerjoin, fullouterjoin) and specify conditions in the filter function to further filter the results of the join.
  130.  
  131.  
  132. Watch: SQLAlchemy Turns Python Objects Into Database Entries
















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