Q. 重要參考

Q. Engine Configuration

The Engineis the starting point for any SQLAlchemy application. an Enginereferences both a Dialectand a Pool

Creating an engine is just a matter of issuing a single call, create_engine():

from sqlalchemy import create_engine

engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')

Note that the Engine and its underlying Pool do not establish the first actual DBAPI connection until the Engine.connect() method is called, or an operation which is dependent on this method such as Engine.execute() is invoked.

The Engine, once created, can either be used directly to interact with the database, or can be passed to a Sessionobject to work with the ORM

Q.Database URL

dialect+driver://username:password@host:port/database

PostgreSQL

# default

engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

# pg8000

engine = create_engine('postgresql+pg8000://welen:867080@localhost:5566/test', client_encoding='utf8')

# 啟動 and 停止 PostgreSQL Service on Windows command line

NET START postgresql-x64-9.6 (64-bit windows)
NET STOP postgresql-x64-9.6 (64-bit windows)

NET START postgresql-9.6 (32-bit windows)
NET STOP postgresql-9.6 (32-bit windows)

Note: This is for Windows 7. Replace 9.6  with your version of PostgreSQL version.

# Windows 7 指令
netstat -nao | find "0.0.0.0:5566" => 查看 port 5566 有沒有 service 在用
netstat -nao | find "LISTENING"  => 查看目前那些port在LISTENING
tasklist /fi "pid eq 2032" => 查看pid 對應到的 service

MySQL

# default

engine = create_engine('mysql://scott:tiger@localhost/foo')

# mysql-python

engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

SQLite

SQLite connects to file-based databases, using the Python built-in module sqlite3by default.

For a relative file path, this requires three slashes:

# sqlite://<nohostname>/<path>

# where <path> is relative:

engine = create_engine('sqlite:///foo.db')

Absolute path

#Unix/Mac - 4 initial slashes in total

engine = create_engine('sqlite:////absolute/path/to/foo.db')

#Windows

engine = create_engine('sqlite:///C:\\path\\to\\foo.db')

#Windows alternative using raw string

engine = create_engine(r'sqlite:///C:\path\to\foo.db')

To use a SQLite:memory: database, specify an empty URL

engine = create_engine('sqlite://')

engine = create_engine('sqlite:///:memory:')

Q. SQLAlchemy Object Relational Mapper (ORM)

Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables.

The ORM is in contrast to the SQLAlchemy Expression Language, upon which the ORM is constructed.

A successful application may be constructed using the Object Relational Mapper exclusively.

Declare a Mapping

When using the ORM, the configurational process starts by describing the database tables. And then by defining our own classes which will be mapped to those tables.

These task is done by Declarative, which allows us to create classes that include directives to describe the actual database table they will be mapped to.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

We will start with just a single table called users, which will store records for the end-users using our application. A new class called Userwill be the class to which we map this table.

from sqlalchemy import Column, Integer, String

class User(Base):

__tablename__ = 'users' # Class User 映對到資料庫中的 users 資料表

id = Column(Integer, primary_key=True) # SQLite has an implicit “auto increment” feature

name = Column(String)

username = Column(String)

password = Column(String)

A class using Declarative at a minimum needs a __tablename__attribute, and at least one Columnwhich is part of a primary key

Q. Declarative

The Declarative system is the typically used system provided by the SQLAlchemy ORM in order to define classes mapped to relational database tables

Declarative is in fact a series of extensions that ride on top of the SQLAlchemy mapper() construct.

SQLAlchemy object-relational configuration involves the combination of Table, mapper(), and class objects to define a mapped class. declarative allows all three to be expressed at once within the class declaration

範例

from sqlalchemy import Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class SomeClass(Base):

__tablename__ = 'some_table'

id = Column(Integer, primary_key=True)

name = Column(String(50))

Above, the declarative_base() callable returns a new base class from which all mapped classes should inherit. When the class definition is completed, a new Table and mapper() will have been generated.

# access the mapped Table

SomeClass.__table__

# access the Mapper

SomeClass.__mapper__

Defining Attributes

In previous example, the Columnobjects are automatically named with the name of the attribute to which they are assigned

Accessing the MetaData

The declarative_base() base class contains a MetaData object where newly defined Table objects are collected.