示例#1
0
def drop_table(dbo, tablename, schema=None, commit=True):
    """
    Drop a database table.

    Keyword arguments:

    dbo : database object
        DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or
        SQLAlchemy connection, engine or session
    tablename : text
        Name of the table
    schema : text
        Name of the database schema the table is in
    commit : bool
        If True commit the changes

    """

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + "." + tablename

    sql = u"DROP TABLE %s" % tablename
    _execute(sql, dbo, commit)
示例#2
0
def drop_table(dbo, tablename, schema=None, commit=True):
    """
    Drop a database table.

    Keyword arguments:

    dbo : database object
        DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or
        SQLAlchemy connection, engine or session
    tablename : text
        Name of the table
    schema : text
        Name of the database schema the table is in
    commit : bool
        If True commit the changes

    """

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + '.' + tablename

    sql = u'DROP TABLE %s' % tablename
    _execute(sql, dbo, commit)
示例#3
0
def _todb_dbapi_connection(table,
                           connection,
                           tablename,
                           schema=None,
                           commit=True,
                           truncate=False,
                           replace=False):

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + '.' + tablename
    debug('tablename: %r', tablename)

    # sanitise field names
    it = iter(table)
    hdr = next(it)
    flds = list(map(text_type, hdr))
    colnames = [_quote(n) for n in flds]
    debug('column names: %r', colnames)

    # determine paramstyle and build placeholders string
    placeholders = _placeholders(connection, colnames)
    debug('placeholders: %r', placeholders)

    # get a cursor
    cursor = connection.cursor()

    if truncate:
        # TRUNCATE is not supported in some databases and causing locks with
        # MySQL used via SQLAlchemy, fall back to DELETE FROM for now
        truncatequery = SQL_TRUNCATE_QUERY % tablename
        debug('truncate the table via query %r', truncatequery)
        cursor.execute(truncatequery)
        # just in case, close and resurrect cursor
        cursor.close()
        cursor = connection.cursor()

    insertcolnames = ', '.join(colnames)
    if replace:
        insertquery = SQL_REPLACE_QUERY % (tablename, insertcolnames,
                                           placeholders)
    else:
        insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames,
                                          placeholders)
    debug('insert data via query %r' % insertquery)
    cursor.executemany(insertquery, it)

    # finish up
    debug('close the cursor')
    cursor.close()

    if commit:
        debug('commit transaction')
        connection.commit()
示例#4
0
文件: db.py 项目: larissarmp/TCC
def _todb_sqlalchemy_connection(table,
                                connection,
                                tablename,
                                schema=None,
                                commit=True,
                                truncate=False):

    debug('connection: %r', connection)

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + '.' + tablename
    debug('tablename: %r', tablename)

    # sanitise field names
    it = iter(table)
    hdr = next(it)
    flds = list(map(text_type, hdr))
    colnames = [_quote(n) for n in flds]
    debug('column names: %r', colnames)

    # N.B., we need to obtain a reference to the underlying DB-API connection so
    # we can import the module and determine the paramstyle
    proxied_raw_connection = connection.connection
    actual_raw_connection = proxied_raw_connection.connection

    # determine paramstyle and build placeholders string
    placeholders = _placeholders(actual_raw_connection, colnames)
    debug('placeholders: %r', placeholders)

    if commit:
        debug('begin transaction')
        trans = connection.begin()

    if truncate:
        # TRUNCATE is not supported in some databases and causing locks with
        # MySQL used via SQLAlchemy, fall back to DELETE FROM for now
        truncatequery = SQL_TRUNCATE_QUERY % tablename
        debug('truncate the table via query %r', truncatequery)
        connection.execute(truncatequery)

    insertcolnames = ', '.join(colnames)
    insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
    debug('insert data via query %r' % insertquery)
    for row in it:
        connection.execute(insertquery, row)

    # finish up

    if commit:
        debug('commit transaction')
        trans.commit()
