Select data in a database table and put into prettytable.

    Create a :class:`prettytable.PrettyTable` from :class:`sqlalchemy.Table`.

    **中文文档**

    将数据表中的数据放入prettytable中.
336020
def from_table(table, engine, limit=None):
    
    sql = select([table])
    if limit is not None:
        sql = sql.limit(limit)
    result_proxy = engine.execute(sql)
    return from_db_cursor(result_proxy.cursor)
1196589
def generate_one(tbl, args, table_name=None, file=None):
    
    table = Table(tbl, table_name=table_name, varying_length_text=args.text, uniques=args.uniques,
                  pk_name = args.key, force_pk=args.force_key, reorder=args.reorder, data_size_cushion=args.cushion,
                  save_metadata_to=args.save_metadata_to, metadata_source=args.use_metadata_from,
                  loglevel=args.log, limit=args.limit)
    if args.dialect.startswith('sqla'):
        if not args.no_creates:
            print(table.sqlalchemy(), file=file)
        if args.inserts:
            print("\n".join(table.inserts(dialect=args.dialect)), file=file)
    elif args.dialect.startswith('dj'):
        table.django_models()
    else:
        print(table.sql(dialect=args.dialect, inserts=args.inserts,
                        creates=(not args.no_creates), drops=args.drops,
                        metadata_source=args.use_metadata_from), file=file)
    return table
23110
def schema_from_table(table, schema=None):
    
    schema = schema if schema is not None else {}
    pairs = []
    for name, column in table.columns.items():
        if name in schema:
            dtype = dt.dtype(schema[name])
        else:
            dtype = dt.dtype(
                getattr(table.bind, 'dialect', SQLAlchemyDialect()),
                column.type,
                nullable=column.nullable,
            )
        pairs.append((name, dtype))
    return sch.schema(pairs)
