Py-linq-SQL Examples

This file explains how we use Py-linq-SQL and give many examples. The examples are executed on the "objects" table.

Table of content

• Py-linq-SQL Examples
  • Table of content
  • Initialize
    • Temporary database
    • From clauses
      • From table
      • From another requests
  • Consultation requests
    • Select
      • Select all
      • Select field
      • Select more fields
      • Select with operators and functions
      • Select with custom column names
    • Where
      • Classic where
      • Chain where
      • Where with operator AND and OR
      • Where with AND
      • Where with OR
      • Where with operators and functions
    • All, Any and Contains
      • All
      • Any
        • Any with predicate
        • Any without predicates
      • Contains
        • Contains with predicate
        • Contains with dict
    • Distinct
    • Ordering: Order by and Order by descending
      • Order by
      • Order by descending
    • Methods with one return: Count, Max, Min, Single
      • Count
      • Max
      • Min
      • Single
        • Simple single
        • Single with where
        • Single or default
    • Positional methods: First, Last, Element at
      • First
      • Last
      • Element at
    • Limit and Offset: Take, Skip, Take last, Skip last
      • Take
      • Take Last
      • Skip
      • Skip Last
    • Crossing methods
      • Except_, Union, Intersect
        • Except_
        • Union
        • Intersect
      • Join, Group by, Group join
        • Join
        • Group by
        • Group join
  • Alteration requests
    • Insert
      • Simple insert
      • Insert a single line
      • Insert multiple line
    • Update
    • Delete
      • Delete without where clauses
      • Delete with where clauses
      • Delete all
  • Pretty Print

Initialize

Temporary database

You can use tmp-connection-psql for test the package. tmp-connection-psql will create a temporary database, and you can give a SQL file to fill the database.

>>> from tmp_connection_psql import tmp_connection
>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    pass # Replace with your requests

You can also use a 'classic' connection with the function connect

From clauses

From table

The FROM clause is implicit, it is included in the declaration of the SQLEnumerable. SQLEnumerable(connection, table).

See the documentation of SQLEnumerable.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> sqle = SQLEnumerable(con, "objects")

From another requests

You can make nested queries with py-linq-sql. To do this replaces the table with a variable containing another SQLEnumerable not yet executed.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...     sqle_1 = SQLEnumerable(con, "objects")
...     sqle_2 = SQLEnumerable(con, sqle_1)

Consultation requests

Select

Select all

With py-lin-sql we can make some selections with the keyword select.

See the documentation of SQLEnumerable.select.

You can select all fields in a table if you let empty the argument of select.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...     sqle = SQLEnumerable(con, "objects").select().execute()

>>> for element in sqle:
...    print(element)
Row(id=1, data={'obj': {'mass': 12, 'name': 'earth'}})
Row(id=2, data={'obj': {'mass': 91, 'name': 'saturn'}})
Row(id=3, data={'obj': {'mass': 105, 'name': 'jupiter'}})
Row(id=4, data={'obj': {'mass': 12, 'name': 'beta pic ter'}})
Row(id=5, data={'obj': {'mass': 26, 'name': 'beta pic bis'}})
Row(id=6, data={'obj': {'mass': 3, 'name': 'toto'}})
Row(id=7, data={'obj': {'mass': 5, 'name': 'beta pic'}})

Select field

You can also make a selection on a field with a lambda function. With attribute access or dict access.

lambda x: x.data.obj.name
# Is the same as
lambda x: x["data"]["obj"]["name"]

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='earth')
Row(data_obj_name='saturn')
Row(data_obj_name='jupiter')
Row(data_obj_name='beta pic ter')
Row(data_obj_name='beta pic bis')
Row(data_obj_name='toto')
Row(data_obj_name='beta pic')

Select more fields