示例#5
0
文件: db.py 项目: larissarmp/TCC
def _todb_dbapi_mkcurs(table,
                       mkcurs,
                       tablename,
                       schema=None,
                       commit=True,
                       truncate=False):

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + '.' + tablename
    debug('tablename: %r', tablename)

    # sanitise field names
    it = iter(table)
    hdr = next(it)
    flds = list(map(text_type, hdr))
    colnames = [_quote(n) for n in flds]
    debug('column names: %r', colnames)

    debug('obtain cursor and connection')
    cursor = mkcurs()
    # N.B., we depend on this optional DB-API 2.0 attribute being implemented
    assert hasattr(cursor, 'connection'), \
        'could not obtain connection via cursor'
    connection = cursor.connection

    # determine paramstyle and build placeholders string
    placeholders = _placeholders(connection, colnames)
    debug('placeholders: %r', placeholders)

    if truncate:
        # TRUNCATE is not supported in some databases and causing locks with
        # MySQL used via SQLAlchemy, fall back to DELETE FROM for now
        truncatequery = SQL_TRUNCATE_QUERY % tablename
        debug('truncate the table via query %r', truncatequery)
        cursor.execute(truncatequery)
        # N.B., may be server-side cursor, need to resurrect
        cursor.close()
        cursor = mkcurs()

    insertcolnames = ', '.join(colnames)
    insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
    debug('insert data via query %r' % insertquery)
    cursor.executemany(insertquery, it)
    cursor.close()

    if commit:
        debug('commit transaction')
        connection.commit()
示例#6
0
文件: db.py 项目: Mgutjahr/petl
def _todb_sqlalchemy_connection(table, connection, tablename, schema=None,
                                commit=True, truncate=False):

    debug('connection: %r', connection)

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + '.' + tablename
    debug('tablename: %r', tablename)

    # sanitise field names
    it = iter(table)
    hdr = next(it)
    flds = list(map(text_type, hdr))
    colnames = [_quote(n) for n in flds]
    debug('column names: %r', colnames)

    # N.B., we need to obtain a reference to the underlying DB-API connection so
    # we can import the module and determine the paramstyle
    proxied_raw_connection = connection.connection
    actual_raw_connection = proxied_raw_connection.connection

    # determine paramstyle and build placeholders string
    placeholders = _placeholders(actual_raw_connection, colnames)
    debug('placeholders: %r', placeholders)

    if commit:
        debug('begin transaction')
        trans = connection.begin()

    if truncate:
        # TRUNCATE is not supported in some databases and causing locks with
        # MySQL used via SQLAlchemy, fall back to DELETE FROM for now
        truncatequery = SQL_TRUNCATE_QUERY % tablename
        debug('truncate the table via query %r', truncatequery)
        connection.execute(truncatequery)

    insertcolnames = ', '.join(colnames)
    insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
    debug('insert data via query %r' % insertquery)
    for row in it:
        connection.execute(insertquery, row)

    # finish up

    if commit:
        debug('commit transaction')
        trans.commit()
示例#7
0
    def stmt(self):
        # handle fields
        fields = self.fields
        if fields is None:
            # default to non geom fields
            fields = self.table.non_geom_fields
        fields = [_quote(field) for field in fields]

        # handle geom
        geom_field = self.table.geom_field
        if geom_field and self.return_geom:
            wkt_getter = self.table.wkt_getter(geom_field, self.to_srid)
            fields.append(wkt_getter)

        # form statement
        fields_joined = ', '.join(fields)
        stmt = 'SELECT {} FROM {}'.format(fields_joined,
                                          self.table.name_with_schema)

        where = self.where
        if where:
            stmt += ' WHERE {}'.format(where)

        limit = self.limit
        if limit:
            stmt += ' LIMIT {}'.format(limit)

        # print('from stmt', stmt)

        return stmt
示例#8
0
    def start(self):
        BaseManager.register('getDispatchedJobQueue')
        BaseManager.register('getConfigQueue')
        self.manager = BaseManager(address=(self.host, self.port), authkey=b'huafeng@123+1s')

        try:
            self.manager.connect()
        except:
            time.sleep(3)
            self.manager.connect()

        self.dispatchJob = self.manager.getDispatchedJobQueue()
        
        self.setting = self.manager.getConfigQueue().get(1)
        self.manager.getConfigQueue().put(self.setting)
        
        TO_DB_CON = getDBConnection(self.setting['to_db_setting'])
        hdr = self.setting['to_field_list']
        flds = list(map(str, hdr))
        colnames = [_quote(n) for n in flds]
        insertcolnames = ', '.join(colnames)
        placeholders = _placeholders(TO_DB_CON, colnames)
        insertquery = SQL_INSERT_QUERY % (self.setting['to_db_table'], insertcolnames, placeholders)
        TO_DB_CON.close()
        #FROM_DB_CON.close()
        return insertquery
