select query

Steven Chen · Steven Chen · commit d13594b5c03f · 2023-11-01T02:15:39.000-07:00
diff --git a/03_select/__init__.py b/03_select/__init__.py
diff --git a/03_select/db_init.py b/03_select/db_init.py
@@ -0,0 +1,14 @@
+import sqlalchemy
+
+engine = sqlalchemy.create_engine('mysql://root:test@localhost/testdb', echo=True)
+
+meta_data = sqlalchemy.MetaData()
+
+person_table = sqlalchemy.Table(
+    "person", meta_data,
+    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
+    sqlalchemy.Column("name", sqlalchemy.String(128), unique=True, nullable=False),
+    sqlalchemy.Column("birthday", sqlalchemy.Date, nullable=False),
+)
+
+meta_data.create_all(engine)
diff --git a/03_select/select_all.py b/03_select/select_all.py
@@ -0,0 +1,15 @@
+from db_init import engine, person_table
+
+with engine.connect() as conn:
+    query = person_table.select()
+    result_set = conn.execute(query)
+
+    # for row in result_set:
+    #     print(row[0])
+    #     print(row.name)
+
+    # result = result_set.fetchall()
+    # print(result)
+
+    row = result_set.fetchone()
+    print(row)
diff --git a/03_select/select_by_birthday.py b/03_select/select_by_birthday.py
@@ -0,0 +1,8 @@
+from db_init import engine, person_table
+
+with engine.connect() as conn:
+    query = person_table.select().where(person_table.c.birthday > '2000-10-13')
+    result_set = conn.execute(query)
+
+    result = result_set.fetchall()
+    print(result)
diff --git a/03_select/select_by_multiple.py b/03_select/select_by_multiple.py
@@ -0,0 +1,18 @@
+from db_init import engine, person_table
+from sqlalchemy.sql import and_, or_
+
+with engine.connect() as conn:
+    # query = person_table.select().where(person_table.c.birthday > '2000-10-13').where(person_table.c.id < 6)
+    query = person_table.select().where(
+        or_(
+            person_table.c.name == 'Tom',
+            and_(
+                person_table.c.birthday > '2000-10-13',
+                person_table.c.id < 7
+            )
+        )
+    )
+    result_set = conn.execute(query)
+
+    result = result_set.fetchall()
+    print(result)