If you want select more tan 1 field, just give a tuple to the lambda.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: (x.data.obj.name, x.data.obj.mass))
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='earth', data_obj_mass=12)
Row(data_obj_name='saturn', data_obj_mass=91)
Row(data_obj_name='jupiter', data_obj_mass=105)
Row(data_obj_name='beta pic ter', data_obj_mass=12)
Row(data_obj_name='beta pic bis', data_obj_mass=26)
Row(data_obj_name='toto', data_obj_mass=3)
Row(data_obj_name='beta pic', data_obj_mass=5)

Select with operators and functions

You can also make a select with some operators and functions. Here you can find the list of all operators and functions.

>>> from py_linq_sql import SQLEnumerable, sqrt
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: (x.data.obj.name, sqrt(x.data.obj.mass * 3 + 1)))
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='earth', sqrt_data_obj_mass_mul_3_add_1=6.082762530298219)
Row(data_obj_name='saturn', sqrt_data_obj_mass_mul_3_add_1=16.55294535724685)
Row(data_obj_name='jupiter', sqrt_data_obj_mass_mul_3_add_1=17.776388834631177)
Row(data_obj_name='beta pic ter', sqrt_data_obj_mass_mul_3_add_1=6.082762530298219)
Row(data_obj_name='beta pic bis', sqrt_data_obj_mass_mul_3_add_1=8.888194417315589)
Row(data_obj_name='toto', sqrt_data_obj_mass_mul_3_add_1=3.1622776601683795)
Row(data_obj_name='beta pic', sqrt_data_obj_mass_mul_3_add_1=4.0)

Select with custom column names

By default, py-linq-sql generates column names based on the selection.

data.obj.mass + 1 gives: _data_obj_mass_add_1.

In SQL, it is very useful to be able to name your own columns. py-linq-sql also allows you to do it thanks to python dict.

It is highly recommended to do so.

To name your columns you must respect 3 constraints:

• the name contains only numbers, letters or '_'.
• the name must start with a letter.
• the max length is 58 bytes.

The string "name_of_planet" is a valid name The string "8_add_9" isn't a valid name.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='earth', mass=12)
Row(name='saturn', mass=91)
Row(name='jupiter', mass=105)
Row(name='beta pic ter', mass=12)
Row(name='beta pic bis', mass=26)
Row(name='toto', mass=3)
Row(name='beta pic', mass=5)

Where

One of the most useful things in SQL is being able to define predicates (conditions). For this we will use the keyword where.

Classic where

where define a predicate, a condition which is taken into account for a selection. To do this we use the lambda functions as for the select.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: x.data.obj.mass > 60)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='saturn', mass=91)
Row(name='jupiter', mass=105)

Chain where

We can chain where to give many predicates. When we make .where(predicate).where(predicate), elements of the table need to validate the two predicate. It's a AND.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: x.data.obj.mass < 60)
...        .where(lambda x: x.data.obj.name != "beta pic")
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='earth', mass=12)
Row(name='beta pic ter', mass=12)
Row(name='beta pic bis', mass=26)
Row(name='toto', mass=3)

Wa can make chain where in the same method calling with a tuple of lambda. This examples will return the same result of the previous one.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: (x.data.obj.mass < 60, x.data.obj.name != "beta pic"))
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='earth', mass=12)
Row(name='beta pic ter', mass=12)
Row(name='beta pic bis', mass=26)
Row(name='toto', mass=3)

Where with operator AND and OR

We can explicitly specify the AND operator and OR operator in where with python operators & and |.

Where with AND

To make an explicit and need to write the lambda like:

lambda x: (x.lambda.mass < 60) & (x.data.obj.name != "beta pic)".

You need to put parentheses around the other operators because in python the operator & has a higher priority than the others.

The next example will return the same result of chain where.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: (x.data.obj.mass < 60) & (x.data.obj.name != "beta pic"))
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='earth', mass=12)
Row(name='beta pic ter', mass=12)
Row(name='beta pic bis', mass=26)
Row(name='toto', mass=3)

Where with OR

In py-linq-sql, the operator OR is never implicit. To make an explicit or need to write the lambda like:

