Category: sqlalchemy

SQLAlchemy Core – Expression Language ”; Previous Next 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. Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Creating Table ”; Previous Next 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. from sqlalchemy import MetaData meta = MetaData() 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 − Name Name of the table Metadata MetaData object that will hold this table Column(s) One or more objects of column class 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 − BigInteger Boolean Date DateTime Float Integer Numeric SmallInteger String Text Time To create a students table in college database, use the following snippet − from sqlalchemy import Table, Column, Integer, String, MetaData meta = MetaData() students = Table( ”students”, meta, Column(”id”, Integer, primary_key = True), Column(”name”, String), Column(”lastname”, String), ) The create_all() function uses the engine object to create all the defined table objects and stores the information in metadata. meta.create_all(engine) Complete code is given below which will create a SQLite database college.db with a students table in it. 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), ) meta.create_all(engine) Because echo attribute of create_engine() function is set to True, the console will display the actual SQL query for table creation as follows − CREATE TABLE students ( id INTEGER NOT NULL, name VARCHAR, lastname VARCHAR, PRIMARY KEY (id) ) 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 − 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 ”;

Using Multiple Table Updates ”; Previous Next 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. stmt = students.update(). values({ students.c.name:”xyz”, addresses.c.email_add:”[email protected]” }). where(students.c.id == addresses.c.id) The update object is equivalent to the following UPDATE query − UPDATE students SET email_add = :addresses_email_add, name = :name FROM addresses WHERE students.id = addresses.id As far as MySQL dialect is concerned, multiple tables can be embedded into a single UPDATE statement separated by a comma as given below − stmt = students.update(). values(name = ”xyz”). where(students.c.id == addresses.c.id) The following code depicts the resulting UPDATE query − ”UPDATE students SET name = :name FROM addresses WHERE students.id = addresses.id” SQLite dialect however doesn’t support multiple-table criteria within UPDATE and shows following error − NotImplementedError: This backend does not support multiple-table criteria within UPDATE Print Page Previous Next Advertisements ”;

