<commit_before><%flags>inherit='document_base.myt'</%flags>
<%attr>title='Database Meta Data'</%attr>
<&|doclib.myt:item, name="metadata", description="Database Meta Data" &>
    <&|doclib.myt:item, name="tables", description="Describing Tables with MetaData" &>
    <p>The core of SQLAlchemy's query and object mapping operations is table metadata, which are Python objects that describe tables.  Metadata objects can be created by explicitly naming the table and all its properties, using the Table, Column, ForeignKey, and Sequence objects imported from <span class="codeline">sqlalchemy.schema</span>, and a database engine constructed as described in the previous section, or they can be automatically pulled from an existing database schema.  First, the explicit version: </p>
        <&|formatting.myt:code&>
        from sqlalchemy import *
        engine = create_engine('sqlite', {'filename':':memory:'}, **opts)
        
        users = Table('users', engine, 
            Column('user_id', Integer, primary_key = True),
            Column('user_name', String(16), nullable = False),
            Column('email_address', String(60), key='email'),
            Column('password', String(20), nullable = False)
        )
        
        user_prefs = Table('user_prefs', engine, 
            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))
        )
        </&>
        <p>The specific datatypes, such as Integer, String, etc. are defined in <&formatting.myt:link, path="types", text="sqlalchemy.types"&> and are automatically pulled in when you import * from <span class="codeline">sqlalchemy</span>.  Note that for Column objects, an altername name can be specified via the "key" parameter; if this parameter is given, then all programmatic references to this Column object will be based on its key, instead of its actual column name.</p>
        
        <p>Once constructed, the Table object provides a clean interface to the table's properties as well as that of its columns:
        
        <&|formatting.myt:code&>
        employees = Table('employees', engine, 
            Column('employee_id', Integer, primary_key=True),
            Column('employee_name', String(60), nullable=False, key='name'),
            Column('employee_dept', Integer, ForeignKey("departments.department_id"))
        )
        
        # access the column "EMPLOYEE_ID":
        employees.columns.employee_id
        
        # or just
        employees.c.employee_id
        
        # via string
        employees.c['employee_id']
        
        # iterate through all columns
        for c in employees.c:
            # ...
            
        # get the table's primary key columns
        for primary_key in employees.primary_key:
            # ...
        
        # get the table's foreign key objects:
        for fkey in employees.foreign_keys:
            # ...
            
        # access the table's SQLEngine object:
        employees.engine
        
        # access a column's name, type, nullable, primary key, foreign key
        employees.c.employee_id.name
        employees.c.employee_id.type
        employees.c.employee_id.nullable
        employees.c.employee_id.primary_key
        employees.c.employee_dept.foreign_key
        
        # get the "key" of a column, which defaults to its name, but can 
        # be any user-defined string:
        employees.c.name.key
        
        # access a column's table:
        employees.c.employee_id.table is employees
        >>> True
        
        # get the table related by a foreign key
        fcolumn = employees.c.employee_dept.foreign_key.column.table
        </&>
        </p>
        
        <p>Metadata objects can also be <b>reflected</b> from tables that already exist in the database.  Reflection means based on a table name, the names, datatypes, and attributes of all columns, including foreign keys, will be loaded automatically.  This feature is supported by all database engines:</p>
        <&|formatting.myt:code&>
        >>> messages = Table('messages', engine, autoload = True)
        >>> [c.name for c in messages.columns]
        ['message_id', 'message_name', 'date']
        </&>
        
        <p>
        Note that if a reflected table has a foreign key referencing another table, then the metadata for the related table will be loaded as well, even if it has not been defined by the application:              
        </p>
        <&|formatting.myt:code&>
        >>> shopping_cart_items = Table('shopping_cart_items', engine, autoload = True)
        >>> print shopping_cart_items.c.cart_id.table.name
        shopping_carts
        </&>
        <p>To get direct access to 'shopping_carts', simply instantiate it via the Table constructor.  You'll get the same instance of the shopping cart Table as the one that is attached to shopping_cart_items:
        <&|formatting.myt:code&>
        >>> shopping_carts = Table('shopping_carts', engine)
        >>> shopping_carts is shopping_cart_items.c.cart_id.table.name
        True
        </&>
        <p>This works because when the Table constructor is called for a particular name and database engine, if the table has already been created then the instance returned will be the same as the original.  This is a <b>singleton</b> constructor:</p>
        <&|formatting.myt:code&>
        >>> news_articles = Table('news', engine, 
        ... Column('article_id', Integer, primary_key = True),
        ... Column('url', String(250), nullable = False)
        ... )
        >>> othertable = Table('news', engine)
        >>> othertable is news_articles
        True
        </&>
    </&>
    <&|doclib.myt:item, name="creating", description="Creating and Dropping Database Tables" &>
    <p>Creating and dropping is easy, just use the <span class="codeline">create()</span> and <span class="codeline">drop()</span> methods:
        <&|formatting.myt:code&>
            <&formatting.myt:poplink&>employees = Table('employees', engine, 
                Column('employee_id', Integer, primary_key=True),
                Column('employee_name', String(60), nullable=False, key='name'),
                Column('employee_dept', Integer, ForeignKey("departments.department_id"))
            )
            employees.create() <&|formatting.myt:codepopper, link="sql" &>
