Short Notes on Python - Revision history

Admin: /* Subqueries */

2021-09-15T16:09:56Z

‎Subqueries

2021-05-17T15:14:50Z

‎iPython fails with NameError: name 'sys' is not defined on autocompletion

2021-05-17T15:14:18Z

2020-12-04T13:01:41Z

‎SQLAlchemy

2020-12-03T11:55:16Z

2020-12-03T01:53:06Z

2020-08-07T01:24:39Z

‎Some Basics

2020-08-06T21:39:02Z

‎SQLAlchemy

2020-07-30T11:52:25Z

‎SQLAlchemy

2020-07-14T21:06:34Z

‎SQLAlchemy

@@ Line 365: / Line 365: @@
 res = session.query(sub.c.c1, sub.c.c2).\
      all()
 </pre>

@@ Line 368: / Line 368: @@
 </pre>
-== iPython fails with <tt>NameError: name 'sys' is not defined</tt> on autocompletion ==
+== iPython fails with ''NameError: name 'sys' is not defined'' on autocompletion ==
 This is due to new version of <tt>jedi</tt> module not being compatible with iPython.

@@ Line 366: / Line 366: @@
 res = session.query(sub.c.c1, sub.c.c2).\
      all()
 </pre>

@@ Line 260: / Line 260: @@
 == SQLAlchemy ==
-=== Some Basics ===
+=== Select, Where, Group ===
-==== Select, Where, Group ====
+=== Updating ===
-−
-==== Updating ====
 <pre>
@@ Line 275: / Line 273: @@
 </pre>
-==== Scalar Values ====
+=== Scalar Values ===
 <pre>
 max_value = session.query(sqlalchemy.func.max(Table.column)).scalar()
@@ Line 283: / Line 285: @@
 session.query(sqlalchemy.func.min(Table.column), sqlalchemy.func.max(Table.column)).first()
 </pre>
 === Make SQLAlchemy Read-Only ===
@@ Line 341: / Line 348: @@
          cols = ['{} ({})'.format(col['name'], col['type']) for col in inspector.get_columns(table_name, schema=schema)]
          print("Table: {} ({})".format(table_name, ', '.join(cols)))</pre>
 === Subqueries ===

@@ Line 11: / Line 11: @@
 datetime.datetime.combine(d, t) -> datetime.datetime
 in particular: datetime.datetime.combine(d, datetime.time(0, 0, 0)) -> start of date `d` as datetime</pre>
 === Memory ===

@@ Line 1: / Line 1: @@
-== Timing, and memory, on Linux ==
+== Miscellaneous ==
-=== Timing ===
+=== Date, Time, Datetime ===
-On Linux, it's safer to use <tt>time.time()</tt>
+<pre>d: datetime.date = ...
-<pre>import time
+t: datetime.time = ...
-t = time.time()
+dt: datetime.datetime = ...
-# do some stuff
-print "stuff took %1.3f", time.time() - t, "seconds"</pre>
+dt.date() -> datetime.date
-On Windows, AFAIK, it's safer to use <tt>time.clock()</tt>
+dt.time() -> datetime.time
 === Memory ===

@@ Line 232: / Line 232: @@
 === Some Basics ===
 ==== Updating ====
 <pre>
-session.query(model).\
+session.query(Table).\
-     filter(...).\
+     filter(Table.id.in_(...)).\
-     update({model.col1: 10, model.col2: model.col2 + 50})
+     update({
 </pre>

← Older revision		Revision as of 21:39, 6 August 2020
Line 230:		Line 230:

	== SQLAlchemy ==		== SQLAlchemy ==
		+
		+	=== Some Basics ===
		+
		+	==== Updating ====
		+
		+	<pre>
		+	session.query(model).\
		+	filter(...).\
		+	update({model.col1: 10, model.col2: model.col2 + 50})
		+	</pre>

	=== Make SQLAlchemy Read-Only ===		=== Make SQLAlchemy Read-Only ===

@@ Line 264: / Line 264: @@
          # use session object</pre>
-=== Show Create Statement for Your Model ===
+=== Show Create Statement for Your Model, including Indexes ===
-<pre>from sqlalchemy.schema import CreateTable
+<pre>from sqlalchemy.schema import CreateTable, CreateIndex
-with db_session():
+def print_create_statement(model):
-     print(CreateTable(MyModel.__table__))</pre>
+     print(CreateTable(model.__table__).compile(dialect=postgresql.dialect()))
+    for index in model.__table__.indexes:
-or, without actually instantiating an engine:
+        print(CreateIndex(index).compile(dialect=postgresql.dialect()))</pre>
-−
-<pre>from sqlalchemy.dialects import postgresql
-−
-print(CreateTable(MyModel.__table__).compile(dialect=postgresql.dialect()))</pre>
 === List Tables and Their Columns ===

← Older revision		Revision as of 21:06, 14 July 2020
Line 295:		Line 295:

	<pre>session.query(MyModel).with_entities(func.count()).scalar()</pre>		<pre>session.query(MyModel).with_entities(func.count()).scalar()</pre>
		+
		+	=== Subqueries ===
		+
		+	Creating subqueries is pretty straightforward in SQLAlchemy.
		+
		+	To emit
		+	<pre>
		+	SELECT sub.c1, sub.c2
		+	FROM (SELECT x as c1, y as c2 FROM table) AS sub
		+	</pre>
		+	you define the <tt>sub</tt> and then query on it as usual, with the exception of bit more explicit access to columns:
		+	<pre>
		+	sub = session.query(table.x.label('c1'),
		+	table.y.label('c2')).\
		+	cte('sub')
		+
		+	res = session.query(sub.c.c1, sub.c.c2).\
		+	all()
		+	</pre>