Category: sqlalchemy

Many to Many Relationships ”; Previous Next Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys – one from each table’s primary key. Moreover, classes mapping to the two tables have an attribute with a collection of objects of other association tables assigned as secondary attribute of relationship() function. For this purpose, we shall create a SQLite database (mycollege.db) with two tables – department and employee. Here, we assume that an employee is a part of more than one department, and a department has more than one employee. This constitutes many-to-many relationship. Definition of Employee and Department classes mapped to department and employee table is as follows − from sqlalchemy import create_engine, ForeignKey, Column, Integer, String engine = create_engine(”sqlite:///mycollege.db”, echo = True) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() from sqlalchemy.orm import relationship class Department(Base): __tablename__ = ”department” id = Column(Integer, primary_key = True) name = Column(String) employees = relationship(”Employee”, secondary = ”link”) class Employee(Base): __tablename__ = ”employee” id = Column(Integer, primary_key = True) name = Column(String) departments = relationship(Department,secondary=”link”) We now define a Link class. It is linked to link table and contains department_id and employee_id attributes respectively referencing to primary keys of department and employee table. class Link(Base): __tablename__ = ”link” department_id = Column( Integer, ForeignKey(”department.id”), primary_key = True) employee_id = Column( Integer, ForeignKey(”employee.id”), primary_key = True) Here, we have to make a note that Department class has employees attribute related to Employee class. The relationship function’s secondary attribute is assigned a link as its value. Similarly, Employee class has departments attribute related to Department class. The relationship function’s secondary attribute is assigned a link as its value. All these three tables are created when the following statement is executed − Base.metadata.create_all(engine) The Python console emits following CREATE TABLE queries − CREATE TABLE department ( id INTEGER NOT NULL, name VARCHAR, PRIMARY KEY (id) ) CREATE TABLE employee ( id INTEGER NOT NULL, name VARCHAR, PRIMARY KEY (id) ) CREATE TABLE link ( department_id INTEGER NOT NULL, employee_id INTEGER NOT NULL, PRIMARY KEY (department_id, employee_id), FOREIGN KEY(department_id) REFERENCES department (id), FOREIGN KEY(employee_id) REFERENCES employee (id) ) We can check this by opening mycollege.db using SQLiteStudio as shown in the screenshots given below − Next we create three objects of Department class and three objects of Employee class as shown below − d1 = Department(name = “Accounts”) d2 = Department(name = “Sales”) d3 = Department(name = “Marketing”) e1 = Employee(name = “John”) e2 = Employee(name = “Tony”) e3 = Employee(name = “Graham”) Each table has a collection attribute having append() method. We can add Employee objects to Employees collection of Department object. Similarly, we can add Department objects to departments collection attribute of Employee objects. e1.departments.append(d1) e2.departments.append(d3) d1.employees.append(e3) d2.employees.append(e2) d3.employees.append(e1) e3.departments.append(d2) All we have to do now is to set up a session object, add all objects to it and commit the changes as shown below − from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() session.add(e1) session.add(e2) session.add(d1) session.add(d2) session.add(d3) session.add(e3) session.commit() Following SQL statements will be emitted on Python console − INSERT INTO department (name) VALUES (?) (”Accounts”,) INSERT INTO department (name) VALUES (?) (”Sales”,) INSERT INTO department (name) VALUES (?) (”Marketing”,) INSERT INTO employee (name) VALUES (?) (”John”,) INSERT INTO employee (name) VALUES (?) (”Graham”,) INSERT INTO employee (name) VALUES (?) (”Tony”,) INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3)) INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3)) To check the effect of above operations, use SQLiteStudio and view data in department, employee and link tables − To display the data, run the following query statement − from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() for x in session.query( Department, Employee).filter(Link.department_id == Department.id, Link.employee_id == Employee.id).order_by(Link.department_id).all(): print (“Department: {} Name: {}”.format(x.Department.name, x.Employee.name)) As per the data populated in our example, output will be displayed as below − Department: Accounts Name: John Department: Accounts Name: Graham Department: Sales Name: Graham Department: Sales Name: Tony Department: Marketing Name: John Department: Marketing Name: Tony Print Page Previous Next Advertisements ”;

SQLAlchemy – Useful Resources ”; Previous Next The following resources contain additional information on SQLAlchemy. Please use them to get more in-depth knowledge on this topic. Useful Links on SQLAlchemy SQLAlchemy Wikipedia– A Complete Wikipedia of SQLAlchemy. SQLAlchemy Official Website– SQLAlchemy Official Website Useful Books on SQLAlchemy To enlist your site on this page, please drop an email to [email protected] Print Page Previous Next Advertisements ”;

SQLAlchemy – Quick Guide ”; Previous Next SQLAlchemy – Introduction SQLAlchemy is a popular SQL toolkit and Object Relational Mapper. It is written in Python and gives full power and flexibility of SQL to an application developer. It is an open source and cross-platform software released under MIT license. SQLAlchemy is famous for its object-relational mapper (ORM), using which, classes can be mapped to the database, thereby allowing the object model and database schema to develop in a cleanly decoupled way from the beginning. As size and performance of SQL databases start to matter, they behave less like object collections. On the other hand, as abstraction in object collections starts to matter, they behave less like tables and rows. SQLAlchemy aims to accommodate both of these principles. For this reason, it has adopted the data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs. Databases and SQL will be viewed in a different perspective using SQLAlchemy. Michael Bayer is the original author of SQLAlchemy. Its initial version was released in February 2006. Latest version is numbered as 1.2.7, released as recently as in April 2018. What is ORM? ORM (Object Relational Mapping) is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Usually, the type system used in an Object Oriented (OO) language like Python contains non-scalar types. These cannot be expressed as primitive types such as integers and strings. Hence, the OO programmer has to convert objects in scalar data to interact with backend database. However, data types in most of the database products such as Oracle, MySQL, etc., are primary. In an ORM system, each class maps to a table in the underlying database. Instead of writing tedious database interfacing code yourself, an ORM takes care of these issues for you while you can focus on programming the logics of the system. SQLAlchemy – Environment setup Let us discuss the environmental setup required to use SQLAlchemy. Any version of Python higher than 2.7 is necessary to install SQLAlchemy. The easiest way to install is by using Python Package Manager, pip. This utility is bundled with standard distribution of Python. pip install sqlalchemy Using the above command, we can download the latest released version of SQLAlchemy from python.org and install it to your system. In case of anaconda distribution of Python, SQLAlchemy can be installed from conda terminal using the below command − conda install -c anaconda sqlalchemy It is also possible to install SQLAlchemy from below source code − python setup.py install SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database. It uses dialect system to communicate with various types of DBAPI implementations and databases. All dialects require that an appropriate DBAPI driver is installed. The following are the dialects included − Firebird Microsoft SQL Server MySQL Oracle PostgreSQL SQLite Sybase To check if SQLAlchemy is properly installed and to know its version, enter the following command in the Python prompt − >>> import sqlalchemy >>>sqlalchemy.__version__ ”1.2.7” SQLAlchemy Core – Expression Language SQLAlchemy core includes SQL rendering engine, DBAPI integration, transaction integration, and schema description services. SQLAlchemy core uses SQL Expression Language that provides a schema-centric usage paradigm whereas SQLAlchemy ORM is a domain-centric mode of usage. The SQL Expression Language presents a system of representing relational database structures and expressions using Python constructs. It presents a system of representing the primitive constructs of the relational database directly without opinion, which is in contrast to ORM that presents a high level and abstracted pattern of usage, which itself is an example of applied usage of the Expression Language. Expression Language is one of the core components of SQLAlchemy. It allows the programmer to specify SQL statements in Python code and use it directly in more complex queries. Expression language is independent of backend and comprehensively covers every aspect of raw SQL. It is closer to raw SQL than any other component in SQLAlchemy. Expression Language represents the primitive constructs of the relational database directly. Because the ORM is based on top of Expression language, a typical Python database application may have overlapped use of both. The application may use expression language alone, though it has to define its own system of translating application concepts into individual database queries. Statements of Expression language will be translated into corresponding raw SQL queries by SQLAlchemy engine. We shall now learn how to create the engine and execute various SQL queries with its help. SQLAlchemy Core – Connecting to Database In the previous chapter, we have discussed about expression Language in SQLAlchemy. Now let us proceed towards the steps involved in connecting to a database. Engine class connects a Pool and Dialect together to provide a source of database connectivity and behavior. An object of Engine class is instantiated using the create_engine() function. The create_engine() function takes the database as one argument. The database is not needed to be defined anywhere. The standard calling form has to send the URL as the first positional argument, usually a string that indicates database dialect and connection arguments. Using the code given below, we can create a database. >>> from sqlalchemy import create_engine >>> engine = create_engine(”sqlite:///college.db”, echo = True) For a MySQL database, use the below command − engine = create_engine(“mysql://user:pwd@localhost/college”,echo = True) To specifically mention DB-API to be used for connection, the URL string takes the form as follows − dialect[+driver]://user:password@host/dbname For example, if you are using PyMySQL driver with MySQL, use the following command − mysql+pymysql://<username>:<password>@<host>/<dbname> The echo flag is a shortcut to set up SQLAlchemy logging, which is accomplished via Python’s standard logging module. In the subsequent chapters, we will learn all the generated SQLs. To hide the verbose output, set echo attribute to None. Other arguments to create_engine() function may be dialect specific. The create_engine() function returns an Engine object. Some important methods of Engine class are − Sr.No. Method & Description 1 connect() Returns connection object 2

Discuss – SQLAlchemy ”; Previous Next SQLAlchemy is a popular SQL toolkit and Object Relational Mapper. It is written in Python and gives full power and flexibility of SQL to an application developer. It is an open source and cross-platform software released under MIT license. SQLAlchemy is famous for its object-relational mapper (ORM), using which classes can be mapped to the database, thereby allowing the object model and database schema to develop in a cleanly decoupled way from the beginning. Print Page Previous Next Advertisements ”;

Working with Related Objects ”; Previous Next In this chapter, we will focus on the related objects in SQLAlchemy ORM. Now when we create a Customer object, a blank invoice collection will be present in the form of Python List. c1 = Customer(name = “Gopal Krishna”, address = “Bank Street Hydarebad”, email = “[email protected]”) The invoices attribute of c1.invoices will be an empty list. We can assign items in the list as − c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)] Let us commit this object to the database using Session object as follows − from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() session.add(c1) session.commit() This will automatically generate INSERT queries for customers and invoices tables − INSERT INTO customers (name, address, email) VALUES (?, ?, ?) (”Gopal Krishna”, ”Bank Street Hydarebad”, ”[email protected]”) INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?) (2, 10, 15000) INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?) (2, 14, 3850) Let us now look at contents of customers table and invoices table in the table view of SQLiteStudio − You can construct Customer object by providing mapped attribute of invoices in the constructor itself by using the below command − c2 = [ Customer( name = “Govind Pant”, address = “Gulmandi Aurangabad”, email = “[email protected]”, invoices = [Invoice(invno = 3, amount = 10000), Invoice(invno = 4, amount = 5000)] ) ] Or a list of objects to be added using add_all() function of session object as shown below − rows = [ Customer( name = “Govind Kala”, address = “Gulmandi Aurangabad”, email = “[email protected]”, invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]), Customer( name = “Abdul Rahman”, address = “Rohtak”, email = “[email protected]”, invoices = [Invoice(invno = 9, amount = 15000), Invoice(invno = 11, amount = 6000) ]) ] session.add_all(rows) session.commit() Print Page Previous Next Advertisements ”;

