Category: sqlalchemy

Using UPDATE Expression ”; Previous Next The update() method on target table object constructs equivalent UPDATE SQL expression. table.update().where(conditions).values(SET expressions) The values() method on the resultant update object is used to specify the SET conditions of the UPDATE. If left as None, the SET conditions are determined from those parameters passed to the statement during the execution and/or compilation of the statement. The where clause is an Optional expression describing the WHERE condition of the UPDATE statement. Following code snippet changes value of ‘lastname’ column from ‘Khanna’ to ‘Kapoor’ in students table − stmt = students.update().where(students.c.lastname == ”Khanna”).values(lastname = ”Kapoor”) The stmt object is an update object that translates to − ”UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1” The bound parameter lastname_1 will be substituted when execute() method is invoked. The complete update code is given below − from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String engine = create_engine(”sqlite:///college.db”, echo = True) meta = MetaData() students = Table( ”students”, meta, Column(”id”, Integer, primary_key = True), Column(”name”, String), Column(”lastname”, String), ) conn = engine.connect() stmt=students.update().where(students.c.lastname==”Khanna”).values(lastname=”Kapoor”) conn.execute(stmt) s = students.select() conn.execute(s).fetchall() The above code displays following output with second row showing effect of update operation as in the screenshot given − [ (1, ”Ravi”, ”Kapoor”), (2, ”Rajiv”, ”Kapoor”), (3, ”Komal”, ”Bhandari”), (4, ”Abdul”, ”Sattar”), (5, ”Priya”, ”Rajhans”) ] Note that similar functionality can also be achieved by using update() function in sqlalchemy.sql.expression module as shown below − from sqlalchemy.sql.expression import update stmt = update(students).where(students.c.lastname == ”Khanna”).values(lastname = ”Kapoor”) Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Using Conjunctions ”; Previous Next Conjunctions are functions in SQLAlchemy module that implement relational operators used in WHERE clause of SQL expressions. The operators AND, OR, NOT, etc., are used to form a compound expression combining two individual logical expressions. A simple example of using AND in SELECT statement is as follows − SELECT * from EMPLOYEE WHERE salary>10000 AND age>30 SQLAlchemy functions and_(), or_() and not_() respectively implement AND, OR and NOT operators. and_() function It produces a conjunction of expressions joined by AND. An example is given below for better understanding − from sqlalchemy import and_ print( and_( students.c.name == ”Ravi”, students.c.id <3 ) ) This translates to − students.name = :name_1 AND students.id < :id_1 To use and_() in a select() construct on a students table, use the following line of code − stmt = select([students]).where(and_(students.c.name == ”Ravi”, students.c.id <3)) SELECT statement of the following nature will be constructed − SELECT students.id, students.name, students.lastname FROM students WHERE students.name = :name_1 AND students.id < :id_1 The complete code that displays output of the above SELECT query is as follows − from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select engine = create_engine(”sqlite:///college.db”, echo = True) meta = MetaData() conn = engine.connect() students = Table( ”students”, meta, Column(”id”, Integer, primary_key = True), Column(”name”, String), Column(”lastname”, String), ) from sqlalchemy import and_, or_ stmt = select([students]).where(and_(students.c.name == ”Ravi”, students.c.id <3)) result = conn.execute(stmt) print (result.fetchall()) Following row will be selected assuming that students table is populated with data used in previous example − [(1, ”Ravi”, ”Kapoor”)] or_() function It produces conjunction of expressions joined by OR. We shall replace the stmt object in the above example with the following one using or_() stmt = select([students]).where(or_(students.c.name == ”Ravi”, students.c.id <3)) Which will be effectively equivalent to following SELECT query − SELECT students.id, students.name, students.lastname FROM students WHERE students.name = :name_1 OR students.id < :id_1 Once you make the substitution and run the above code, the result will be two rows falling in the OR condition − [(1, ”Ravi”, ”Kapoor”), (2, ”Rajiv”, ”Khanna”)] asc() function It produces an ascending ORDER BY clause. The function takes the column to apply the function as a parameter. from sqlalchemy import asc stmt = select([students]).order_by(asc(students.c.name)) The statement implements following SQL expression − SELECT students.id, students.name, students.lastname FROM students ORDER BY students.name ASC Following code lists out all records in students table in ascending order of name column − from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select engine = create_engine(”sqlite:///college.db”, echo = True) meta = MetaData() conn = engine.connect() students = Table( ”students”, meta, Column(”id”, Integer, primary_key = True), Column(”name”, String), Column(”lastname”, String), ) from sqlalchemy import asc stmt = select([students]).order_by(asc(students.c.name)) result = conn.execute(stmt) for row in result: print (row) Above code produces following output − (4, ”Abdul”, ”Sattar”) (3, ”Komal”, ”Bhandari”) (5, ”Priya”, ”Rajhans”) (2, ”Rajiv”, ”Khanna”) (1, ”Ravi”, ”Kapoor”) desc() function Similarly desc() function produces descending ORDER BY clause as follows − from sqlalchemy import desc stmt = select([students]).order_by(desc(students.c.lastname)) The equivalent SQL expression is − SELECT students.id, students.name, students.lastname FROM students ORDER BY students.lastname DESC And the output for the above lines of code is − (4, ”Abdul”, ”Sattar”) (5, ”Priya”, ”Rajhans”) (2, ”Rajiv”, ”Khanna”) (1, ”Ravi”, ”Kapoor”) (3, ”Komal”, ”Bhandari”) between() function It produces a BETWEEN predicate clause. This is generally used to validate if value of a certain column falls between a range. For example, following code selects rows for which id column is between 2 and 4 − from sqlalchemy import between stmt = select([students]).where(between(students.c.id,2,4)) print (stmt) The resulting SQL expression resembles − SELECT students.id, students.name, students.lastname FROM students WHERE students.id BETWEEN :id_1 AND :id_2 and the result is as follows − (2, ”Rajiv”, ”Khanna”) (3, ”Komal”, ”Bhandari”) (4, ”Abdul”, ”Sattar”) Print Page Previous Next Advertisements ”;

Parameter-Ordered Updates ”; Previous Next The UPDATE query of raw SQL has SET clause. It is rendered by the update() construct using the column ordering given in the originating Table object. Therefore, a particular UPDATE statement with particular columns will be rendered the same each time. Since the parameters themselves are passed to the Update.values() method as Python dictionary keys, there is no other fixed ordering available. In some cases, the order of parameters rendered in the SET clause are significant. In MySQL, providing updates to column values is based on that of other column values. Following statement’s result − UPDATE table1 SET x = y + 10, y = 20 will have a different result than − UPDATE table1 SET y = 20, x = y + 10 SET clause in MySQL is evaluated on a per-value basis and not on per-row basis. For this purpose, the preserve_parameter_order is used. Python list of 2-tuples is given as argument to the Update.values() method − stmt = table1.update(preserve_parameter_order = True). values([(table1.c.y, 20), (table1.c.x, table1.c.y + 10)]) The List object is similar to dictionary except that it is ordered. This ensures that the “y” column’s SET clause will render first, then the “x” column’s SET clause. Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Using Joins ”; Previous Next In this chapter, we will learn how to use Joins in SQLAlchemy. Effect of joining is achieved by just placing two tables in either the columns clause or the where clause of the select() construct. Now we use the join() and outerjoin() methods. The join() method returns a join object from one table object to another. join(right, onclause = None, isouter = False, full = False) The functions of the parameters mentioned in the above code are as follows − right − the right side of the join; this is any Table object onclause − a SQL expression representing the ON clause of the join. If left at None, it attempts to join the two tables based on a foreign key relationship isouter − if True, renders a LEFT OUTER JOIN, instead of JOIN full − if True, renders a FULL OUTER JOIN, instead of LEFT OUTER JOIN For example, following use of join() method will automatically result in join based on the foreign key. >>> print(students.join(addresses)) This is equivalent to following SQL expression − students JOIN addresses ON students.id = addresses.st_id You can explicitly mention joining criteria as follows − j = students.join(addresses, students.c.id == addresses.c.st_id) If we now build the below select construct using this join as − stmt = select([students]).select_from(j) This will result in following SQL expression − SELECT students.id, students.name, students.lastname FROM students JOIN addresses ON students.id = addresses.st_id If this statement is executed using the connection representing engine, data belonging to selected columns will be displayed. The complete code is as follows − from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey engine = create_engine(”sqlite:///college.db”, echo = True) meta = MetaData() conn = engine.connect() students = Table( ”students”, meta, Column(”id”, Integer, primary_key = True), Column(”name”, String), Column(”lastname”, String), ) addresses = Table( ”addresses”, meta, Column(”id”, Integer, primary_key = True), Column(”st_id”, Integer,ForeignKey(”students.id”)), Column(”postal_add”, String), Column(”email_add”, String) ) from sqlalchemy import join from sqlalchemy.sql import select j = students.join(addresses, students.c.id == addresses.c.st_id) stmt = select([students]).select_from(j) result = conn.execute(stmt) result.fetchall() The following is the output of the above code − [ (1, ”Ravi”, ”Kapoor”), (1, ”Ravi”, ”Kapoor”), (3, ”Komal”, ”Bhandari”), (5, ”Priya”, ”Rajhans”), (2, ”Rajiv”, ”Khanna”) ] Print Page Previous Next Advertisements ”;

SQLAlchemy ORM – Adding Objects ”; Previous Next In the previous chapters of SQLAlchemy ORM, we have learnt how to declare mapping and create sessions. In this chapter, we will learn how to add objects to the table. We have declared Customer class that has been mapped to customers table. We have to declare an object of this class and persistently add it to the table by add() method of session object. c1 = Sales(name = ”Ravi Kumar”, address = ”Station Road Nanded”, email = ”[email protected]”) session.add(c1) Note that this transaction is pending until the same is flushed using commit() method. session.commit() Following is the complete script to add a record in customers table − from sqlalchemy import Column, Integer, String from sqlalchemy import create_engine engine = create_engine(”sqlite:///sales.db”, echo = True) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Customers(Base): __tablename__ = ”customers” id = Column(Integer, primary_key=True) name = Column(String) address = Column(String) email = Column(String) from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() c1 = Customers(name = ”Ravi Kumar”, address = ”Station Road Nanded”, email = ”[email protected]”) session.add(c1) session.commit() To add multiple records, we can use add_all() method of the session class. session.add_all([ Customers(name = ”Komal Pande”, address = ”Koti, Hyderabad”, email = ”[email protected]”), Customers(name = ”Rajender Nath”, address = ”Sector 40, Gurgaon”, email = ”[email protected]”), Customers(name = ”S.M.Krishna”, address = ”Budhwar Peth, Pune”, email = ”[email protected]”)] ) session.commit() Table view of SQLiteStudio shows that the records are persistently added in customers table. The following image shows the result − Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Using Set Operations ”; Previous Next In the last chapter, we have learnt about various functions such as max(), min(), count(), etc., here, we will learn about set operations and their uses. Set operations such as UNION and INTERSECT are supported by standard SQL and most of its dialect. SQLAlchemy implements them with the help of following functions − union() While combining results of two or more SELECT statements, UNION eliminates duplicates from the resultset. The number of columns and datatype must be same in both the tables. The union() function returns a CompoundSelect object from multiple tables. Following example demonstrates its use − from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union engine = create_engine(”sqlite:///college.db”, echo = True) meta = MetaData() conn = engine.connect() addresses = Table( ”addresses”, meta, Column(”id”, Integer, primary_key = True), Column(”st_id”, Integer), Column(”postal_add”, String), Column(”email_add”, String) ) u = union(addresses.select().where(addresses.c.email_add.like(”%@gmail.com addresses.select().where(addresses.c.email_add.like(”%@yahoo.com”)))) result = conn.execute(u) result.fetchall() The union construct translates to following SQL expression − SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM addresses WHERE addresses.email_add LIKE ? UNION SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM addresses WHERE addresses.email_add LIKE ? From our addresses table, following rows represent the union operation − [ (1, 1, ”Shivajinagar Pune”, ”[email protected]”), (2, 1, ”ChurchGate Mumbai”, ”[email protected]”), (3, 3, ”Jubilee Hills Hyderabad”, ”[email protected]”), (4, 5, ”MG Road Bangaluru”, ”[email protected]”) ] union_all() UNION ALL operation cannot remove the duplicates and cannot sort the data in the resultset. For example, in above query, UNION is replaced by UNION ALL to see the effect. u = union_all(addresses.select().where(addresses.c.email_add.like(”%@gmail.com”)), addresses.select().where(addresses.c.email_add.like(”%@yahoo.com”))) The corresponding SQL expression is as follows − SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM addresses WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM addresses WHERE addresses.email_add LIKE ? except_() The SQL EXCEPT clause/operator is used to combine two SELECT statements and return rows from the first SELECT statement that are not returned by the second SELECT statement. The except_() function generates a SELECT expression with EXCEPT clause. In the following example, the except_() function returns only those records from addresses table that have ‘gmail.com’ in email_add field but excludes those which have ‘Pune’ as part of postal_add field. u = except_(addresses.select().where(addresses.c.email_add.like(”%@gmail.com”)), addresses.select().where(addresses.c.postal_add.like(”%Pune”))) Result of the above code is the following SQL expression − SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM addresses WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM addresses WHERE addresses.postal_add LIKE ? Assuming that addresses table contains data used in earlier examples, it will display following output − [(2, 1, ”ChurchGate Mumbai”, ”[email protected]”), (3, 3, ”Jubilee Hills Hyderabad”, ”[email protected]”)] intersect() Using INTERSECT operator, SQL displays common rows from both the SELECT statements. The intersect() function implements this behaviour. In following examples, two SELECT constructs are parameters to intersect() function. One returns rows containing ‘gmail.com’ as part of email_add column, and other returns rows having ‘Pune’ as part of postal_add column. The result will be common rows from both resultsets. u = intersect(addresses.select().where(addresses.c.email_add.like(”%@gmail.com”)), addresses.select().where(addresses.c.postal_add.like(”%Pune”))) In effect, this is equivalent to following SQL statement − SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM addresses WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM addresses WHERE addresses.postal_add LIKE ? The two bound parameters ‘%gmail.com’ and ‘%Pune’ generate a single row from original data in addresses table as shown below − [(1, 1, ”Shivajinagar Pune”, ”[email protected]”)] Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Using Multiple Tables ”; Previous Next One of the important features of RDBMS is establishing relation between tables. SQL operations like SELECT, UPDATE and DELETE can be performed on related tables. This section describes these operations using SQLAlchemy. For this purpose, two tables are created in our SQLite database (college.db). The students table has the same structure as given in the previous section; whereas the addresses table has st_id column which is mapped to id column in students table using foreign key constraint. The following code will create two tables in college.db − from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey engine = create_engine(”sqlite:///college.db”, echo=True) meta = MetaData() students = Table( ”students”, meta, Column(”id”, Integer, primary_key = True), Column(”name”, String), Column(”lastname”, String), ) addresses = Table( ”addresses”, meta, Column(”id”, Integer, primary_key = True), Column(”st_id”, Integer, ForeignKey(”students.id”)), Column(”postal_add”, String), Column(”email_add”, String)) meta.create_all(engine) Above code will translate to CREATE TABLE queries for students and addresses table as below − CREATE TABLE students ( id INTEGER NOT NULL, name VARCHAR, lastname VARCHAR, PRIMARY KEY (id) ) CREATE TABLE addresses ( id INTEGER NOT NULL, st_id INTEGER, postal_add VARCHAR, email_add VARCHAR, PRIMARY KEY (id), FOREIGN KEY(st_id) REFERENCES students (id) ) The following screenshots present the above code very clearly − These tables are populated with data by executing insert() method of table objects. To insert 5 rows in students table, you can use the code given below − from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String engine = create_engine(”sqlite:///college.db”, echo = True) meta = MetaData() conn = engine.connect() students = Table( ”students”, meta, Column(”id”, Integer, primary_key = True), Column(”name”, String), Column(”lastname”, String), ) conn.execute(students.insert(), [ {”name”:”Ravi”, ”lastname”:”Kapoor”}, {”name”:”Rajiv”, ”lastname” : ”Khanna”}, {”name”:”Komal”,”lastname” : ”Bhandari”}, {”name”:”Abdul”,”lastname” : ”Sattar”}, {”name”:”Priya”,”lastname” : ”Rajhans”}, ]) Rows are added in addresses table with the help of the following code − from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String engine = create_engine(”sqlite:///college.db”, echo = True) meta = MetaData() conn = engine.connect() addresses = Table( ”addresses”, meta, Column(”id”, Integer, primary_key = True), Column(”st_id”, Integer), Column(”postal_add”, String), Column(”email_add”, String) ) conn.execute(addresses.insert(), [ {”st_id”:1, ”postal_add”:”Shivajinagar Pune”, ”email_add”:”[email protected]”}, {”st_id”:1, ”postal_add”:”ChurchGate Mumbai”, ”email_add”:”[email protected]”}, {”st_id”:3, ”postal_add”:”Jubilee Hills Hyderabad”, ”email_add”:”[email protected]”}, {”st_id”:5, ”postal_add”:”MG Road Bangaluru”, ”email_add”:”[email protected]”}, {”st_id”:2, ”postal_add”:”Cannought Place new Delhi”, ”email_add”:”[email protected]”}, ]) Note that the st_id column in addresses table refers to id column in students table. We can now use this relation to fetch data from both the tables. We want to fetch name and lastname from students table corresponding to st_id in the addresses table. from sqlalchemy.sql import select s = select([students, addresses]).where(students.c.id == addresses.c.st_id) result = conn.execute(s) for row in result: print (row) The select objects will effectively translate into following SQL expression joining two tables on common relation − SELECT students.id, students.name, students.lastname, addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add FROM students, addresses WHERE students.id = addresses.st_id This will produce output extracting corresponding data from both tables as follows − (1, ”Ravi”, ”Kapoor”, 1, 1, ”Shivajinagar Pune”, ”[email protected]”) (1, ”Ravi”, ”Kapoor”, 2, 1, ”ChurchGate Mumbai”, ”[email protected]”) (3, ”Komal”, ”Bhandari”, 3, 3, ”Jubilee Hills Hyderabad”, ”[email protected]”) (5, ”Priya”, ”Rajhans”, 4, 5, ”MG Road Bangaluru”, ”[email protected]”) (2, ”Rajiv”, ”Khanna”, 5, 2, ”Cannought Place new Delhi”, ”[email protected]”) Print Page Previous Next Advertisements ”;

Using DELETE Expression ”; Previous Next In the previous chapter, we have understood what an Update expression does. The next expression that we are going to learn is Delete. The delete operation can be achieved by running delete() method on target table object as given in the following statement − stmt = students.delete() In case of students table, the above line of code constructs a SQL expression as following − ”DELETE FROM students” However, this will delete all rows in students table. Usually DELETE query is associated with a logical expression specified by WHERE clause. The following statement shows where parameter − stmt = students.delete().where(students.c.id > 2) The resultant SQL expression will have a bound parameter which will be substituted at runtime when the statement is executed. ”DELETE FROM students WHERE students.id > :id_1” Following code example will delete those rows from students table having lastname as ‘Khanna’ − from sqlalchemy.sql.expression import update from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String engine = create_engine(”sqlite:///college.db”, echo = True) meta = MetaData() students = Table( ”students”, meta, Column(”id”, Integer, primary_key = True), Column(”name”, String), Column(”lastname”, String), ) conn = engine.connect() stmt = students.delete().where(students.c.lastname == ”Khanna”) conn.execute(stmt) s = students.select() conn.execute(s).fetchall() To verify the result, refresh the data view of students table in SQLiteStudio. Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Multiple Table Deletes ”; Previous Next In this chapter, we will look into the Multiple Table Deletes expression which is similar to using Multiple Table Updates function. More than one table can be referred in WHERE clause of DELETE statement in many DBMS dialects. For PG and MySQL, “DELETE USING” syntax is used; and for SQL Server, using “DELETE FROM” expression refers to more than one table. The SQLAlchemy delete() construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause as follows − stmt = users.delete(). where(users.c.id == addresses.c.id). where(addresses.c.email_address.startswith(”xyz%”)) conn.execute(stmt) On a PostgreSQL backend, the resulting SQL from the above statement would render as − DELETE FROM users USING addresses WHERE users.id = addresses.id AND (addresses.email_address LIKE %(email_address_1)s || ”%%”) If this method is used with a database that doesn’t support this behaviour, the compiler will raise NotImplementedError. Print Page Previous Next Advertisements ”;

SQLAlchemy Tutorial PDF Version Quick Guide Resources Job Search Discussion 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. Audience This tutorial is designed for all those Python programmers who would like to understand the ORM framework with SQLAlchemy and its API. Prerequisites Before you start proceeding with this tutorial, we assume you have a good understanding of the Python programming language. A basic understanding of relational databases, DB-API, and SQL is desired to understand this tutorial. Print Page Previous Next Advertisements ”;