Fabled upsert for SQLiteDB.

    Perform an upsert based on primary key.

    :param SQLiteDB db: database
    :param str table: table to upsert into
    :param str key_cols: name of key columns
    :param dict update_dict: key-value pairs to upsert
322598
def upsert(db, table, key_cols, update_dict):
    
    with db:
        cur = db.cursor()
        cur.execute(
            'UPDATE {} SET {} WHERE {}'.format(
                table,
                ','.join(_sqlpformat(col) for col in update_dict.keys()),
                ' AND '.join(_sqlpformat(col) for col in key_cols),
            ),
            update_dict,
        )
        if db.changes() == 0:
            keys, values = zip(*update_dict.items())
            cur.execute(
                'INSERT INTO {} ({}) VALUES ({})'.format(
                    table,
                    ','.join(keys),
                    ','.join('?' for _ in values)),
                values)
1146879
def update(sql, *args, **kwargs):
        
        assert "update" in sql.lower(), 'This function requires an update statement, provided: {}'.format(sql)
        cursor = CoyoteDb.execute_and_commit(sql, *args, **kwargs)

        # now get that id
        last_row_id = cursor.lastrowid

        return last_row_id
183833
def batch_upsert_records(session, table, records):
    """"""
    if not records:
        return
    for record_batch in _batch_postgres_query(table, records):
        stmt = insert(table.__table__)
        stmt = stmt.on_conflict_do_update(
            constraint=table.__table__.primary_key,
            set_={
                "keys": stmt.excluded.get("keys"),
                "values": stmt.excluded.get("values"),
            },
        )
        session.execute(stmt, record_batch)
        session.commit()
1088362
def add(self, key):
        '''
        
        '''
        encodedKey = json.dumps(key)
        with self.connect() as conn:
            with doTransaction(conn):
                sql = 'INSERT IGNORE INTO ' + self.table + ' (name) VALUES (%s)'
                return insertSQL(conn, sql, args=[encodedKey])
286340
def update(self, table, columns, values, where):
        
        # Unpack WHERE clause dictionary into tuple
        where_col, where_val = where

        # Create column string from list of values
        cols = get_col_val_str(columns, query_type='update')

        # Concatenate statement
        statement = "UPDATE {0} SET {1} WHERE {2}='{3}'".format(wrap(table), cols, where_col, where_val)

        # Execute statement
        self._cursor.execute(statement, values)
        self._printer('\tMySQL cols (' + str(len(values)) + ') successfully UPDATED')
630655
def _construct_inserts(self, record, new_columns, rename_columns, retype_columns, resize_columns):

        '''  '''

        insert_kwargs = {}

        for key, value in new_columns.items():

        # retrieve value for key (or from old key name)
            if key in rename_columns.keys():
                record_value = getattr(record, rename_columns[key], None)
            else:
                record_value = getattr(record, key, None)

        # attempt to convert datatype
            if record_value:
                if key in retype_columns.keys():
                    try:
                        old_list = False
                        if isinstance(record_value, bytes):
                            record_value = pickle.loads(record_value)
                            old_list = True
                        if retype_columns[key] == 'boolean':
                            record_value = bool(record_value)
                        elif retype_columns[key] == 'string':
                            if old_list:
                                record_value = ','.join(record_value)
                            else:
                                record_value = str(record_value)
                        elif retype_columns[key] == 'integer':
                            if old_list:
                                record_value = int(record_value[0])
                            else:
                                record_value = int(record_value)
                        elif retype_columns[key] == 'float':
                            if old_list:
                                record_value = int(record_value[0])
                            else:
                                record_value = float(record_value)
                        elif retype_columns[key] == 'list':
                            if isinstance(record_value, str):
                                record_value = pickle.dumps(record_value.split(','))
                            else:
                                record_value = pickle.dumps([record_value])
                    except:
                        record_value = None
        
        # attempt to resize string data 
                if key in resize_columns.keys():
                    max_length = resize_columns[key]
                    try:
                        if len(record_value) > max_length:
                            record_value = record_value[0:max_length]
                    except:
                        record_value = None

                insert_kwargs[key] = record_value

        return insert_kwargs