SQLAlchemy – Dialects ”; Previous Next SQLAlchemy uses system of dialects to communicate with various types of databases. Each database has a corresponding DBAPI wrapper. All dialects require that an appropriate DBAPI driver is installed. Following dialects are included in SQLAlchemy API − Firebird Microsoft SQL Server MySQL Oracle PostgreSQL SQL Sybase An Engine object based on a URL is produced by create_engine() function. These URLs can include username, password, hostname, and database name. There may be optional keyword arguments for additional configuration. In some cases, a file path is accepted, and in others, a “data source name” replaces the “host” and “database” portions. The typical form of a database URL is as follows − dialect+driver://username:password@host:port/database PostgreSQL The PostgreSQL dialect uses psycopg2 as the default DBAPI. pg8000 is also available as a pure-Python substitute as shown below: # default engine = create_engine(”postgresql://scott:tiger@localhost/mydatabase”) # psycopg2 engine = create_engine(”postgresql+psycopg2://scott:tiger@localhost/mydatabase”) # pg8000 engine = create_engine(”postgresql+pg8000://scott:tiger@localhost/mydatabase”) MySQL The MySQL dialect uses mysql-python as the default DBAPI. There are many MySQL DBAPIs available, such as MySQL-connector-python as follows − # default engine = create_engine(”mysql://scott:tiger@localhost/foo”) # mysql-python engine = create_engine(”mysql+mysqldb://scott:tiger@localhost/foo”) # MySQL-connector-python engine = create_engine(”mysql+mysqlconnector://scott:tiger@localhost/foo”) Oracle The Oracle dialect uses cx_oracle as the default DBAPI as follows − engine = create_engine(”oracle://scott:[email protected]:1521/sidname”) engine = create_engine(”oracle+cx_oracle://scott:tiger@tnsname”) Microsoft SQL Server The SQL Server dialect uses pyodbc as the default DBAPI. pymssql is also available. # pyodbc engine = create_engine(”mssql+pyodbc://scott:tiger@mydsn”) # pymssql engine = create_engine(”mssql+pymssql://scott:tiger@hostname:port/dbname”) SQLite SQLite connects to file-based databases, using the Python built-in module sqlite3 by default. As SQLite connects to local files, the URL format is slightly different. The “file” portion of the URL is the filename of the database. For a relative file path, this requires three slashes as shown below − engine = create_engine(”sqlite:///foo.db”) And for an absolute file path, the three slashes are followed by the absolute path as given below − engine = create_engine(”sqlite:///C:\path\to\foo.db”) To use a SQLite:memory:database, specify an empty URL as given below − engine = create_engine(”sqlite://”) Conclusion In the first part of this tutorial, we have learnt how to use the Expression Language to execute SQL statements. Expression language embeds SQL constructs in Python code. In the second part, we have discussed object relation mapping capability of SQLAlchemy. The ORM API maps the SQL tables with Python classes. Print Page Previous Next Advertisements ”;