lambda x: (x.lambda.mass < 60) | (x.data.obj.name != "beta pic)".

You need to put parentheses around the other operators because in python the operator | has a higher priority than the others.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: (x.data.obj.mass < 60) | (x.data.obj.mass >= 100))
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='earth', mass=12)
Row(name='jupiter', mass=105)
Row(name='beta pic ter', mass=12)
Row(name='beta pic bis', mass=26)
Row(name='toto', mass=3)
Row(name='beta pic', mass=5)

Where with operators and functions

In where methods you can use some operators and functions. Here you can find the list of all operators and functions.

>>> from py_linq_sql import SQLEnumerable, sqrt, factorial
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x:
...             (sqrt(x.data.obj.mass) > 2)
...             &
...             (factorial(x.data.obj.mass) >= 100)
...         )
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='earth', mass=12)
Row(name='saturn', mass=91)
Row(name='jupiter', mass=105)
Row(name='beta pic ter', mass=12)
Row(name='beta pic bis', mass=26)
Row(name='beta pic', mass=5)

>>> from py_linq_sql import SQLEnumerable, is_in
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: is_in(x.data.obj.name, ["earth", "saturn"]))
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='earth', mass=12)
Row(name='saturn', mass=91)

All, Any and Contains

When they are executed those 3 methods return a boolean. They are useful to quickly known if your data respects some conditions.

All

all allows you to know if all your data respects a predicate, it will return True if it is the case and False otherwise. You can verify more than une predicate with a tuple in the lambda like the selection.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .all(lambda x: x.data.obj.mass > 0)
...        .execute()
...    )

print(sqle)
True

Any

Any with predicate

any work like all but just verify if at least one element validate the predicate.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .any(lambda x: x.data.obj.mass == 0)
...        .execute()
...    )

print(sqle)
False

Any without predicates

You can use any without predicate and this just checks if the table contains data.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .any()
...        .execute()
...    )

print(sqle)
True

Contains

contains is used to know if your table contains an element which validates a predicate (like any) or which is equal to python dict.

Contains with predicate

Contains with predicate is exactly like any with predicate

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .contains(lambda x: x.data.obj.mass == 0)
...        .execute()
...    )

print(sqle)
False

Contains with dict

You can give a dict to contains which represents an entire row to find out if it is already in your table.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .contains({"id": 2, "data": {"obj": {"name": "saturn", "mass": 91}}})
...        .execute()
...    )

print(sqle)
True

Distinct

distinct is very explicit, it is use so that there are no duplicates in the result.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .distinct()
...        .execute()
...    )

print(sqle)
Row(name='earth', mass=12)
Row(name='saturn', mass=91)
Row(name='jupiter', mass=105)
Row(name='beta pic ter', mass=12)
Row(name='beta pic bis', mass=26)
Row(name='toto', mass=3)
Row(name='beta pic', mass=5)

Ordering: Order by and Order by descending

order_by and order_by_descending are useful to sort the results by specific keys.

Order by

You can order the results by one key or multiple keys.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .order_by(lambda x: x.data.obj.mass)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='toto')
Row(data_obj_name='beta pic')
Row(data_obj_name='beta pic ter')
Row(data_obj_name='earth')
Row(data_obj_name='beta pic bis')
Row(data_obj_name='saturn')
Row(data_obj_name='jupiter')

Order by descending

You can also sort by descending (by the end).

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .order_by_descending(lambda x: x.data.obj.mass)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='jupiter')
Row(data_obj_name='saturn')
Row(data_obj_name='beta pic bis')
Row(data_obj_name='beta pic ter')
Row(data_obj_name='earth')
Row(data_obj_name='beta pic')
Row(data_obj_name='toto')

Methods with one return: Count, Max, Min, Single

Count

count is used to count the number of element in table. (No way :open_mouth:). You can use a where with your count for more precise results.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select()
...        .where(lambda x: x.data.mass < 100)
...        .count()
...        .execute()
...    )

print(sqle)
6

If you just wat the number of rows in the table you can make count without where.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select()
...        .count()
...        .execute()
...    )

print(sqle)
7

Max