1344004
function bulkUpsert(connection, model, data, done) {
  var query;
  var n = connection.connectionManager.dialectName;
  // Note that the UPSERT type is needed so that SQLite can run multiple statements,
  // specifically use the .exec method of the underlying sqlite library.
  var options = {
    type: connection.QueryTypes.UPSERT,
    raw: true
  };

  // Make query based on dialiect
  if (n === "mysql" || n === "mariadb" || n === "mysqli") {
    query = mysqlBulkUpsert(model, data);
  }
  else if (n === "sqlite") {
    query = sqliteBulkUpsert(model, data);
  }
  else if (n === "pgsql" || n === "postgres") {
    query = pgsqlBulkUpsert(model, data);
    options.type = connection.QueryTypes.INSERT;
  }

  // Run query
  if (_.isObject(query)) {
    options.replacements = query.replacements;
    runQuery(connection, query.query, options, done);
  }
  else if (query) {
    runQuery(connection, query, options, done);
  }
  else {
    done(new Error("Could not create query for bulkUpsert."));
  }
}
512638
def update(self, table, value, where, join=None, commit=True):
        

        value_q, _value_args = self._value_parser(value, columnname=True)

        where_q, _where_args = self._where_parser(where)

        _sql = ''.join(['UPDATE ', self._tablename_parser(table)['formatted_tablename'],
                        self._join_parser(join),
                        ' SET ', value_q, where_q, ';'])
        _args = _value_args + _where_args

        if self.debug:
            return self.cur.mogrify(_sql, _args)

        result = self.cur.execute(_sql, _args)
        if commit:
            self.commit()
        return result
1143658
def applyKeyMapping(self):
		
		assignments = ", ".join("%s = (SELECT new FROM _idmap_ WHERE old == %s)" % (colname, colname) for coltype, colname in zip(self.dbcolumntypes, self.dbcolumnnames) if coltype in ligolwtypes.IDTypes and (self.next_id is None or colname != self.next_id.column_name))
		if assignments:
			# SQLite documentation says ROWID is monotonically
			# increasing starting at 1 for the first row unless
			# it ever wraps around, then it is randomly
			# assigned.  ROWID is a 64 bit integer, so the only
			# way it will wrap is if somebody sets it to a very
			# high number manually.  This library does not do
			# that, so I don't bother checking.
			self.cursor.execute("UPDATE %s SET %s WHERE ROWID > %d" % (self.Name, assignments, self.last_maxrowid))
			self.last_maxrowid = self.maxrowid() or 0
889537
def namedb_insert_prepare( cur, record, table_name ):
    

    namedb_assert_fields_match( cur, record, table_name )
     
    columns = record.keys()
    columns.sort()

    values = []
    for c in columns:
        if record[c] == False:
            values.append(0)
        elif record[c] == True:
            values.append(1)
        else:
            values.append(record[c])
    
    values = tuple(values)

    field_placeholders = ",".join( ["?"] * len(columns) )

    query = "INSERT INTO %s (%s) VALUES (%s);" % (table_name, ",".join(columns), field_placeholders)
    log.debug(namedb_format_query(query, values))

    return (query, values)
731091
def _sql_expand_insert(self, spec, key_prefix = '', col_prefix = ''):
        
        col = list(spec)
        sql = '('
        sql += ', '.join(col_prefix + key for key in col)
        sql += ') VALUES ('
        sql += ', '.join('%(' + key_prefix + key + ')s' for key in col)
        sql += ')'
        params = {}
        for key in spec:
            params[key_prefix + key] = spec[key]

        return sql, params
72954
def append_column(table, col_name, default_value=None):
    '''
    
    '''
    table[0].append(col_name.strip())
    for row in table[1:]:
        row.append(default_value)