SQLAlchemy ORM – Filter Operators ”; Previous Next Now, we will learn the filter operations with their respective codes and output. Equals The usual operator used is == and it applies the criteria to check equality. result = session.query(Customers).filter(Customers.id == 2) for row in result: print (“ID:”, row.id, “Name: “,row.name, “Address:”,row.address, “Email:”,row.email) SQLAlchemy will send following SQL expression − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.id = ? The output for the above code is as follows − ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: [email protected] Not Equals The operator used for not equals is != and it provides not equals criteria. result = session.query(Customers).filter(Customers.id! = 2) for row in result: print (“ID:”, row.id, “Name: “,row.name, “Address:”,row.address, “Email:”,row.email) The resulting SQL expression is − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.id != ? The output for the above lines of code is as follows − ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected] ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected] ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected] Like like() method itself produces the LIKE criteria for WHERE clause in the SELECT expression. result = session.query(Customers).filter(Customers.name.like(”Ra%”)) for row in result: print (“ID:”, row.id, “Name: “,row.name, “Address:”,row.address, “Email:”,row.email) Above SQLAlchemy code is equivalent to following SQL expression − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.name LIKE ? And the output for the above code is − ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected] ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected] IN This operator checks whether the column value belongs to a collection of items in a list. It is provided by in_() method. result = session.query(Customers).filter(Customers.id.in_([1,3])) for row in result: print (“ID:”, row.id, “Name: “,row.name, “Address:”,row.address, “Email:”,row.email) Here, the SQL expression evaluated by SQLite engine will be as follows − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.id IN (?, ?) The output for the above code is as follows − ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected] ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected] AND This conjunction is generated by either putting multiple commas separated criteria in the filter or using and_() method as given below − result = session.query(Customers).filter(Customers.id>2, Customers.name.like(”Ra%”)) for row in result: print (“ID:”, row.id, “Name: “,row.name, “Address:”,row.address, “Email:”,row.email) from sqlalchemy import and_ result = session.query(Customers).filter(and_(Customers.id>2, Customers.name.like(”Ra%”))) for row in result: print (“ID:”, row.id, “Name: “,row.name, “Address:”,row.address, “Email:”,row.email) Both the above approaches result in similar SQL expression − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.id > ? AND customers.name LIKE ? The output for the above lines of code is − ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected] OR This conjunction is implemented by or_() method. from sqlalchemy import or_ result = session.query(Customers).filter(or_(Customers.id>2, Customers.name.like(”Ra%”))) for row in result: print (“ID:”, row.id, “Name: “,row.name, “Address:”,row.address, “Email:”,row.email) As a result, SQLite engine gets following equivalent SQL expression − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.id > ? OR customers.name LIKE ? The output for the above code is as follows − ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected] ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected] ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected] Print Page Previous Next Advertisements ”;