max is very useful to know what is the maximum value from a field. It is used with a predicate.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select()
...        .max(lambda x :x.data.obj.name)
...        .execute()
...    )

print(sqle)
Row(max='toto')

By default, the predicate field is cast in str, you can cast it in other type with the second option argument.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select()
...        .max(lambda x :x.data.obj.mass, int)
...        .execute()
...    )

print(sqle)
Row(max=105)

Min

min work exactly like max but give the minimum instead the maximum. min is very useful to know what is the minimum value from a field. It is used with a predicate.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .min(lambda x :x.data.obj.name)
...        .execute()
...    )

print(sqle)
Row(min='beta pic')

By default, the predicate field is cast in str, you can cast it in other type with the second option argument.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .min(lambda x :x.data.obj.mass, int)
...        .execute()
...    )

print(sqle)
Row(min=3)

Single

Simple single

single lets you known if a single table element validates a predicate. If only one element validates the predicate the selection (from the select keyword) will be returned. If no element validates the predicate or more than one, single raise an error. You can use single without predicate to known if the table contains only one element.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .single(lambda x: x.data.obj.mass == 5)
...        .execute()
...    )

print(sqle)
Row(data_obj_name='beta pic')

Single with where

You can also let single empty and put a where to give the predicate.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .where(lambda x: x.data.obj.mass == 5)
...        .single()
...        .execute()
...    )

print(sqle)
Row(data_obj_name='beta pic')

Single or default

You can use single_or_default if you don't want py-linq-sql raise an error. If single_or_default should raise an error, it would return None when executed.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .single_or_default(lambda x: x.data.obj.mass > 0)
...        .execute()
...    )

print(sqle)
None

Positional methods: First, Last, Element at

First

first give the first element of the table or the first element who validates the predicate. If the table is empty or no element validates the predicate, py-linq-sql will raise an error. If you don't want this error use first_or_default. You can also give the predicate with a where (like single).

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .first(lambda x: x.data.obj.mass < 50)
...        .execute()
...    )

print(sqle)
Row(data_obj_name='earth')

Last

last give the last element of the table or the last element who validates the predicate. If the table is empty or no element validates the predicate, py-linq-sql will raise an error. If you don't want this error use last_or_default. You can also give the predicate with a where (like single).

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .last(lambda x: x.data.obj.mass < 50)
...        .execute()
...    )

print(sqle)
Row(data_obj_name='beta pic')

Element at

element_at give the element at the index n base on the 0 index. first() == element_at(0) You can use predicate with where. If n is greater than the size of the table, py-linq-sql raise an IndexError. If you don't want this error use element_at_or_default.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .element_at(2)
...        .execute()
...    )

print(sqle)
Row(data_obj_name='jupiter')

Limit and Offset: Take, Skip, Take last, Skip last

All those commands define the number of rows we take, and we skip. You can chain all this command, but you can't make max or min with those commands.

Take

take define the number of elements to take from the top of the table. If you put a number bigger the size of the table, take will return all the table.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .take(3)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='earth')
Row(data_obj_name='saturn')
Row(data_obj_name='jupiter')

Take Last

take_last define the number of elements to take from the end of the table. If you put a number bigger the size of the table, take_last will return all the table.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .take_last(3)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='beta pic bis')
Row(data_obj_name='toto')
Row(data_obj_name='beta pic')

Skip

skip define the number of elements to skip from the end of the table. If you put a number bigger the size of the table, skip will return None.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .skip(3)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='beta pic ter')
Row(data_obj_name='beta pic bis')
Row(data_obj_name='toto')
Row(data_obj_name='beta pic')

Skip Last

skip_last define the number of elements to skip from the end of the table. If you put a number bigger the size of the table, skip_last will return None.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: x.data.obj.name)
...        .skip_last(3)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='earth')
Row(data_obj_name='saturn')
Row(data_obj_name='jupiter')
Row(data_obj_name='beta pic ter')

Crossing methods

Except_, Union, Intersect

Those 3 methods depend on another SQLEnumerable.

Except_