CREATE TABLE employees(
        employee_id SERIAL NOT NULL PRIMARY KEY,
        employee_name VARCHAR(60) NOT NULL,
        employee_dept INTEGER REFERENCES departments(department_id)
)
{}            </&>

            <&formatting.myt:poplink&>employees.drop() <&|formatting.myt:codepopper, link="sql" &>
DROP TABLE employees
{}            </&>
        </&>    
    </&>

    
    <&|doclib.myt:item, name="defaults", description="Column Defaults and OnUpdates" &>
    <p>SQLAlchemy includes flexible constructs in which to create default values for columns upon the insertion of rows, as well as upon update.  These defaults can take several forms: a constant, a Python callable to be pre-executed before the SQL is executed, a SQL expression or function to be pre-executed before the SQL is executed, a pre-executed Sequence (for databases that support sequences), or a "passive" default, which is a default function triggered by the database itself upon insert, the value of which can then be post-fetched by the engine, provided the row provides a primary key in which to call upon.</p>
        <&|doclib.myt:item, name="oninsert", description="Pre-Executed Insert Defaults" &>
        <p>A basic default is most easily specified by the "default" keyword argument to Column.  This defines a value, function, or SQL expression that will be pre-executed to produce the new value, before the row is inserted:</p>
            <&|formatting.myt:code&>
                # a function to create primary key ids
                i = 0
                def mydefault():
                    i += 1
                    return i
                
                t = Table("mytable", db, 
                    # function-based default
                    Column('id', Integer, primary_key=True, default=mydefault),
                    
                    # a scalar default
                    Column('key', String(10), default="default")<commit_msg>fixed default example<commit_after><%flags>inherit='document_base.myt'</%flags>
<%attr>title='Database Meta Data'</%attr>
<&|doclib.myt:item, name="metadata", description="Database Meta Data" &>
    <&|doclib.myt:item, name="tables", description="Describing Tables with MetaData" &>
    <p>The core of SQLAlchemy's query and object mapping operations is table metadata, which are Python objects that describe tables.  Metadata objects can be created by explicitly naming the table and all its properties, using the Table, Column, ForeignKey, and Sequence objects imported from <span class="codeline">sqlalchemy.schema</span>, and a database engine constructed as described in the previous section, or they can be automatically pulled from an existing database schema.  First, the explicit version: </p>
        <&|formatting.myt:code&>
        from sqlalchemy import *
        engine = create_engine('sqlite', {'filename':':memory:'}, **opts)
        
        users = Table('users', engine, 
            Column('user_id', Integer, primary_key = True),
            Column('user_name', String(16), nullable = False),
            Column('email_address', String(60), key='email'),
            Column('password', String(20), nullable = False)
        )
        
        user_prefs = Table('user_prefs', engine, 
            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))
        )
        </&>
        <p>The specific datatypes, such as Integer, String, etc. are defined in <&formatting.myt:link, path="types", text="sqlalchemy.types"&> and are automatically pulled in when you import * from <span class="codeline">sqlalchemy</span>.  Note that for Column objects, an altername name can be specified via the "key" parameter; if this parameter is given, then all programmatic references to this Column object will be based on its key, instead of its actual column name.</p>
        
        <p>Once constructed, the Table object provides a clean interface to the table's properties as well as that of its columns:
        
        <&|formatting.myt:code&>
        employees = Table('employees', engine, 
            Column('employee_id', Integer, primary_key=True),
            Column('employee_name', String(60), nullable=False, key='name'),
            Column('employee_dept', Integer, ForeignKey("departments.department_id"))
        )
        
        # access the column "EMPLOYEE_ID":
        employees.columns.employee_id
        
        # or just
        employees.c.employee_id
        
        # via string
        employees.c['employee_id']
        
        # iterate through all columns
        for c in employees.c:
            # ...
            
        # get the table's primary key columns
        for primary_key in employees.primary_key:
            # ...
        
        # get the table's foreign key objects:
        for fkey in employees.foreign_keys:
            # ...
            
        # access the table's SQLEngine object:
        employees.engine
        
        # access a column's name, type, nullable, primary key, foreign key
        employees.c.employee_id.name
        employees.c.employee_id.type
        employees.c.employee_id.nullable
        employees.c.employee_id.primary_key
        employees.c.employee_dept.foreign_key
        
        # get the "key" of a column, which defaults to its name, but can 
        # be any user-defined string:
        employees.c.name.key
        
        # access a column's table:
        employees.c.employee_id.table is employees
        >>> True
        
        # get the table related by a foreign key
        fcolumn = employees.c.employee_dept.foreign_key.column.table
        </&>
        </p>
        
        <p>Metadata objects can also be <b>reflected</b> from tables that already exist in the database.  Reflection means based on a table name, the names, datatypes, and attributes of all columns, including foreign keys, will be loaded automatically.  This feature is supported by all database engines:</p>
        <&|formatting.myt:code&>
        >>> messages = Table('messages', engine, autoload = True)
        >>> [c.name for c in messages.columns]
        ['message_id', 'message_name', 'date']
        </&>
        
        <p>
        Note that if a reflected table has a foreign key referencing another table, then the metadata for the related table will be loaded as well, even if it has not been defined by the application:              
        </p>
        <&|formatting.myt:code&>
        >>> shopping_cart_items = Table('shopping_cart_items', engine, autoload = True)
        >>> print shopping_cart_items.c.cart_id.table.name
        shopping_carts
        </&>
        <p>To get direct access to 'shopping_carts', simply instantiate it via the Table constructor.  You'll get the same instance of the shopping cart Table as the one that is attached to shopping_cart_items:
        <&|formatting.myt:code&>
        >>> shopping_carts = Table('shopping_carts', engine)
        >>> shopping_carts is shopping_cart_items.c.cart_id.table.name
        True
        </&>
        <p>This works because when the Table constructor is called for a particular name and database engine, if the table has already been created then the instance returned will be the same as the original.  This is a <b>singleton</b> constructor:</p>
        <&|formatting.myt:code&>
        >>> news_articles = Table('news', engine, 
        ... Column('article_id', Integer, primary_key = True),
        ... Column('url', String(250), nullable = False)
        ... )
        >>> othertable = Table('news', engine)
        >>> othertable is news_articles
        True
        </&>
    </&>
    <&|doclib.myt:item, name="creating", description="Creating and Dropping Database Tables" &>
    <p>Creating and dropping is easy, just use the <span class="codeline">create()</span> and <span class="codeline">drop()</span> methods:
        <&|formatting.myt:code&>
            <&formatting.myt:poplink&>employees = Table('employees', engine, 
                Column('employee_id', Integer, primary_key=True),
                Column('employee_name', String(60), nullable=False, key='name'),
                Column('employee_dept', Integer, ForeignKey("departments.department_id"))
            )
            employees.create() <&|formatting.myt:codepopper, link="sql" &>