SQLAlchemy ORM – Textual SQL ”; Previous Next Earlier, textual SQL using text() function has been explained from the perspective of core expression language of SQLAlchemy. Now we shall discuss it from ORM point of view. Literal strings can be used flexibly with Query object by specifying their use with the text() construct. Most applicable methods accept it. For example, filter() and order_by(). In the example given below, the filter() method translates the string “id<3” to the WHERE id<3 from sqlalchemy import text for cust in session.query(Customers).filter(text(“id<3”)): print(cust.name) The raw SQL expression generated shows conversion of filter to WHERE clause with the code illustrated below − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE id<3 From our sample data in Customers table, two rows will be selected and name column will be printed as follows − Ravi Kumar Komal Pande To specify bind parameters with string-based SQL, use a colon,and to specify the values, use the params() method. cust = session.query(Customers).filter(text(“id = :value”)).params(value = 1).one() The effective SQL displayed on Python console will be as given below − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE id = ? To use an entirely string-based statement, a text() construct representing a complete statement can be passed to from_statement(). session.query(Customers).from_statement(text(“SELECT * FROM customers”)).all() The result of above code will be a basic SELECT statement as given below − SELECT * FROM customers Obviously, all records in customers table will be selected. The text() construct allows us to link its textual SQL to Core or ORM-mapped column expressions positionally. We can achieve this by passing column expressions as positional arguments to the TextClause.columns() method. stmt = text(“SELECT name, id, name, address, email FROM customers”) stmt = stmt.columns(Customers.id, Customers.name) session.query(Customers.id, Customers.name).from_statement(stmt).all() The id and name columns of all rows will be selected even though the SQLite engine executes following expression generated by above code shows all columns in text() method − SELECT name, id, name, address, email FROM customers Print Page Previous Next Advertisements ”;