except_ exclude a previous selection for this one.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle_exclude = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: x.data.obj.name == "earth")
...    )
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .except_(sqle_exclude)
...        .execute()
...    )

>>> for element in sqle:
...     print(element)
Row(name='beta pic ter', mass=12)
Row(name='toto', mass=3)
Row(name='beta pic bis', mass=26)
Row(name='jupiter', mass=105)
Row(name='beta pic', mass=5)
Row(name='saturn', mass=91)

Union

union make the sum of 2 selections (like a OR).

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    other_sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: x.data.obj.mass > 50)
...    )
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: x.data.obj.mass < 50)
...        .union(other_sqle)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='beta pic ter', mass=12)
Row(name='jupiter', mass=105)
Row(name='saturn', mass=91)
Row(name='toto', mass=3)
Row(name='beta pic bis', mass=26)
Row(name='beta pic', mass=5)
Row(name='earth', mass=12)

Intersect

intersect make the intersection between 2 selections (like a AND).

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    other_sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: x.data.obj.mass < 10)
...    )
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
...        .where(lambda x: x.data.obj.mass >= 5)
...        .intersect(other_sqle)
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(name='beta pic', mass=5)

Join, Group by, Group join

Join

A join clause in SQL – corresponding to a join operation in relational algebra – combines columns from two tables according to a matching criterion. The result is a SQLEnumerable containing the resulting data. Once executed this gives us an Enumerable.

You can find many explanations in the web. A good site, only in French unfortunately: SQL.sh

For this example we have another table: 'satellite' which contains a single column. {"obj": {"name": "titi", "owner_id": 6}}

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle_sat = SQLEnumerable(con, "satellite")
...    sqle_objects = (
...        SQLEnumerable(con, "objects")
...        .join(
...            sqle_sat,
...            lambda objects: objects.id,
...            lambda satellite: satellite.data.obj.owner_id,
...            # For the moment you need to put inner before outer in the lambda:
...            # here inner is satellite
...            lambda satellite, objects: (
...                satellite.data.obj.name,
...                objects.data.obj.name,
...            ),
...        )
...        .execute()
...    )

>>> for element in sqle_objects:
...    print(element)
Row(satellite_data_obj_name='titi', objects_data_obj_name='toto')

By default, join make an INNER JOIN, but we can make all other join. See the documentation of join.

Group by

A group by statement in SQL specifies that a SQL select statement partitions result rows into groups, based on their values in one or several columns. Typically, grouping is used to apply some sort of aggregate function for each group.

You can find many s in the web. A good site, only in French unfortunately: SQL.sh

You can find all aggregate function in the README

>>> from py_linq_sql import SQLEnumerable, sum, avg
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .group_by(
...            lambda x: x.data.obj.name,
...            lambda x: (sum(x.data.obj.mass), avg(x.data.obj.mass)),
...        )
...        .execute()
...    )

>>> for element in sqle:
...    print(element)
Row(data_obj_name='beta pic', sum=Decimal('5'), avg=Decimal('5.0000000000000000'))
Row(data_obj_name='toto', sum=Decimal('3'), avg=Decimal('3.0000000000000000'))
Row(data_obj_name='beta pic bis', sum=Decimal('26'), avg=Decimal('26.0000000000000000'))
Row(data_obj_name='earth', sum=Decimal('12'), avg=Decimal('12.0000000000000000'))
Row(data_obj_name='jupiter', sum=Decimal('105'), avg=Decimal('105.0000000000000000'))
Row(data_obj_name='saturn', sum=Decimal('91'), avg=Decimal('91.0000000000000000'))
Row(data_obj_name='beta pic ter', sum=Decimal('12'), avg=Decimal('12.0000000000000000'))

Group join

If you want to make a join and group the result don't use join and group_by. You must use group_join.

