def __init__(self, db, schema, table, columns=None): self.db = db self.schema = schema self.name = table self.engine = create_engine(db.url) self.metadata = MetaData(schema=schema) self.metadata.bind = self.engine # http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html # if provided columns (SQLAlchemy columns), create the table if table: if columns: self.table = SQLATable(table, self.metadata, schema=self.schema, *columns) self.table.create() # otherwise just load from db else: self.table = SQLATable(table, self.metadata, schema=self.schema, autoload=True) self.indexes = dict((i.name, i) for i in self.table.indexes) self._is_dropped = False else: self._is_dropped = True self.table = None
def _sync_table(self, columns): """Lazy load, create or adapt the table structure in the database.""" if self._table is None: # Load an existing table from the database. self._reflect_table() if self._table is None: # Create the table with an initial set of columns. if not self._auto_create: raise DatasetException("Table does not exist: %s" % self.name) # Keep the lock scope small because this is run very often. with self.db.lock: self._threading_warn() self._table = SQLATable(self.name, self.db.metadata, schema=self.db.schema) if self._primary_id is not False: # This can go wrong on DBMS like MySQL and SQLite where # tables cannot have no columns. primary_id = self._primary_id or self.PRIMARY_DEFAULT primary_type = self._primary_type or Types.integer increment = primary_type in [Types.integer, Types.bigint] column = Column(primary_id, primary_type, primary_key=True, autoincrement=increment) self._table.append_column(column) for column in columns: self._table.append_column(column) self._table.create(self.db.executable, checkfirst=True) elif len(columns): with self.db.lock: self._threading_warn() for column in columns: if not self.has_column(column.name): self.db.op.add_column(self.name, column, self.db.schema) self._reflect_table()
def load_table(self, table_name): """ Load a table. This will fail if the tables does not already exist in the database. If the table exists, its columns will be reflected and are available on the :py:class:`Table <dataset.Table>` object. Returns a :py:class:`Table <dataset.Table>` instance. :: table = db.load_table('population') """ table_name = self._valid_table_name(table_name) self._acquire() try: log.debug("Loading table: %s on %r" % (table_name, self)) table = SQLATable(table_name, self.metadata, schema=self.schema, autoload=True) self._tables[table_name] = table return Table(self, table) finally: self._release()
def create_table(self, table_name, primary_id='id', primary_type='Integer'): """ Creates a new table. The new table will automatically have an `id` column unless specified via optional parameter primary_id, which will be used as the primary key of the table. Automatic id is set to be an auto-incrementing integer, while the type of custom primary_id can be a String or an Integer as specified with primary_type flag. The default length of String is 255. The caller can specify the length. The caller will be responsible for the uniqueness of manual primary_id. This custom id feature is only available via direct create_table call. Returns a :py:class:`Table <dataset.Table>` instance. :: table = db.create_table('population') # custom id and type table2 = db.create_table('population2', 'age') table3 = db.create_table('population3', primary_id='race', primary_type='String') # custom length of String table4 = db.create_table('population4', primary_id='race', primary_type='String(50)') """ table_name = self._valid_table_name(table_name) self._acquire() try: log.debug("Creating table: %s on %r" % (table_name, self.engine)) match = re.match(r'^(Integer)$|^(String)(\(\d+\))?$', primary_type) if match: if match.group(1) == 'Integer': auto_flag = False if primary_id == 'id': auto_flag = True col = Column(primary_id, Integer, primary_key=True, autoincrement=auto_flag) elif not match.group(3): col = Column(primary_id, String(255), primary_key=True) else: len_string = int(match.group(3)[1:-1]) len_string = min(len_string, 255) col = Column(primary_id, String(len_string), primary_key=True) else: raise DatasetException( "The primary_type has to be either 'Integer' or 'String'.") table = SQLATable(table_name, self.metadata, schema=self.schema) table.append_column(col) table.create(self.engine) self._tables[table_name] = table return Table(self, table) finally: self._release()
def update_table(self, table_name): table_name = self._valid_table_name(table_name) self.metadata = MetaData(schema=self.schema) self.metadata.bind = self.engine self.metadata.reflect(self.engine) self._tables[table_name] = SQLATable(table_name, self.metadata, schema=self.schema) return self._tables[table_name]
def _reflect_table(self): """Load the tables definition from the database.""" with self.db.lock: try: self._table = SQLATable(self.name, self.db.metadata, schema=self.db.schema, autoload=True) except NoSuchTableError: pass
def rename(self, name): """Rename the table """ sql = """ALTER TABLE {s}.{t} RENAME TO {name} """.format(s=self.schema, t=self.name, name=name) self.engine.execute(sql) self.table = SQLATable(name, self.metadata, schema=self.schema, autoload=True)
def create_table(self, table_name, primary_id='id', primary_type='Integer'): """ Creates a new table. The new table will automatically have an `id` column unless specified via optional parameter primary_id, which will be used as the primary key of the table. Automatic id is set to be an auto-incrementing integer, while the type of custom primary_id can be a Text or an Integer as specified with primary_type flag. The caller will be responsible for the uniqueness of manual primary_id. This custom id feature is only available via direct create_table call. Returns a :py:class:`Table <dataset.Table>` instance. :: table = db.create_table('population') # custom id and type table2 = db.create_table('population2', 'age') table3 = db.create_table('population3', primary_id='race', primary_type='Text') """ self._acquire() try: log.debug("Creating table: %s on %r" % (table_name, self.engine)) table = SQLATable(table_name, self.metadata) if primary_type is 'Integer': auto_flag = False if primary_id is 'id': auto_flag = True col = Column(primary_id, Integer, primary_key=True, autoincrement=auto_flag) elif primary_type is 'Text': col = Column(primary_id, Text, primary_key=True) else: raise DatasetException( "The primary_type has to be either 'Integer' or 'Text'.") table.append_column(col) table.create(self.engine) self._tables[table_name] = table return Table(self, table) finally: self._release()
def create_table(self, table_name): """ Creates a new table. The new table will automatically have an `id` column, which is set to be an auto-incrementing integer as the primary key of the table. Returns a :py:class:`Table <dataset.Table>` instance. :: table = db.create_table('population') """ self._acquire() try: log.debug("Creating table: %s on %r" % (table_name, self.engine)) table = SQLATable(table_name, self.metadata) col = Column('id', Integer, primary_key=True) table.append_column(col) table.create(self.engine) self._tables[table_name] = table return Table(self, table) finally: self._release()
def _update_table(self, table_name): self.metadata = MetaData(schema=self.schema) self.metadata.bind = self.engine return SQLATable(table_name, self.metadata, schema=self.schema)