SQLAlchemy ORM – Deleting Related Objects ”; Previous Next It is easy to perform delete operation on a single table. All you have to do is to delete an object of the mapped class from a session and commit the action. However, delete operation on multiple related tables is little tricky. In our sales.db database, Customer and Invoice classes are mapped to customer and invoice table with one to many type of relationship. We will try to delete Customer object and see the result. As a quick reference, below are the definitions of Customer and Invoice classes − from sqlalchemy import create_engine, ForeignKey, Column, Integer, String engine = create_engine(”sqlite:///sales.db”, echo = True) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() from sqlalchemy.orm import relationship class Customer(Base): __tablename__ = ”customers” id = Column(Integer, primary_key = True) name = Column(String) address = Column(String) email = Column(String) class Invoice(Base): __tablename__ = ”invoices” id = Column(Integer, primary_key = True) custid = Column(Integer, ForeignKey(”customers.id”)) invno = Column(Integer) amount = Column(Integer) customer = relationship(“Customer”, back_populates = “invoices”) Customer.invoices = relationship(“Invoice”, order_by = Invoice.id, back_populates = “customer”) We setup a session and obtain a Customer object by querying it with primary ID using the below program − from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind=engine) session = Session() x = session.query(Customer).get(2) In our sample table, x.name happens to be ”Gopal Krishna”. Let us delete this x from the session and count the occurrence of this name. session.delete(x) session.query(Customer).filter_by(name = ”Gopal Krishna”).count() The resulting SQL expression will return 0. SELECT count(*) AS count_1 FROM ( SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.name = ?) AS anon_1(”Gopal Krishna”,) 0 However, the related Invoice objects of x are still there. It can be verified by the following code − session.query(Invoice).filter(Invoice.invno.in_([10,14])).count() Here, 10 and 14 are invoice numbers belonging to customer Gopal Krishna. Result of the above query is 2, which means the related objects have not been deleted. SELECT count(*) AS count_1 FROM ( SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount FROM invoices WHERE invoices.invno IN (?, ?)) AS anon_1(10, 14) 2 This is because SQLAlchemy doesn’t assume the deletion of cascade; we have to give a command to delete it. To change the behavior, we configure cascade options on the User.addresses relationship. Let us close the ongoing session, use new declarative_base() and redeclare the User class, adding in the addresses relationship including the cascade configuration. The cascade attribute in relationship function is a comma-separated list of cascade rules which determines how Session operations should be “cascaded” from parent to child. By default, it is False, which means that it is “save-update, merge”. The available cascades are as follows − save-update merge expunge delete delete-orphan refresh-expire Often used option is “all, delete-orphan” to indicate that related objects should follow along with the parent object in all cases, and be deleted when de-associated. Hence redeclared Customer class is shown below − class Customer(Base): __tablename__ = ”customers” id = Column(Integer, primary_key = True) name = Column(String) address = Column(String) email = Column(String) invoices = relationship( “Invoice”, order_by = Invoice.id, back_populates = “customer”, cascade = “all, delete, delete-orphan” ) Let us delete the Customer with Gopal Krishna name using the below program and see the count of its related Invoice objects − from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() x = session.query(Customer).get(2) session.delete(x) session.query(Customer).filter_by(name = ”Gopal Krishna”).count() session.query(Invoice).filter(Invoice.invno.in_([10,14])).count() The count is now 0 with following SQL emitted by above script − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.id = ? (2,) SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount FROM invoices WHERE ? = invoices.custid ORDER BY invoices.id (2,) DELETE FROM invoices WHERE invoices.id = ? ((1,), (2,)) DELETE FROM customers WHERE customers.id = ? (2,) SELECT count(*) AS count_1 FROM ( SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE customers.name = ?) AS anon_1(”Gopal Krishna”,) SELECT count(*) AS count_1 FROM ( SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount FROM invoices WHERE invoices.invno IN (?, ?)) AS anon_1(10, 14) 0 Print Page Previous Next Advertisements ”;

Common Relationship Operators ”; Previous Next In this chapter, we will discuss about the operators which build on relationships. __eq__() The above operator is a many-to-one “equals” comparison. The line of code for this operator is as shown below − s = session.query(Customer).filter(Invoice.invno.__eq__(12)) The equivalent SQL query for the above line of code is − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers, invoices WHERE invoices.invno = ? __ne__() This operator is a many-to-one “not equals” comparison. The line of code for this operator is as shown below − s = session.query(Customer).filter(Invoice.custid.__ne__(2)) The equivalent SQL query for the above line of code is given below − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers, invoices WHERE invoices.custid != ? contains() This operator is used for one-to-many collections and given below is the code for contains() − s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5])) The equivalent SQL query for the above line of code is − SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount FROM invoices WHERE (invoices.invno LIKE ”%” + ? || ”%”) any() any() operator is used for collections as shown below − s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11)) The equivalent SQL query for the above line of code is shown below − SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email FROM customers WHERE EXISTS ( SELECT 1 FROM invoices WHERE customers.id = invoices.custid AND invoices.invno = ?) has() This operator is used for scalar references as follows − s = session.query(Invoice).filter(Invoice.customer.has(name = ”Arjun Pandit”)) The equivalent SQL query for the above line of code is − SELECT invoices.id AS invoices_id, invoices.custid AS invoices_custid, invoices.invno AS invoices_invno, invoices.amount AS invoices_amount FROM invoices WHERE EXISTS ( SELECT 1 FROM customers WHERE customers.id = invoices.custid AND customers.name = ?) Print Page Previous Next Advertisements ”;