>>> from py_linq_sql import SQLEnumerable, count
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle_sat = SQLEnumerable(con, "satellite")
...    sqle_obj = (
...        SQLEnumerable(con, "objects")
...        .group_join(
...            sqle_sat,
...            lambda objects: objects.id,
...            lambda satellite: satellite.data.obj.owner_id,
...            # For the moment you need to put inner before outer in the lambda:
...            # here inner is satellite
...            lambda satellite, objects: (
...                satellite.data.obj.name,
...                objects.data.obj.name,
...                count(objects.data.obj.name, str),
...            ),
...        )
...        .execute()
...    )

>>> for element in sqle_obj:
...    print(element)
Row(satellite_data_obj_name='titi', objects_data_obj_name='toto', count=1)

Alteration requests

With py-linq-sql you can also make alteration of your database. For this we have 3 keywords.

Insert

To insert data in the database you need to precise the column and give a python dict with your data.

See the documentation of SQLEnumerable.insert.

To insert data in SQL table you need to follow those rules for the typing.

Containers

Python Type	Python Example	Meaning
list	(1, 2, 3)	Multiple lines
tuple	(1, 2, 3)	Single relational or mixed
dict	{"a": 1, "b": 2, "c": 3}	Single json line

Values

Python Type	Python Example	SQL Type
str	"hello world"	TEXT
int	12	NUMBER
float	12.05	NUMBER
Decimal	Decimal("12.05")	NUMBER
dict	{"a": 1, "b": 2, "c": 3}	JSON
list	[1, 2, 3]	ARRAY
tuple	(1, 2, 3)	ARRAY

Simple insert

You can make simple insertion Only in relational table with the method simple_insert().

To give data and column name we use the **kwargs. The column name is the key and data is the value.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "rel_objects")
...        .simple_insert(name="earth", mass=1)
...        .execute()
...        )

>>> print(sqle)
1

Insert a single line

You can insert only one line with insert

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .insert("data", {"obj": {"name": "earth", "mass": 12}})
...        .execute()
...        )

>>> print(sqle)
1

Insert multiple line

You can also insert more than one line.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...            .insert(
...                "data",
...                [
...                    {"obj": {"name": "jupiter", "mass": 1}},
...                    {"obj": {"name": "saturn", "mass": 91}},
...                ],
...            )
...            .execute()
...        )

>>> print(sqle)
2

Update

To update something in the database you need update method. You must be precise with a where what you want update. You can make multi where. If you want to update all data you can update without where.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .where(lambda x: x.data.obj.name == "beta pic ter")
...        .update(lambda x: x.data.obj.mass == 666)
...        .where(lambda x: x.data.obj.mass == 12)
...        .execute()
...    )

>>> print(sqle)
1

Delete

To delete from the database you can have precise what you want to delete in the delete method or with a where method.

See the documentation of SQLEnumerable.delete.

Delete without where clauses

You can have precise what you want to delete directly in the method. Be careful if your predicate is not precise enough, you risk deleting data unintentionally.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .delete(lambda x: x.data.obj.name == "toto")
...        .execute()
...    )

>>> print(sqle)
1

Delete with where clauses

You can also use where to precise what you want to delete.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .delete()
...        .where(lambda x: x.data.obj.name == "toto")
...        .where(lambda x: x.data.obj.mass != 12)
...        .execute()
...    )

>>> print(sqle)
1

Delete all

If you want to delete all the data in your table you can use the options armageddon. It's a kargw so you need to specify the name of the option when you call it. You can't make armageddon with where clauses or clauses in the delete method.

>>> from py_linq_sql import SQLEnumerable
>>> from tmp_connection_psql import tmp_connection

>>> with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
...    sqle = (
...        SQLEnumerable(con, "objects")
...        .delete(armageddon=True)
...        .execute()
...    )

>>> print(sqle)
7

Pretty Print

You can print the result directly with print but you can use pretty_print to display the result in a beautiful table.

from py_linq_sql import SQLEnumerable
from tmp_connection_psql import tmp_connection

with tmp_connection("dummypassword", "./create_db_for_main.sql") as con:
    sqle = (
        SQLEnumerable(con, "objects")
        .select(lambda x: {"name": x.data.obj.name, "mass": x.data.obj.mass})
        .execute()
    )

pretty_print(sqle)