604114
function sqliteBulkUpsert(model, data) {
  var queries = ["BEGIN"];
  var values = [];

  // Transform data
  _.each(data, function(d) {
    var query = "INSERT OR REPLACE INTO " + model.tableName + " ([[COLUMNS]]) VALUES ([[VALUES]])";
    query = query.replace("[[COLUMNS]]", _.keys(d).join(", "));

    // Put in ?'s
    query = query.replace("[[VALUES]]", _.map(d, function(v) {
      return "?";
    }).join(", "));

    // Get values
    values = values.concat(_.values(d));
    queries.push(query);
  });

  // Create final output and format
  queries.push("COMMIT");
  return {
    query: queries.join("; "),
    replacements: values
  };
}
308361
function update (table, columns, values) {
    if (this.typecheck) t.typecheck('update', [table, columns, values]);
    if ('string'===typeof columns) {
        columns = [columns];
        values = [values];
    }
    var result = [];
    for (var i=0; i < columns.length; i++) {
        var value = ('string'===typeof values[i]
            // X\'blob\' \'\\xblob\'
            && values[i].indexOf("X\'") != 0 && values[i].indexOf("\'\\x") != 0)
            ? this.string(values[i]) : values[i];
        result.push(columns[i]+'='+value);
    }
    return ['update',table,'set',result.join()].join(' ');
}
831594
def create_or_replace_primary_key(self,
                                      table: str,
                                      fieldnames: Sequence[str]) -> int:
        """"""
        # *** create_or_replace_primary_key: Uses code specific to MySQL
        sql = """
            SELECT COUNT(*)
            FROM information_schema.table_constraints
            WHERE table_name=?
            AND table_schema={}
            AND constraint_name='PRIMARY'
        """.format(self.get_current_schema_expr())
        # http://forums.mysql.com/read.php?10,114742,114748#msg-114748
        row = self.fetchone(sql, table)
        has_pk_already = True if row[0] >= 1 else False
        drop_pk_if_exists = " DROP PRIMARY KEY," if has_pk_already else ""
        fieldlist = ",".join([self.delimit(f) for f in fieldnames])
        sql = ("ALTER TABLE " + self.delimit(table) +
               drop_pk_if_exists +
               " ADD PRIMARY KEY(" + fieldlist + ")")
        # http://stackoverflow.com/questions/8859353
        return self.db_exec(sql)