CREATE TABLE employees(
        employee_id SERIAL NOT NULL PRIMARY KEY,
        employee_name VARCHAR(60) NOT NULL,
        employee_dept INTEGER REFERENCES departments(department_id)
)
{}            </&>

            <&formatting.myt:poplink&>employees.drop() <&|formatting.myt:codepopper, link="sql" &>
DROP TABLE employees
{}            </&>
        </&>    
    </&>

    
    <&|doclib.myt:item, name="defaults", description="Column Defaults and OnUpdates" &>
    <p>SQLAlchemy includes flexible constructs in which to create default values for columns upon the insertion of rows, as well as upon update.  These defaults can take several forms: a constant, a Python callable to be pre-executed before the SQL is executed, a SQL expression or function to be pre-executed before the SQL is executed, a pre-executed Sequence (for databases that support sequences), or a "passive" default, which is a default function triggered by the database itself upon insert, the value of which can then be post-fetched by the engine, provided the row provides a primary key in which to call upon.</p>
        <&|doclib.myt:item, name="oninsert", description="Pre-Executed Insert Defaults" &>
        <p>A basic default is most easily specified by the "default" keyword argument to Column.  This defines a value, function, or SQL expression that will be pre-executed to produce the new value, before the row is inserted:</p>
            <&|formatting.myt:code&>
                # a function to create primary key ids
                i = 0
                def mydefault():
                    global i
                    i += 1
                    return i
                
                t = Table("mytable", db, 
                    # function-based default
                    Column('id', Integer, primary_key=True, default=mydefault),
                    
                    # a scalar default
                    Column('key', String(10), default="default")
{'fraction_non_alphanumeric': 0.10212162401749306, 'fraction_numerical': 0.002836711778263696, 'mean_word_length': 2.540908139778196, 'pattern_counts': {'":': 2, '<': 129, '<?xml version=': 0, '>': 189, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7366260', 'n_tokens_mistral': 4630, 'n_tokens_neox': 4466, 'n_words': 1533}