Alchemy нфт tutorial
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.
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.
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.
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 −
It is also possible to install SQLAlchemy from below source code −
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 −
To check if SQLAlchemy is properly installed and to know its version, enter the following command in the Python prompt −
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.
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.
For a MySQL database, use the below command −
To specifically mention DB-API to be used for connection, the URL string takes the form as follows −
For example, if you are using PyMySQL driver with MySQL, use the following command −
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 −
connect()
Returns connection object
execute()
Executes a SQL statement construct
begin()
Returns a context manager delivering a Connection with a Transaction established. Upon successful operation, the Transaction is committed, else it is rolled back
dispose()
Disposes of the connection pool used by the Engine
driver()
Driver name of the Dialect in use by the Engine
table_names()
Returns a list of all table names available in the database
transaction()
Executes the given function within a transaction boundary
Let us now discuss how to use the create table function.
The SQL Expression Language constructs its expressions against table columns. SQLAlchemy Column object represents a column in a database table which is in turn represented by a Tableobject. Metadata contains definitions of tables and associated objects such as index, view, triggers, etc.
Hence an object of MetaData class from SQLAlchemy Metadata is a collection of Table objects and their associated schema constructs. It holds a collection of Table objects as well as an optional binding to an Engine or Connection.
Constructor of MetaData class can have bind and schema parameters which are by default None.
Next, we define our tables all within above metadata catalog, using the Table construct, which resembles regular SQL CREATE TABLE statement.
An object of Table class represents corresponding table in a database. The constructor takes the following parameters −
Column object represents a column in a database table. Constructor takes name, type and other parameters such as primary_key, autoincrement and other constraints.
SQLAlchemy matches Python data to the best possible generic column data types defined in it. Some of the generic data types are −
To create a students table in college database, use the following snippet −
The create_all() function uses the engine object to create all the defined table objects and stores the information in metadata.
Complete code is given below which will create a SQLite database college.db with a students table in it.
Because echo attribute of create_engine() function is set to True, the console will display the actual SQL query for table creation as follows −
The college.db will be created in current working directory. To check if the students table is created, you can open the database using any SQLite GUI tool such as SQLiteStudio.
The below image shows the students table that is created in the database −
In this chapter, we will briefly focus on the SQL Expressions and their functions.
SQL expressions are constructed using corresponding methods relative to target table object. For example, the INSERT statement is created by executing insert() method as follows −
The result of above method is an insert object that can be verified by using str() function. The below code inserts details like student id, name, lastname.
It is possible to insert value in a specific field by values() method to insert object. The code for the same is given below −
The SQL echoed on Python console doesn’t show the actual value (‘Karan’ in this case). Instead, SQLALchemy generates a bind parameter which is visible in compiled form of the statement.
Similarly, methods like update(), delete() and select() create UPDATE, DELETE and SELECT expressions respectively. We shall learn about them in later chapters.
In the previous chapter, we have learnt SQL Expressions. In this chapter, we shall look into the execution of these expressions.
In order to execute the resulting SQL expressions, we have to obtain a connection object representing an actively checked out DBAPI connection resource and then feed the expression object as shown in the code below.
The following insert() object can be used for execute() method −
The console shows the result of execution of SQL expression as below −
Following is the entire snippet that shows the execution of INSERT query using SQLAlchemy’s core technique −
The result can be verified by opening the database using SQLite Studio as shown in the below screenshot −
The result variable is known as a ResultProxyobject. It is analogous to the DBAPI cursor object. We can acquire information about the primary key values which were generated from our statement using ResultProxy.inserted_primary_key as shown below −
To issue many inserts using DBAPI’s execute many() method, we can send in a list of dictionaries each containing a distinct set of parameters to be inserted.
This is reflected in the data view of the table as shown in the following figure −
In this chapter, we will discuss about the concept of selecting rows in the table object.
The select() method of table object enables us to construct SELECT expression.
The select object translates to SELECT query by str(s) function as shown below −
We can use this select object as a parameter to execute() method of connection object as shown in the code below −
When the above statement is executed, Python shell echoes following equivalent SQL expression −
The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method.
All selected rows in the table can be printed by a for loop as given below −
The complete code to print all rows from students table is shown below −
The output shown in Python shell is as follows −
The WHERE clause of SELECT query can be applied by using Select.where(). For example, if we want to display rows with id >2
Here c attribute is an alias for column. Following output will be displayed on the shell −
Here, we have to note that select object can also be obtained by select() function in sqlalchemy.sql module. The select() function requires the table object as argument.
SQLAlchemy lets you just use strings, for those cases when the SQL is already known and there isn’t a strong need for the statement to support dynamic features. The text() construct is used to compose a textual statement that is passed to the database mostly unchanged.
It constructs a new TextClause, representing a textual SQL string directly as shown in the below code −
The advantages text() provides over a plain string are −
The text()function requires Bound parameters in the named colon format. They are consistent regardless of database backend. To send values in for the parameters, we pass them into the execute() method as additional arguments.
The following example uses bound parameters in textual SQL −
The text() function constructs SQL expression as follows −
The values of x = ’A’ and y = ’L’ are passed as parameters. Result is a list of rows with names between ‘A’ and ‘L’ −
The text() construct supports pre-established bound values using the TextClause.bindparams() method. The parameters can also be explicitly typed as follows −
You can also use and_() function to combine multiple conditions in WHERE clause created with the help of text() function.
Above code fetches rows with names between “A” and “L” with id greater than 2. The output of the code is given below −
The alias in SQL corresponds to a “renamed” version of a table or SELECT statement, which occurs anytime you say “SELECT * FROM table1 AS a”. The AS creates a new name for the table. Aliases allow any table or subquery to be referenced by a unique name.
In case of a table, this allows the same table to be named in the FROM clause multiple times. It provides a parent name for the columns represented by the statement, allowing them to be referenced relative to this name.
In SQLAlchemy, any Table, select() construct, or other selectable object can be turned into an alias using the From Clause.alias() method, which produces an Alias construct. The alias() function in sqlalchemy.sql module represents an alias, as typically applied to any table or sub-select within a SQL statement using the AS keyword.
This alias can now be used in select() construct to refer to students table −
This translates to SQL expression as follows −
We can now execute this SQL query with the execute() method of connection object. The complete code is as follows −
When above line of code is executed, it generates the following output −
The update() method on target table object constructs equivalent UPDATE SQL expression.
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 −
The stmt object is an update object that translates to −
The bound parameter lastname_1 will be substituted when execute() method is invoked. The complete update code is given below −
The above code displays following output with second row showing effect of update operation as in the screenshot given −
Note that similar functionality can also be achieved by using update() function in sqlalchemy.sql.expression module as shown below −
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 −
In case of students table, the above line of code constructs a SQL expression as following −
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 −
The resultant SQL expression will have a bound parameter which will be substituted at runtime when the statement is executed.
Following code example will delete those rows from students table having lastname as ‘Khanna’ −
To verify the result, refresh the data view of students table in SQLiteStudio.
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 −
Above code will translate to CREATE TABLE queries for students and addresses table as below −
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 −
Rows are added in addresses table with the help of the following code −
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.
The select objects will effectively translate into following SQL expression joining two tables on common relation −
This will produce output extracting corresponding data from both tables as follows −
In the previous chapter, we have discussed about how to use multiple tables. So we proceed a step further and learn multiple table updates in this chapter.
Using SQLAlchemy’s table object, more than one table can be specified in WHERE clause of update() method. The PostgreSQL and Microsoft SQL Server support UPDATE statements that refer to multiple tables. This implements “UPDATE FROM” syntax, which updates one table at a time. However, additional tables can be referenced in an additional “FROM” clause in the WHERE clause directly. The following lines of codes explain the concept of multiple table updates clearly.
The update object is equivalent to the following UPDATE query −
As far as MySQL dialect is concerned, multiple tables can be embedded into a single UPDATE statement separated by a comma as given below −
The following code depicts the resulting UPDATE query −
SQLite dialect however doesn’t support multiple-table criteria within UPDATE and shows following error −
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 −
will have a different result than −
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 −
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.
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 −
On a PostgreSQL backend, the resulting SQL from the above statement would render as −
If this method is used with a database that doesn’t support this behaviour, the compiler will raise NotImplementedError.
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.
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.
This is equivalent to following SQL expression −
You can explicitly mention joining criteria as follows −
If we now build the below select construct using this join as −
This will result in following SQL expression −
If this statement is executed using the connection representing engine, data belonging to selected columns will be displayed. The complete code is as follows −
The following is the output of the above code −
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 −
SQLAlchemy functions and_(), or_() and not_() respectively implement AND, OR and NOT operators.
It produces a conjunction of expressions joined by AND. An example is given below for better understanding −
This translates to −
To use and_() in a select() construct on a students table, use the following line of code −
SELECT statement of the following nature will be constructed −
The complete code that displays output of the above SELECT query is as follows −
Following row will be selected assuming that students table is populated with data used in previous example −
It produces conjunction of expressions joined by OR. We shall replace the stmt object in the above example with the following one using or_()
Which will be effectively equivalent to following SELECT query −
Once you make the substitution and run the above code, the result will be two rows falling in the OR condition −
It produces an ascending ORDER BY clause. The function takes the column to apply the function as a parameter.
The statement implements following SQL expression −
Following code lists out all records in students table in ascending order of name column −
Above code produces following output −
Similarly desc() function produces descending ORDER BY clause as follows −
The equivalent SQL expression is −
And the output for the above lines of code is −
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 −
The resulting SQL expression resembles −
and the result is as follows −
Some of the important functions used in SQLAlchemy are discussed in this chapter.
Standard SQL has recommended many functions which are implemented by most dialects. They return a single value based on the arguments passed to it. Some SQL functions take columns as arguments whereas some are generic. Thefunc keyword in SQLAlchemy API is used to generate these functions.
In SQL, now() is a generic function. Following statements renders the now() function using func −
Sample result of above code may be as shown below −
On the other hand, count() function which returns number of rows selected from a table, is rendered by following usage of func −
From the above code, count of number of rows in students table will be fetched.
Some built-in SQL functions are demonstrated using Employee table with following data −
The max() function is implemented by following usage of func from SQLAlchemy which will result in 85, the total maximum marks obtained −
Similarly, min() function that will return 56, minimum marks, will be rendered by following code −
So, the AVG() function can also be implemented by using the below code −
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 −
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 −
The union construct translates to following SQL expression −
From our addresses table, following rows represent the union operation −
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.
The corresponding SQL expression is as follows −
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.
Result of the above code is the following SQL expression −
Assuming that addresses table contains data used in earlier examples, it will display following output −
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.
In effect, this is equivalent to following SQL statement −
The two bound parameters ‘%gmail.com’ and ‘%Pune’ generate a single row from original data in addresses table as shown below −
The main objective of the Object Relational Mapper API of SQLAlchemy is to facilitate associating user-defined Python classes with database tables, and objects of those classes with rows in their corresponding tables. Changes in states of objects and rows are synchronously matched with each other. SQLAlchemy enables expressing database queries in terms of user defined classes and their defined relationships.
The ORM is constructed on top of the SQL Expression Language. It is a high level and abstracted pattern of usage. In fact, ORM is an applied usage of the Expression Language.
Although a successful application may be constructed using the Object Relational Mapper exclusively, sometimes an application constructed with the ORM may use the Expression Language directly where specific database interactions are required.
First of all, create_engine() function is called to set up an engine object which is subsequently used to perform SQL operations. The function has two arguments, one is the name of database and other is an echo parameter when set to True will generate the activity log. If it doesn’t exist, the database will be created. In the following example, a SQLite database is created.
The Engine establishes a real DBAPI connection to the database when a method like Engine.execute() or Engine.connect() is called. It is then used to emit the SQLORM which does not use the Engine directly; instead, it is used behind the scenes by the ORM.
In case of ORM, the configurational process starts by describing the database tables and then by defining classes which will be mapped to those tables. In SQLAlchemy, these two tasks are performed together. This is done by using Declarative system; the classes created include directives to describe the actual database table they are mapped to.
A base class stores a catlog of classes and mapped tables in the Declarative system. This is called as the declarative base class. There will be usually just one instance of this base in a commonly imported module. The declarative_base() function is used to create base class. This function is defined in sqlalchemy.ext.declarative module.
Once base classis declared, any number of mapped classes can be defined in terms of it. Following code defines a Customer’s class. It contains the table to be mapped to, and names and datatypes of columns in it.
A class in Declarative must have a __tablename__ attribute, and at least one Column which is part of a primary key. Declarative replaces all the Column objects with special Python accessors known as descriptors. This process is known as instrumentation which provides the means to refer to the table in a SQL context and enables persisting and loading the values of columns from the database.
This mapped class like a normal Python class has attributes and methods as per the requirement.
The information about class in Declarative system, is called as table metadata. SQLAlchemy uses Table object to represent this information for a specific table created by Declarative. The Table object is created according to the specifications, and is associated with the class by constructing a Mapper object. This mapper object is not directly used but is used internally as interface between mapped class and table.
Each Table object is a member of larger collection known as MetaData and this object is available using the .metadata attribute of declarative base class. The MetaData.create_all() method is, passing in our Engine as a source of database connectivity. For all tables that haven’t been created yet, it issues CREATE TABLE statements to the database.
The complete script to create a database and a table, and to map Python class is given below −
When executed, Python console will echo following SQL expression being executed −
If we open the Sales.db using SQLiteStudio graphic tool, it shows customers table inside it with above mentioned structure.
In order to interact with the database, we need to obtain its handle. A session object is the handle to database. Session class is defined using sessionmaker() – a configurable session factory method which is bound to the engine object created earlier.
The session object is then set up using its default constructor as follows −
Some of the frequently required methods of session class are listed below −
begin()
begins a transaction on this session
add()
places an object in the session. Its state is persisted in the database on next flush operation
add_all()
adds a collection of objects to the session
commit()
flushes all items and any transaction in progress
delete()
marks a transaction as deleted
execute()
executes a SQL expression
expire()
marks attributes of an instance as out of date
flush()
flushes all object changes to the database
invalidate()
closes the session using connection invalidation
rollback()
rolls back the current transaction in progress
close()
Closes current session by clearing all items and ending any transaction in progress
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.
Note that this transaction is pending until the same is flushed using commit() method.
Following is the complete script to add a record in customers table −
To add multiple records, we can use add_all() method of the session class.
Table view of SQLiteStudio shows that the records are persistently added in customers table. The following image shows the result −
All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.
Query objects are initially generated using the query() method of the Session as follows −
Following statement is also equivalent to the above given statement −
The query object has all() method which returns a resultset in the form of list of objects. If we execute it on our customers table −
This statement is effectively equivalent to following SQL expression −
The result object can be traversed using For loop as below to obtain all records in underlying customers table. Here is the complete code to display all records in Customers table −
Python console shows list of records as below −
The Query object also has following useful methods −
add_columns()
It adds one or more column expressions to the list of result columns to be returned.
add_entity()
It adds a mapped entity to the list of result columns to be returned.
count()
It returns a count of rows this Query would return.
delete()
It performs a bulk delete query. Deletes rows matched by this query from the database.
distinct()
It applies a DISTINCT clause to the query and return the newly resulting Query.
filter()
It applies the given filtering criterion to a copy of this Query, using SQL expressions.
first()
It returns the first result of this Query or None if the result doesn’t contain any row.
get()
It returns an instance based on the given primary key identifier providing direct access to the identity map of the owning Session.
group_by()
It applies one or more GROUP BY criterion to the query and return the newly resulting Query
join()
It creates a SQL JOIN against this Query object’s criterion and apply generatively, returning the newly resulting Query.
one()
It returns exactly one result or raise an exception.
order_by()
It applies one or more ORDER BY criterion to the query and returns the newly resulting Query.
update()
It performs a bulk update query and updates rows matched by this query in the database.
In this chapter, we will see how to modify or update the table with desired values.
To modify data of a certain attribute of any object, we have to assign new value to it and commit the changes to make the change persistent.
Let us fetch an object from the table whose primary key identifier, in our Customers table with ID=2. We can use get() method of session as follows −
We can display contents of the selected object with the below given code −
From our customers table, following output should be displayed −
Now we need to update the Address field by assigning new value as given below −
The change will be persistently reflected in the database. Now we fetch object corresponding to first row in the table by using first() method as follows −
This will execute following SQL expression −
The bound parameters will be LIMIT = 1 and OFFSET = 0 respectively which means first row will be selected.
Now, the output for the above code displaying the first row is as follows −
Now change name attribute and display the contents using the below code −
The output of the above code is −
Even though the change is displayed, it is not committed. You can retain the earlier persistent position by using rollback() method with the code below.
Original contents of first record will be displayed.
For bulk updates, we shall use update() method of the Query object. Let us try and give a prefix, ‘Mr.’ to name in each row (except ID = 2). The corresponding update() statement is as follows −
The update() method requires two parameters as follows −
A dictionary of key-values with key being the attribute to be updated, and value being the new contents of attribute.
synchronize_session attribute mentioning the strategy to update attributes in the session. Valid values are false: for not synchronizing the session, fetch: performs a select query before the update to find objects that are matched by the update query; and evaluate: evaluate criteria on objects in the session.
Three out of 4 rows in the table will have name prefixed with ‘Mr.’ However, the changes are not committed and hence will not be reflected in the table view of SQLiteStudio. It will be refreshed only when we commit the session.
In this chapter, we will discuss how to apply filter and also certain filter operations along with their codes.
Resultset represented by Query object can be subjected to certain criteria by using filter() method. The general usage of filter method is as follows −
In the following example, resultset obtained by SELECT query on Customers table is filtered by a condition, (ID>2) −
This statement will translate into following SQL expression −
Since the bound parameter (?) is given as 2, only those rows with ID column>2 will be displayed. The complete code is given below −
The output displayed in the Python console is as follows −
Now, we will learn the filter operations with their respective codes and output.
The usual operator used is == and it applies the criteria to check equality.
SQLAlchemy will send following SQL expression −
The output for the above code is as follows −
The operator used for not equals is != and it provides not equals criteria.
The resulting SQL expression is −
The output for the above lines of code is as follows −
like() method itself produces the LIKE criteria for WHERE clause in the SELECT expression.
Above SQLAlchemy code is equivalent to following SQL expression −
And the output for the above code is −
This operator checks whether the column value belongs to a collection of items in a list. It is provided by in_() method.
Here, the SQL expression evaluated by SQLite engine will be as follows −
The output for the above code is as follows −
This conjunction is generated by either putting multiple commas separated criteria in the filter or using and_() method as given below −
Both the above approaches result in similar SQL expression −
The output for the above lines of code is −
This conjunction is implemented by or_() method.
As a result, SQLite engine gets following equivalent SQL expression −
The output for the above code is as follows −
There are a number of methods of Query object that immediately issue SQL and return a value containing loaded database results.
Here’s a brief rundown of returning list and scalars −
It returns a list. Given below is the line of code for all() function.
Python console displays following SQL expression emitted −
It applies a limit of one and returns the first result as a scalar.
The bound parameters for LIMIT is 1 and for OFFSET is 0.
This command fully fetches all rows, and if there is not exactly one object identity or composite row present in the result, it raises an error.
With multiple rows found −
With no rows found −
The one() method is useful for systems that expect to handle “no items found” versus “multiple items found” differently.
It invokes the one() method, and upon success returns the first column of the row as follows −
This generates following SQL statement −
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
The raw SQL expression generated shows conversion of filter to WHERE clause with the code illustrated below −
From our sample data in Customers table, two rows will be selected and name column will be printed as follows −
To specify bind parameters with string-based SQL, use a colon,and to specify the values, use the params() method.
The effective SQL displayed on Python console will be as given below −
To use an entirely string-based statement, a text() construct representing a complete statement can be passed to from_statement().
The result of above code will be a basic SELECT statement as given below −
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.
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 −
This session describes creation of another table which is related to already existing one in our database. The customers table contains master data of customers. We now need to create invoices table which may have any number of invoices belonging to a customer. This is a case of one to many relationships.
Using declarative, we define this table along with its mapped class, Invoices as given below −
This will send a CREATE TABLE query to SQLite engine as below −
We can check that new table is created in sales.db with the help of SQLiteStudio tool.
Invoices class applies ForeignKey construct on custid attribute. This directive indicates that values in this column should be constrained to be values present in id column in customers table. This is a core feature of relational databases, and is the “glue” that transforms unconnected collection of tables to have rich overlapping relationships.
A second directive, known as relationship(), tells the ORM that the Invoice class should be linked to the Customer class using the attribute Invoice.customer.