1179145
def convert_sqlite_to_mysql(
            self):
        
        from fundamentals.renderer import list_of_dictionaries
        from fundamentals.mysql import directory_script_runner
        self.log.debug('starting the ``convert_sqlite_to_mysql`` method')

        con = lite.connect(self.pathToSqlite)
        con.row_factory = lite.Row
        cur = con.cursor()

        # GET ALL TABLE NAMES
        cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cur.fetchall()

        createStatements = []
        inserts = []
        for table in tables:
            table = table['name']
            if table == "sqlite_sequence":
                continue

            # CREATE TABLE collection_books (folder_id, fingerprint, primary key(folder_id, fingerprint));
            # GENEREATE THE MYSQL CREATE STATEMENTS FOR EACH TABLE
            cur.execute(
                "SELECT sql FROM sqlite_master WHERE name = '%(table)s';" % locals())
            createStatement = cur.fetchone()
            createStatement = createStatement[0].replace('"', '`') + ";"
            if "DEFAULT" not in createStatement:
                if "primary key(" in createStatement:
                    tmp = createStatement.split("primary key(")
                    tmp[0] = tmp[0].replace(
                        ",", " varchar(150) DEFAULT NULL,")
                    createStatement = ("primary key(").join(tmp)
                if "primary key," in createStatement:
                    tmp = createStatement.split("primary key,")
                    tmp[1] = tmp[1].replace(
                        ",", " varchar(150) DEFAULT NULL,")
                    tmp[1] = tmp[1].replace(
                        ");", " varchar(150) DEFAULT NULL);")
                    createStatement = ("primary key,").join(tmp)
            createStatement = createStatement.replace(
                "INTEGER PRIMARY KEY", "INTEGER AUTO_INCREMENT PRIMARY KEY")
            createStatement = createStatement.replace(
                "AUTOINCREMENT", "AUTO_INCREMENT")
            createStatement = createStatement.replace(
                "DEFAULT 't'", "DEFAULT '1'")
            createStatement = createStatement.replace(
                "DEFAULT 'f'", "DEFAULT '0'")
            createStatement = createStatement.replace(",'t'", ",'1'")
            createStatement = createStatement.replace(",'f'", ",'0'")
            if "CREATE TABLE `" in createStatement:
                createStatement = createStatement.replace(
                    "CREATE TABLE `", "CREATE TABLE IF NOT EXISTS `" + self.tablePrefix)
            else:
                createStatement = createStatement.replace(
                    "CREATE TABLE ", "CREATE TABLE IF NOT EXISTS " + self.tablePrefix)
            if ", primary key(" in createStatement:
                createStatement = createStatement.replace(", primary key(", """,
`dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,
`dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP,
`updated` tinyint(4) DEFAULT '0',
primary key(""")
            else:
                createStatement = createStatement.replace(");", """,
    `dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,
    `dateLastModified` datetime DEFAULT CURRENT_TIMESTAMP,
    `updated` tinyint(4) DEFAULT '0');
                """)
            createStatement = createStatement.replace(
                " text primary key", " varchar(100) primary key")
            createStatement = createStatement.replace(
                "`EntryText` TEXT NOT NULL,", "`EntryText` TEXT,")
            createStatement = createStatement.replace(
                "`SelectionText` TEXT NOT NULL", "`SelectionText` TEXT")
            createStatement = createStatement.replace(
                "`Filename` INTEGER NOT NULL,", "`Filename` TEXT NOT NULL,")
            createStatement = createStatement.replace(
                "`SessionPartUUID` TEXT NOT NULL UNIQUE,", "`SessionPartUUID` VARCHAR(100) NOT NULL UNIQUE,")
            createStatement = createStatement.replace(
                "`Name` TEXT PRIMARY KEY NOT NULL", "`Name` VARCHAR(100) PRIMARY KEY NOT NULL")
            createStatement = createStatement.replace(
                " VARCHAR ", " VARCHAR(100) ")
            createStatement = createStatement.replace(
                " VARCHAR,", " VARCHAR(100),")

            # GRAB THE DATA TO ADD TO THE MYSQL DATABASE TABLES
            cur.execute(
                "SELECT * from '%(table)s';" % locals())
            rows = cur.fetchall()

            allRows = []
            for row in rows:
                allRows.append(dict(row))

            # RECURSIVELY CREATE MISSING DIRECTORIES
            if not os.path.exists("/tmp/headjack/"):
                os.makedirs("/tmp/headjack/")

            writequery(
                log=self.log,
                sqlQuery=createStatement,
                dbConn=self.dbConn,
            )

            from fundamentals.mysql import insert_list_of_dictionaries_into_database_tables
            # USE dbSettings TO ACTIVATE MULTIPROCESSING
            insert_list_of_dictionaries_into_database_tables(
                dbConn=self.dbConn,
                log=self.log,
                dictList=allRows,
                dbTableName=self.tablePrefix + table,
                uniqueKeyList=[],
                dateModified=True,
                dateCreated=True,
                batchSize=10000,
                replace=True,
                dbSettings=self.settings["database settings"]
            )

            # # DUMP THE DATA INTO A MYSQL DATABASE
            # dataSet = list_of_dictionaries(
            #     log=self.log,
            #     listOfDictionaries=allRows
            # )
            # originalList = dataSet.list
            # now = datetime.now()
            # now = now.strftime("%Y%m%dt%H%M%S%f.sql")
            # mysqlData = dataSet.mysql(
            # tableName=self.tablePrefix + table, filepath="/tmp/headjack/" +
            # now, createStatement=createStatement)

            # directory_script_runner(
            #     log=self.log,
            #     pathToScriptDirectory="/tmp/headjack/",
            #     databaseName=self.settings["database settings"]["db"],
            #     loginPath=self.settings["database settings"]["loginPath"],
            #     successRule="delete",
            #     failureRule="failed"
            # )

        con.close()

        self.log.debug('completed the ``convert_sqlite_to_mysql`` method')
        return None
546256
def insert(self, table, value, ignore=False, commit=True):
        
        value_q, _args = self._value_parser(value, columnname=False)
        _sql = ''.join(['INSERT', ' IGNORE' if ignore else '', ' INTO ', self._backtick(table),
                        ' (', self._backtick_columns(value), ') VALUES (', value_q, ');'])

        if self.debug:
            return self.cur.mogrify(_sql, _args)

        self.cur.execute(_sql, _args)
        if commit:
            self.conn.commit()
        return self.cur.lastrowid