sa core example

md

![](images/2022-07-28-15-30-32.png)

```
~$ pip install SQLAlchemy

Defaulting to user installation because normal site-packages is not writeable
Collecting SQLAlchemy
  Downloading SQLAlchemy-1.4.39-cp310-cp310-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.6 MB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1.6/1.6 MB 8.5 MB/s eta 0:00:00
Collecting greenlet!=0.4.17
  Downloading greenlet-1.1.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (155 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 155.4/155.4 kB 15.6 MB/s eta 0:00:00
Installing collected packages: greenlet, SQLAlchemy
Successfully installed SQLAlchemy-1.4.39 greenlet-1.1.2

~$
```

```
~$ pg_isready

/var/run/postgresql:5432 - accepting connections

~$
```

```
~$ sudo -iu postgres

postgres@:~$ psql

psql (14.4 (Ubuntu 14.4-1.pgdg20.04+1))
Type "help" for help.

postgres=# CREATE USER me WITH PASSWORD 'you';

CREATE ROLE

postgres=# CREATE DATABASE stuff OWNER me;

CREATE DATABASE

postgres=#
```

```
~$ python3 -q
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.4.39'
>>> quit()
~$
```

![](images/2022-07-28-15-37-29.png)

From https://docs.sqlalchemy.org/en/14/core/engines.html

```
from sqlalchemy import create_engine
# default
engine = create_engine('postgresql://me:you@localhost:5432/stuff')
# psycopg2
# engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
```

```
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, select

metadata_obj = MetaData()

# don't user "user", special table in postgres
user = Table('users', metadata_obj,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email'),
    Column('nickname', String(50), nullable=False)
)

user_prefs = Table('user_prefs', metadata_obj,
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("users.user_id"), nullable=False),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100))
)

metadata_obj.create_all(engine)
```

```
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 pyclient  | client   | UTF8     | C.UTF-8 | C.UTF-8 | 
 stuff     | me       | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(5 rows)

postgres=# \c stuff
You are now connected to database "stuff" as user "postgres".

stuff=# \dt
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | user       | table | me
 public | user_prefs | table | me
(2 rows)

stuff=# 
```

```
>>> i = user.insert().values(user_name='foo',  nickname='bar')
>>> engine.execute(i)
<sqlalchemy.engine.cursor.LegacyCursorResult object at 0x7fd7c8378d90>
```

```
stuff=# select * from users;
 user_id | user_name | email_address | nickname 
---------+-----------+---------------+----------
       1 | foo       |               | bar
(1 row)
```

```
>>> engine.execute(select(user.c.user_name)).fetchall()
[('foo',)]
```

```
# employees.drop(engine)
```