示例#9
0
文件: db.py 项目: Mgutjahr/petl
def _todb_dbapi_mkcurs(table, mkcurs, tablename, schema=None, commit=True,
                       truncate=False):

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + '.' + tablename
    debug('tablename: %r', tablename)

    # sanitise field names
    it = iter(table)
    hdr = next(it)
    flds = list(map(text_type, hdr))
    colnames = [_quote(n) for n in flds]
    debug('column names: %r', colnames)

    debug('obtain cursor and connection')
    cursor = mkcurs()
    # N.B., we depend on this optional DB-API 2.0 attribute being implemented
    assert hasattr(cursor, 'connection'), \
        'could not obtain connection via cursor'
    connection = cursor.connection

    # determine paramstyle and build placeholders string
    placeholders = _placeholders(connection, colnames)
    debug('placeholders: %r', placeholders)

    if truncate:
        # TRUNCATE is not supported in some databases and causing locks with
        # MySQL used via SQLAlchemy, fall back to DELETE FROM for now
        truncatequery = SQL_TRUNCATE_QUERY % tablename
        debug('truncate the table via query %r', truncatequery)
        cursor.execute(truncatequery)
        # N.B., may be server-side cursor, need to resurrect
        cursor.close()
        cursor = mkcurs()

    insertcolnames = ', '.join(colnames)
    insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
    debug('insert data via query %r' % insertquery)
    cursor.executemany(insertquery, it)
    cursor.close()

    if commit:
        debug('commit transaction')
        connection.commit()
示例#10
0
文件: db.py 项目: Mgutjahr/petl
def _todb_dbapi_connection(table, connection, tablename, schema=None,
                           commit=True, truncate=False):

    # sanitise table name
    tablename = _quote(tablename)
    if schema is not None:
        tablename = _quote(schema) + '.' + tablename
    debug('tablename: %r', tablename)

    # sanitise field names
    it = iter(table)
    hdr = next(it)
    flds = list(map(text_type, hdr))
    colnames = [_quote(n) for n in flds]
    debug('column names: %r', colnames)

    # determine paramstyle and build placeholders string
    placeholders = _placeholders(connection, colnames)
    debug('placeholders: %r', placeholders)

    # get a cursor
    cursor = connection.cursor()

    if truncate:
        # TRUNCATE is not supported in some databases and causing locks with
        # MySQL used via SQLAlchemy, fall back to DELETE FROM for now
        truncatequery = SQL_TRUNCATE_QUERY % tablename
        debug('truncate the table via query %r', truncatequery)
        cursor.execute(truncatequery)
        # just in case, close and resurrect cursor
        cursor.close()
        cursor = connection.cursor()

    insertcolnames = ', '.join(colnames)
    insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
    debug('insert data via query %r' % insertquery)
    cursor.executemany(insertquery, it)

    # finish up
    debug('close the cursor')
    cursor.close()

    if commit:
        debug('commit transaction')
        connection.commit()
示例#11
0
    def name_with_schema(self):
        """Returns the table name prepended with the schema name, prepared for
        a query."""

        if self.schema:
            comps = [self.schema, self.name]
            name_with_schema = '.'.join([_quote(x) for x in comps])
        else:
            name_with_schema = self.name
        return name_with_schema
示例#12
0
    def start(self):
        # print('slave start')
        BaseManager.register('getDispatchedJobQueue')
        BaseManager.register('getConfigQueue')
        self.manager = BaseManager(address=(self.host, self.port),
                                   authkey=b'huafeng@123+1s')

        try:
            self.manager.connect()
        except:
            time.sleep(3)
            self.manager.connect()

        self.dispatchJob = self.manager.getDispatchedJobQueue()

        self.setting = self.manager.getConfigQueue().get(1)
        self.manager.getConfigQueue().put(self.setting)

        TO_DB_CON = getDBConnection(self.setting['to_db_setting'])
        FROM_DB_CON = getDBConnection(self.setting['from_db_setting'])
        fromTable = petl.fromdb(FROM_DB_CON, self.setting['line'])
        it = iter(fromTable)
        hdr = next(it)
        flds = list(map(str, hdr))
        to_colnames = []
        for n in flds:
            if len(n.encode('utf-8')) > 30:
                to_colnames.append(_quote(n[0:10]))
            else:
                to_colnames.append(_quote(n))
        insertcolnames = ', '.join(to_colnames)
        placeholders = _placeholders(TO_DB_CON, to_colnames)
        insertquery = SQL_INSERT_QUERY % (self.setting['to_db_table'],
                                          insertcolnames, placeholders)
        TO_DB_CON.close()
        #FROM_DB_CON.close()
        return insertquery