813276
def init_from_database(self, conn, database, table_name):
        
        info = conn.query('SHOW TABLE STATUS FROM `{database}` WHERE name="{table_name}"'.format(
            table_name=table_name, database=database), as_dict=True).fetchone()
        if info is None:
            if table_name == '~log':
                logger.warning('Could not create the ~log table')
                return
            else:
                raise DataJointError('The table `{database}`.`{table_name}` is not defined.'.format(
                    table_name=table_name, database=database))
        self.table_info = {k.lower(): v for k, v in info.items()}

        cur = conn.query(
            'SHOW FULL COLUMNS FROM `{table_name}` IN `{database}`'.format(
                table_name=table_name, database=database), as_dict=True)

        attributes = cur.fetchall()

        rename_map = {
            'Field': 'name',
            'Type': 'type',
            'Null': 'nullable',
            'Default': 'default',
            'Key': 'in_key',
            'Comment': 'comment'}

        fields_to_drop = ('Privileges', 'Collation')

        # rename and drop attributes
        attributes = [{rename_map[k] if k in rename_map else k: v
                       for k, v in x.items() if k not in fields_to_drop}
                      for x in attributes]

        numeric_types = {
            ('float', False): np.float64,
            ('float', True): np.float64,
            ('double', False): np.float64,
            ('double', True): np.float64,
            ('tinyint', False): np.int64,
            ('tinyint', True): np.int64,
            ('smallint', False): np.int64,
            ('smallint', True): np.int64,
            ('mediumint', False): np.int64,
            ('mediumint', True): np.int64,
            ('int', False): np.int64,
            ('int', True): np.int64,
            ('bigint', False): np.int64,
            ('bigint', True): np.uint64}

        sql_literals = ['CURRENT_TIMESTAMP']

        # additional attribute properties
        for attr in attributes:
            # process external attributes
            split_comment = attr['comment'].split(':')
            attr['is_external'] = len(split_comment) >= 3 and split_comment[1].startswith('external')
            if attr['is_external']:
                attr['comment'] = ':'.join(split_comment[2:])
                attr['type'] = split_comment[1]

            attr['nullable'] = (attr['nullable'] == 'YES')
            attr['in_key'] = (attr['in_key'] == 'PRI')
            attr['autoincrement'] = bool(re.search(r'auto_increment', attr['Extra'], flags=re.IGNORECASE))
            attr['type'] = re.sub(r'int\(\d+\)', 'int', attr['type'], count=1)   # strip size off integers
            attr['numeric'] = bool(re.match(r'(tiny|small|medium|big)?int|decimal|double|float', attr['type']))
            attr['string'] = bool(re.match(r'(var)?char|enum|date|year|time|timestamp', attr['type']))
            attr['is_blob'] = attr['is_external'] or bool(re.match(r'(tiny|medium|long)?blob', attr['type']))
            attr['database'] = database

            if attr['string'] and attr['default'] is not None and attr['default'] not in sql_literals:
                attr['default'] = '"%s"' % attr['default']

            if attr['nullable']:   # nullable fields always default to null
                attr['default'] = 'null'

            attr['sql_expression'] = None
            if not (attr['numeric'] or attr['string'] or attr['is_blob']):
                raise DataJointError('Unsupported field type {field} in `{database}`.`{table_name}`'.format(
                    field=attr['type'], database=database, table_name=table_name))
            attr.pop('Extra')

            # fill out dtype. All floats and non-nullable integers are turned into specific dtypes
            attr['dtype'] = object
            if attr['numeric']:
                is_integer = bool(re.match(r'(tiny|small|medium|big)?int', attr['type']))
                is_float = bool(re.match(r'(double|float)', attr['type']))
                if is_integer and not attr['nullable'] or is_float:
                    is_unsigned = bool(re.match('\sunsigned', attr['type'], flags=re.IGNORECASE))
                    t = attr['type']
                    t = re.sub(r'\(.*\)', '', t)    # remove parentheses
                    t = re.sub(r' unsigned$', '', t)   # remove unsigned
                    assert (t, is_unsigned) in numeric_types, 'dtype not found for type %s' % t
                    attr['dtype'] = numeric_types[(t, is_unsigned)]
        self.attributes = OrderedDict([(q['name'], Attribute(**q)) for q in attributes])

        # Read and tabulate secondary indexes
        keys = defaultdict(dict)
        for item in conn.query('SHOW KEYS FROM `{db}`.`{tab}`'.format(db=database, tab=table_name), as_dict=True):
            if item['Key_name'] != 'PRIMARY':
                keys[item['Key_name']][item['Seq_in_index']] = dict(
                    column=item['Column_name'],
                    unique=(item['Non_unique'] == 0),
                    nullable=item['Null'].lower() == 'yes')
        self.indexes = {
            tuple(item[k]['column'] for k in sorted(item.keys())):
                dict(unique=item[1]['unique'],
                     nullable=any(v['nullable'] for v in item.values()))
            for item in keys.values()}
752150
def table(args):
    
    import csv

    p = OptionParser(table.__doc__)
    p.set_sep(sep=",")
    opts, args = p.parse_args(args)

    if len(args) != 1:
        sys.exit(not p.print_help())

    htmlfile, = args
    page = open(htmlfile).read()
    soup = BeautifulSoup(page)

    for i, tabl in enumerate(soup.findAll('table')):
        nrows = 0
        csvfile = htmlfile.rsplit(".", 1)[0] + ".{0}.csv".format(i)
        writer = csv.writer(open(csvfile, "w"), delimiter=opts.sep)
        rows = tabl.findAll('tr')
        for tr in rows:
            cols = tr.findAll('td')
            if not cols:
                cols = tr.findAll('th')

            row = []
            for td in cols:
                try:
                    cell = "".join(td.find(text=True))
                    cell = unescape(cell)
                except TypeError:
                    cell = ""
                row.append(cell)
            writer.writerow(row)
            nrows += 1
        logging.debug("Table with {0} rows written to `{1}`.".format(nrows, csvfile))