SQLAlchemy Core – SQL Expressions ”; Previous Next 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 − ins = students.insert() 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. ”INSERT INTO students (id, name, lastname) VALUES (: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 − >>> ins = users.insert().values(name = ”Karan”) >>> str(ins) ”INSERT INTO users (name) VALUES (:name)” 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. ins.compile().params {”name”: ”Karan”} Similarly, methods like update(), delete() and select() create UPDATE, DELETE and SELECT expressions respectively. We shall learn about them in later chapters. Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Executing Expression ”; Previous Next 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. conn = engine.connect() The following insert() object can be used for execute() method − ins = students.insert().values(name = ”Ravi”, lastname = ”Kapoor”) result = conn.execute(ins) The console shows the result of execution of SQL expression as below − INSERT INTO students (name, lastname) VALUES (?, ?) (”Ravi”, ”Kapoor”) COMMIT Following is the entire snippet that shows the execution of INSERT query using SQLAlchemy’s core technique − 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), ) ins = students.insert() ins = students.insert().values(name = ”Ravi”, lastname = ”Kapoor”) conn = engine.connect() result = conn.execute(ins) 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 ResultProxy object. 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 − result.inserted_primary_key [1] 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. conn.execute(students.insert(), [ {”name”:”Rajiv”, ”lastname” : ”Khanna”}, {”name”:”Komal”,”lastname” : ”Bhandari”}, {”name”:”Abdul”,”lastname” : ”Sattar”}, {”name”:”Priya”,”lastname” : ”Rajhans”}, ]) This is reflected in the data view of the table as shown in the following figure − Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Using Textual SQL ”; Previous Next 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 − from sqlalchemy import text t = text(“SELECT * FROM students”) result = connection.execute(t) The advantages text() provides over a plain string are − backend-neutral support for bind parameters per-statement execution options result-column typing behaviour 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 − from sqlalchemy.sql import text s = text(“select students.name, students.lastname from students where students.name between 😡 and :y”) conn.execute(s, x = ”A”, y = ”L”).fetchall() The text() function constructs SQL expression as follows − select students.name, students.lastname from students where students.name between ? and ? The values of x = ’A’ and y = ’L’ are passed as parameters. Result is a list of rows with names between ‘A’ and ‘L’ − [(”Komal”, ”Bhandari”), (”Abdul”, ”Sattar”)] The text() construct supports pre-established bound values using the TextClause.bindparams() method. The parameters can also be explicitly typed as follows − stmt = text(“SELECT * FROM students WHERE students.name BETWEEN 😡 AND :y”) stmt = stmt.bindparams( bindparam(“x”, type_= String), bindparam(“y”, type_= String) ) result = conn.execute(stmt, {“x”: “A”, “y”: “L”}) The text() function also be produces fragments of SQL within a select() object that accepts text() objects as an arguments. The “geometry” of the statement is provided by select() construct , and the textual content by text() construct. We can build a statement without the need to refer to any pre-established Table metadata. from sqlalchemy.sql import select s = select([text(“students.name, students.lastname from students”)]).where(text(“students.name between 😡 and :y”)) conn.execute(s, x = ”A”, y = ”L”).fetchall() You can also use and_() function to combine multiple conditions in WHERE clause created with the help of text() function. from sqlalchemy import and_ from sqlalchemy.sql import select s = select([text(“* from students”)]) .where( and_( text(“students.name between 😡 and :y”), text(“students.id>2″) ) ) conn.execute(s, x = ”A”, y = ”L”).fetchall() Above code fetches rows with names between “A” and “L” with id greater than 2. The output of the code is given below − [(3, ”Komal”, ”Bhandari”), (4, ”Abdul”, ”Sattar”)] Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Selecting Rows ”; Previous Next 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. s = students.select() The select object translates to SELECT query by str(s) function as shown below − ”SELECT students.id, students.name, students.lastname FROM students” We can use this select object as a parameter to execute() method of connection object as shown in the code below − result = conn.execute(s) When the above statement is executed, Python shell echoes following equivalent SQL expression − SELECT students.id, students.name, students.lastname FROM students The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method. row = result.fetchone() All selected rows in the table can be printed by a for loop as given below − for row in result: print (row) The complete code to print all rows from students table is shown 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), ) s = students.select() conn = engine.connect() result = conn.execute(s) for row in result: print (row) The output shown in Python shell is as follows − (1, ”Ravi”, ”Kapoor”) (2, ”Rajiv”, ”Khanna”) (3, ”Komal”, ”Bhandari”) (4, ”Abdul”, ”Sattar”) (5, ”Priya”, ”Rajhans”) The WHERE clause of SELECT query can be applied by using Select.where(). For example, if we want to display rows with id >2 s = students.select().where(students.c.id>2) result = conn.execute(s) for row in result: print (row) Here c attribute is an alias for column. Following output will be displayed on the shell − (3, ”Komal”, ”Bhandari”) (4, ”Abdul”, ”Sattar”) (5, ”Priya”, ”Rajhans”) 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. from sqlalchemy.sql import select s = select([users]) result = conn.execute(s) Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Connecting to Database ”; Previous Next 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 execute() Executes a SQL statement construct 3 begin() Returns a context manager delivering a Connection with a Transaction established. Upon successful operation, the Transaction is committed, else it is rolled back 4 dispose() Disposes of the connection pool used by the Engine 5 driver() Driver name of the Dialect in use by the Engine 6 table_names() Returns a list of all table names available in the database 7 transaction() Executes the given function within a transaction boundary Print Page Previous Next Advertisements ”;

SQLAlchemy Core – Using Aliases ”; Previous Next 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. from sqlalchemy.sql import alias st = students.alias(“a”) This alias can now be used in select() construct to refer to students table − s = select([st]).where(st.c.id>2) This translates to SQL expression as follows − SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2 We can now execute this SQL query with the execute() method of connection object. The complete code is as follows − from sqlalchemy.sql import alias, select st = students.alias(“a”) s = select([st]).where(st.c.id > 2) conn.execute(s).fetchall() When above line of code is executed, it generates the following output − [(3, ”Komal”, ”Bhandari”), (4, ”Abdul”, ”Sattar”), (5, ”Priya”, ”Rajhans”)] Print Page Previous Next Advertisements ”;