1168360
def table_from_data(data, column_names):
    ""

    # The agate table is generated from a list of dicts, so the column order
    # from `data` is not preserved. We can use `select` to reorder the columns
    #
    # If there is no data, create an empty table with the specified columns

    if len(data) == 0:
        return agate.Table([], column_names=column_names)
    else:
        table = agate.Table.from_object(data, column_types=DEFAULT_TYPE_TESTER)
        return table.select(column_names)
748453
def _initTable(self, tableName, schemaDef):
        cursor=_conn.execute("""
            SELECT * FROM sqlite_master WHERE name ='{0}' and type='table';
        """.format(tableName))
        if cursor.fetchone() is None:
            _conn.execute('''CREATE TABLE {0} ({1});'''.format(tableName, schemaDef))
            print("Table {0} created successfully".format(tableName))
        '''
        
        '''
        cursor.close()
292117
def db_schema(db, table):
  
  typedict = [('int', int), ('double', float),
              ('timestamp', datetime),
              ('date', date), ('time', dttime),
              ('text', str), ('char', str)]
  ret = {}
  q = '''select column_name, data_type
          from information_schema.columns
          where table_name = %s;'''
  #q = """select c.name, c.type from sys.columns as c, sys.tables as t where table_id = t.id and t.name = %s;"""
  # and data_type != 'date' and position('time' in data_type) =
  # 0 and column_name != 'humidity'
  for row in query(db, q, (table,)):
      name, dtype = tuple( row[:2] )
      name = str(name)
      for tn, tt in typedict:
          if tn in dtype:
              ret[name] = tt
              break
      if name not in ret:
          msg = "can't find type of %s\t%s"
          raise RuntimeError(msg % (name, dtype))
  return ret
33640
def _justCreateTable(self, tableClass):
        
        sqlstr = []
        sqlarg = []

        # needs to be calculated including version
        tableName = self._tableNameFor(tableClass.typeName,
                                       tableClass.schemaVersion)

        sqlstr.append("CREATE TABLE %s (" % tableName)

        # The column is named "oid" instead of "storeID" for backwards
        # compatibility with the implicit oid/rowid column in old Stores.
        sqlarg.append("oid INTEGER PRIMARY KEY")
        for nam, atr in tableClass.getSchema():
            sqlarg.append("\n%s %s" %
                          (atr.getShortColumnName(self), atr.sqltype))

        sqlstr.append(', '.join(sqlarg))
        sqlstr.append(')')
        self.createSQL(''.join(sqlstr))
1118774
def DBObject(table_name, versioning=VersioningTypes.NONE):
    """"""
    def wrapped(cls):
        field_names = set()
        all_fields = []

        for name in dir(cls):
            fld = getattr(cls, name)
            if fld and isinstance(fld, Field):
                fld.name = name
                all_fields.append(fld)
                field_names.add(name)

        def add_missing_field(name, default='', insert_pos=None):
            if name not in field_names:
                fld = Field(default=default)
                fld.name = name
                all_fields.insert(
                    len(all_fields) if insert_pos is None else insert_pos,
                    fld
                )

        add_missing_field('id', insert_pos=0)
        add_missing_field('_create_date')
        add_missing_field('_last_update')
        if versioning == VersioningTypes.DELTA_HISTORY:
            add_missing_field('_version_hist', default=list)

        # Things we count on as part of our processing
        cls.__table_name__ = table_name
        cls.__versioning__ = versioning
        cls.__fields__ = all_fields

        # Give them a ctor for free - but make sure we aren't clobbering one
        if not ctor_overridable(cls):
            raise TypeError(
                'Classes with user-supplied __init__ should not be decorated '
                'with DBObject. Use the setup method'
            )
        cls.__init__ = _auto_init

        # Duck-type the class for our data methods
        cls.get_table_name = classmethod(_get_table_name)
        cls.get_id = _get_id
        cls.set_id = _set_id
        cls.to_data = _to_data
        cls.from_data = classmethod(_from_data)
        cls.index_names = classmethod(_index_names)
        cls.indexes = _indexes
        # Bonus methods they get for using gludb.simple
        cls.get_version_hist = _get_version_hist

        # Register with our abc since we actually implement all necessary
        # functionality
        Storable.register(cls)

        # And now that we're registered, we can also get the database
        # read/write functionality for free
        cls = DatabaseEnabled(cls)

        if versioning == VersioningTypes.DELTA_HISTORY:
            cls.save = _delta_save(cls.save)

        return cls

    return wrapped
863731
def insert_instance(instance, table, **kwargs):
        
        instancedict = instance.__dict__.copy()
        instancedictclone = instancedict.copy()

        # Remove all Nonetype values
        for k, v in instancedictclone.iteritems():
            if v is None:
                instancedict.pop(k)

        keys, values = CoyoteDb.get_insert_fields_and_values_from_dict(instancedict)
        sql = """INSERT INTO {table} ({keys}) VALUES ({values})""".format(
            table=table,
            keys=keys,
            values=values
        )

        insert = CoyoteDb.insert(sql=sql, **kwargs)
        return insert
677502
def import_table(self, source, table_name):
        """"""
        # Run commands #
        wsl = sh.Command("wsl.exe")
        table_schema   = wsl("-e", "mdb-schema", "-T", table_name, source.wsl_style, "access")
        table_contents = wsl("-e", "mdb-export", "-I", "access", source.wsl_style, table_name)
        # Filter #
        table_schema = ' '.join(l for l in table_schema.split('\n') if not l.startswith("--"))
        # Execute statements #
        self.cursor.execute(str(table_schema))
        self.cursor.execute(str(table_contents))
1139824
def db_create_table(self, table_name, columns):
        
        formatted_columns = ''
        for col in set(columns):
            formatted_columns += '"{}" text, '.format(col.strip('"').strip('\''))
        formatted_columns = formatted_columns.strip(', ')

        create_table_sql = 'CREATE TABLE IF NOT EXISTS {} ({});'.format(
            table_name, formatted_columns
        )
        try:
            cr = self.db_conn.cursor()
            cr.execute(create_table_sql)
        except sqlite3.Error as e:
            self.handle_error(e)
1094152
def create_tables(self):
        
        cdir = os.path.dirname( os.path.realpath(__file__) )
        
        # table schemas -------------------------------------
        schema = os.path.join(cdir,"data","partsmaster.sql")
        if self.debug:
            print(self.hdr,"parts master schema is ",schema)
        
        self.populate(schema)
        
        schema = os.path.join(cdir,"data","approvedmfg.sql")
        if self.debug:
            print(self.hdr,"approved mfg list schema is ",schema)
        
        self.populate(schema)
        
        schema = os.path.join(cdir,"data","attachment.sql")
        if self.debug:
            print(self.hdr,"attachment schema is ",schema)
        
        self.populate(schema)

        schema = os.path.join(cdir,"data","bom.sql")
        if self.debug:
            print(self.hdr,"bill of materials schema is ",schema)
        
        self.populate(schema)
        
        return
451484
def createTable(dbconn, pd):
    """"""
    cols = ('%s %s' % (defn.name, getTypename(defn)) for defn in pd.fields)
    sql  = 'CREATE TABLE IF NOT EXISTS %s (%s)' % (pd.name, ', '.join(cols))

    dbconn.execute(sql)
    dbconn.commit()
489841
def create_model_class(cls, name, table_name, attrs, validations=None, *, otherattrs=None,
                         other_bases=None):
    """"""
    members = dict(table_name=table_name, attrs=attrs, validations=validations or [])
    if otherattrs:
      members.update(otherattrs)
    return type(name, other_bases or (), members)
143039
def _create_table(self):
        '''
        
        '''
        self._cursor.execute('''
            CREATE TABLE IF NOT EXISTS %(name)s ( %(fields)s )
        ''' % {
            'name': self._name,
            'fields': ','.join('%s %s' % (k, v) for (k, v) in self._fields)
        })
        self._connection.commit()