Python pandas.read_sql_query() Examples

def regionFilterGC(conn, minprop, maxprop):
	cur = conn.cursor()
	sql = '''
	UPDATE
		regions
	SET
		pass=0
	WHERE
		(gc > ?) OR (gc < ?)
	'''
	stuff = [maxprop, minprop]
	#print(pd.read_sql_query(sql, conn))
	cur.execute(sql, stuff)
	conn.commit()

#Function to remove targets NOT included in list 

def extract(self, query=None):
        with self.engine.connect() as connection:
            if query is not None:
                return pd.read_sql_query(query, connection)

            elif 'query' in self.options['data']:
                return pd.read_sql_query(self.options['data']['query'], connection)

            else:  # table
                return pd.read_sql_table(self.options['data']['table'], connection,
                                         schema=self.options['data']['schema']) 

def fetchConflictTRs_NoMult(conn):
	cur = conn.cursor()

	#Function call to build conflicts table
	try:
		num = initializeConflicts(conn)
	except RuntimeError as err:
		print(err.args)

	#Query to UPDATE conflicts with each locus as a conflict_block
	update = '''
		UPDATE
			conflicts
		SET
			conflict_block = (SELECT r.locid FROM regions AS r WHERE r.regid = conflicts.regid)
		WHERE
			EXISTS(SELECT * FROM regions AS r WHERE r.regid = conflicts.regid)
	'''
	cur.execute(update)
	conn.commit()

	#DEBUG print
	#print(pd.read_sql_query("SELECT * FROM conflicts", conn))

#Function to fetch number of rows in conflicts table 

def append_new_table_mapd(connection, table_name, csv_file, ts_cols, ts_format, ts_units, int_cols):
    df = pd.read_csv(csv_file)
    format_int_col(df, int_cols)
    if ts_format == 'None':
        format_date_cols(df, ts_cols, un=ts_units)
    elif ts_units == 'None':
        format_date_cols(df, ts_cols, tf=ts_format)

    if df.empty:
        print ("no results to upload")
    else:
        #load the new rows
        df.reset_index(drop=True, inplace=True)
        print ("loading table " + table_name)
        connection.load_table(table_name, df, preserve_index=False, create=False) #append the data into the exisiting table in OmniSci

        #dedupe all of the rows
        command = "select CAST(view_timestamp as DATE) view_timestamp, MAX(view_unique) as view_unique, repo from oss_git_views where repo = 'mapd-core' group by view_timestamp, repo order by view_timestamp ASC"
        df_deduped = pd.read_sql_query(command, connection)
        print ("reloading table " + table_name)
        print (df_deduped)
        drop_table_mapd(connection, table_name)
        connection.load_table(table_name, df_deduped, preserve_index=False, create=True) #append the data into the exisiting table in OmniSci

# MAIN 

def lengthFilterTR(conn, maxlen, minlen):
	cur = conn.cursor()

	sql = '''
	UPDATE
		regions
	SET
		pass=0
	WHERE
		length > ? OR length < ?
	'''
	stuff = [int(maxlen), int(minlen)]
	#print(pd.read_sql_query(sql, conn))
	cur.execute(sql, stuff)
	conn.commit()

#Functon to filter targets by --vmax_r 

def regionFilterGFF_Alias(conn, gff_type, dist):
	cur = conn.cursor()

	if getNumGFF(conn) > 0:
		if getNumPassedGFF(conn) > 0:
			df = pd.DataFrame() #empty pandas DF
			#If get GFF by type:
			if gff_type == "all":
				sql = """
					SELECT regid, regions.start, regions.stop, regions.pass,
						gffid, gff.start AS gff_start, gff.stop AS gff_stop
					FROM
						regions INNER JOIN gff ON regions.locid = gff.locid
					WHERE
						regions.pass = 1 AND gff.pass = 1 AND gff.alias != "NULL"
				"""
				df = pd.read_sql_query(sql, conn)
			else:
				#Query database to get targets with passing GFFs
				sql = """
					SELECT regid, regions.start, regions.stop, regions.pass,
						gffid, gff.start AS gff_start, gff.stop AS gff_stop
					FROM
						regions INNER JOIN gff ON regions.locid = gff.locid
					WHERE
						regions.pass = 1 AND gff.pass = 1 AND gff.alias = ?
				"""
				df = pd.read_sql_query(sql, conn, params=(gff_type,))

			#Get list of passed targets, pass list to FAIL all non-whitelisted targets
			whitelist = parseJoinGFFTable(df, dist)
			removeRegionsByWhitelist(conn, whitelist)

		else:
			cur.execute("UPDATE regions SET pass = 0")
			print("WARNING: No GFF records passed quality control. Because you chose to filter target regions on proximity to GFF records, no targets will be retained.")
	else:
		print("WARNING: No GFF records present in database. Skipping target region filtering on proximity to GFF records.")
	conn.commit()

#Function to parse an INNER JOIN regions/gff table for overlapping fragments, and return whitelist 

def getRegionWeightsByList(conn, fetch):
	cur = conn.cursor()
	if len(fetch) <= 0:
		return(None)
	df = pd.DataFrame({"regid" : fetch})
	df.to_sql('ttt', conn, if_exists='replace')

	sql = """
	SELECT
		regions.regid,
		(vars+vars_flank) AS vars,
		(bad + gap + bad_flank + gap_flank) as sum_bad
	FROM
		regions
	WHERE
		regions.regid IN (SELECT regid FROM ttt)
	"""
	new_df = pd.read_sql_query(sql ,conn)

	cur.execute("DROP TABLE IF EXISTS ttt")
	conn.commit()
	return(new_df)

#Function to return a pandas DF of regions, vars, and 'bad bases' of ONLY regids in a given list 

def getRegionWeightsByList_VAR(conn, fetch):
	cur = conn.cursor()
	if len(fetch) <= 0:
		return(None)
	df = pd.DataFrame({"regid" : fetch})
	df.to_sql('ttt', conn, if_exists='replace')

	sql = """
	SELECT
		regions.regid,
		(vars+vars_flank) AS weight
	FROM
		regions
	WHERE
		regions.regid IN (SELECT regid FROM ttt)
	"""
	new_df = pd.read_sql_query(sql ,conn)

	cur.execute("DROP TABLE IF EXISTS ttt")
	conn.commit()
	return(new_df)

#Function to update REGIONS table based on existing Gap attribute 

def test_categorical(self):
        # GH8624
        # test that categorical gets written correctly as dense column
        df = DataFrame(
            {
                "person_id": [1, 2, 3],
                "person_name": ["John P. Doe", "Jane Dove", "John P. Doe"],
            }
        )
        df2 = df.copy()
        df2["person_name"] = df2["person_name"].astype("category")

        df2.to_sql("test_categorical", self.conn, index=False)
        res = sql.read_sql_query("SELECT * FROM test_categorical", self.conn)

        tm.assert_frame_equal(res, df) 

def test_datetime_NaT(self):
        df = DataFrame(
            {"A": date_range("2013-01-01 09:00:00", periods=3), "B": np.arange(3.0)}
        )
        df.loc[1, "A"] = np.nan
        df.to_sql("test_datetime", self.conn, index=False)

        # with read_table -> type information from schema used
        result = sql.read_sql_table("test_datetime", self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query("SELECT * FROM test_datetime", self.conn)
        if self.flavor == "sqlite":
            assert isinstance(result.loc[0, "A"], str)
            result["A"] = to_datetime(result["A"], errors="coerce")
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 

def test_datetime_time(self):
        # test support for datetime.time
        df = DataFrame([time(9, 0, 0), time(9, 1, 30)], columns=["a"])
        df.to_sql("test_time", self.conn, index=False)
        res = read_sql_table("test_time", self.conn)
        tm.assert_frame_equal(res, df)

        # GH8341
        # first, use the fallback to have the sqlite adapter put in place
        sqlite_conn = TestSQLiteFallback.connect()
        sql.to_sql(df, "test_time2", sqlite_conn, index=False)
        res = sql.read_sql_query("SELECT * FROM test_time2", sqlite_conn)
        ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
        tm.assert_frame_equal(ref, res)  # check if adapter is in place
        # then test if sqlalchemy is unaffected by the sqlite adapter
        sql.to_sql(df, "test_time3", self.conn, index=False)
        if self.flavor == "sqlite":
            res = sql.read_sql_query("SELECT * FROM test_time3", self.conn)
            ref = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
            tm.assert_frame_equal(ref, res)
        res = sql.read_sql_table("test_time3", self.conn)
        tm.assert_frame_equal(df, res) 

def test_connectable_issue_example(self):
        # This tests the example raised in issue
        # https://github.com/pandas-dev/pandas/issues/10104

        def foo(connection):
            query = "SELECT test_foo_data FROM test_foo_data"
            return sql.read_sql_query(query, con=connection)

        def bar(connection, data):
            data.to_sql(name="test_foo_data", con=connection, if_exists="append")

        def main(connectable):
            with connectable.connect() as conn:
                with conn.begin():
                    foo_data = conn.run_callable(foo)
                    conn.run_callable(bar, foo_data)

        DataFrame({"test_foo_data": [0, 1, 2]}).to_sql("test_foo_data", self.conn)
        main(self.conn) 

def test_temporary_table(self):
        test_data = "Hello, World!"
        expected = DataFrame({"spam": [test_data]})
        Base = declarative.declarative_base()

        class Temporary(Base):
            __tablename__ = "temp_test"
            __table_args__ = {"prefixes": ["TEMPORARY"]}
            id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
            spam = sqlalchemy.Column(sqlalchemy.Unicode(30), nullable=False)

        Session = sa_session.sessionmaker(bind=self.conn)
        session = Session()
        with session.transaction:
            conn = session.connection()
            Temporary.__table__.create(conn)
            session.add(Temporary(spam=test_data))
            session.flush()
            df = sql.read_sql_query(sql=sqlalchemy.select([Temporary.spam]), con=conn)

        tm.assert_frame_equal(df, expected) 

def test_chunksize_read_type(self):
        frame = tm.makeTimeDataFrame()
        frame.index.name = "index"
        drop_sql = "DROP TABLE IF EXISTS test"
        cur = self.conn.cursor()
        cur.execute(drop_sql)
        sql.to_sql(frame, name="test", con=self.conn)
        query = "select * from test"
        chunksize = 5
        chunk_gen = pd.read_sql_query(
            sql=query, con=self.conn, chunksize=chunksize, index_col="index"
        )
        chunk_df = next(chunk_gen)
        tm.assert_frame_equal(frame[:chunksize], chunk_df) 

def __QS_prepareRawData__(self, factor_names, ids, dts, args={}):
        EndDate = dts[-1].date()
        AnnDateField = self._DBTableName+"."+self._FactorInfo.loc[self._AnnDateField, "DBFieldName"]
        ReportDateField = self._DBTableName+"."+self._FactorInfo.loc[self._DateField, "DBFieldName"]
        if self._AdjustTypeField is not None: AdjustTypeField = self._DBTableName+"."+self._FactorInfo.loc[self._AdjustTypeField, "DBFieldName"]
        # 形成 SQL 语句, ID, 公告日期, 报告期, 报表类型, 财务因子
        SQLStr = "SELECT "+self._getIDField()+" AS ID, "
        SQLStr += AnnDateField+" AS AnnDate, "
        SQLStr += ReportDateField+" AS ReportDate, "
        FieldSQLStr, SETableJoinStr = self._genFieldSQLStr(factor_names)
        SQLStr += FieldSQLStr+" "
        SQLStr += self._genFromSQLStr(setable_join_str=SETableJoinStr)+" "
        SQLStr += "WHERE ("+genSQLInCondition(self._MainTableName+"."+self._MainTableID, deSuffixID(ids), is_str=self._IDFieldIsStr, max_num=1000)+") "
        SQLStr += "AND "+AnnDateField+"<='"+EndDate.strftime("%Y-%m-%d")+"' "
        SQLStr += "AND "+ReportDateField+" IS NOT NULL "
        SQLStr += self._genConditionSQLStr(args=args)+" "
        if pd.notnull(self._MainTableCondition): SQLStr += "AND "+self._MainTableCondition+" "
        SQLStr += "ORDER BY ID, "+AnnDateField+", "
        SQLStr += ReportDateField
        if self._AdjustTypeField is not None: SQLStr += ", "+AdjustTypeField+" DESC"
        RawData = pd.read_sql_query(SQLStr, self._FactorDB.Connection)
        RawData.columns = ["ID", "AnnDate", "ReportDate"]+factor_names
        RawData = self._adjustRawDataByRelatedField(RawData, factor_names)
        return RawData 

def test_datetime(self):
        df = DataFrame({'A': date_range('2013-01-01 09:00:00', periods=3),
                        'B': np.arange(3.0)})
        df.to_sql('test_datetime', self.conn)

        # with read_table -> type information from schema used
        result = sql.read_sql_table('test_datetime', self.conn)
        result = result.drop('index', axis=1)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query('SELECT * FROM test_datetime', self.conn)
        result = result.drop('index', axis=1)
        if self.flavor == 'sqlite':
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'])
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 

def test_datetime_NaT(self):
        df = DataFrame({'A': date_range('2013-01-01 09:00:00', periods=3),
                        'B': np.arange(3.0)})
        df.loc[1, 'A'] = np.nan
        df.to_sql('test_datetime', self.conn, index=False)

        # with read_table -> type information from schema used
        result = sql.read_sql_table('test_datetime', self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query('SELECT * FROM test_datetime', self.conn)
        if self.flavor == 'sqlite':
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'], errors='coerce')
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 

def get_table_mapd(connection, table_name):
    command = "SELECT * FROM %s" % (table_name) 
    print ("getting table " + table_name + ' with ' + command)
    df = pd.read_sql_query(command, connection)
    return df

# Load CSV to dataframe and then copy to table using pymapd 

def get_table_mapd(connection, table_name):
    command = "SELECT * FROM %s" % (table_name) 
    print ("getting table " + table_name + ' with ' + command)
    df = pd.read_sql_query(command, connection)
    return df 

def query_object(self, starnames, key='*'):
        """
        Get information about the given star.

        Parameters:
        ===========
        starnames:    string, or iterable of strings
                      The name(s) of the star.

        key:          string, default='*' (return everything)
                      What data do you want? Can be anything that sql will take

        Returns:
        ========
        A pandas DataFrame with the given information for each star
        """

        if isinstance(starnames, str):
            starnames = [starnames,]
        starnames = ["'{}'".format(n) for n in starnames]

        name_list = '(' + ', '.join(starnames) + ')'
        sql_query = "SELECT {} FROM star WHERE name IN {}".format(key, name_list)
        print(sql_query)
        df = pd.read_sql_query(sql_query, self.db_con)

        return df 

def getPassedLoci(conn):
	return(pd.read_sql_query("""SELECT id, consensus, chrom FROM loci WHERE pass=1""", conn))

#Function returns a Pandas DataFrame of passing target regions 

def getPassedTRs(conn):
	return(pd.read_sql_query("""SELECT regid, sequence FROM regions WHERE pass=1""", conn))

#Function to parse fetchone() results (internal) 

def getPrintBaits(conn):
	sql = '''
		SELECT
			locid, baits.regid, baitid, baits.sequence
		FROM
		 	baits INNER JOIN regions ON baits.regid = regions.regid
		WHERE
			baits.pass=1
	'''
	return(pd.read_sql_query(sql, conn))

#Function to return loci table 

def getLoci(conn):
	return(pd.read_sql_query("""SELECT * FROM loci """, conn))

#Function to return regions table 

def getRegions(conn):
	return(pd.read_sql_query("""SELECT * FROM regions """, conn))

#Function to return baits table 

def getGFF(conn):
	return(pd.read_sql_query("""SELECT * FROM gff """, conn))

#Function to return baits table 

def getPassedBaits(conn):
	return(pd.read_sql_query("""SELECT baitid, sequence FROM baits WHERE pass=1""", conn)) 

def updateLociMask(conn, newMask):
	cur = conn.cursor()

	#convert to data frame
	df = pd.DataFrame(newMask, columns=("id", "mask"))

	#create temporary table
	df.to_sql('m', conn, if_exists='replace')
	#print(pd.read_sql_query("SELECT * FROM loci", conn))

	#Hacky way to do it, but SQlite doesn't support FROM clause in UPDATEs...
	sql_update = '''
		UPDATE
			loci
		SET
			mask = (SELECT m.mask FROM m WHERE m.id = loci.id)
		WHERE
			EXISTS(SELECT * FROM m WHERE m.id = loci.id)
	'''
	cur.execute(sql_update)

	#Clear up the temp table t
	cur.execute("DROP TABLE IF EXISTS m")
	conn.commit()
	#print(pd.read_sql_query("SELECT * FROM loci", conn))

#Function to build conflicts table when --R is false 

def regionSelect_MINBAD(conn):
	cur = conn.cursor()

	#Fetch number of entries in conflict tables
	rows = getConflictNumRows(conn)

	#Make sure there is some data to work on
	if rows <= 0 :
		raise ValueError("There are no rows in <conflicts>!")

	#-Use RANDOM to resolve any remaining NULLs.
	#print("DIST IS ",dist)
	#Query conflicts temp table to make a pandas dataframe for parsing
	#This version includes records which have a zero from the COUNT aggregate function by
	#outer joining the 'counts' table by the original table.
	#Probably a better way to go about it but i'm tired of dicking with it...
	sql = '''
		SELECT
			c.regid,
			conflict_block,
			choose,
			(gap + bad + bad_flank + gap_flank) as counts
		FROM
			conflicts AS c INNER JOIN regions AS r USING (regid)
		WHERE
			c.choose="NULL"
	'''

	df = pd.read_sql_query(sql, conn)

	#Split df into locid groups (retains INDEX of each entry, but in separate dfs)
	#Loop through groups and select highest
	try:
		df = parseCountsMin(df)
	except:
		raise

	#Push modified DF to SQL as temp table
	updateChosenFromPandas(conn, df)

#Function for resolving conflict blocks by minimizing all variable bases in flanking region 

def regionSelect_MINVAR_TR(conn):
	cur = conn.cursor()

	#Fetch number of entries in conflict tables
	rows = getConflictNumRows(conn)
	#Make sure there is some data to work on
	if rows <= 0 :
		raise ValueError("There are no rows in <conflicts>!")

	#Query conflicts temp table to make a pandas dataframe for parsing
	sql = '''
		SELECT
			c.regid,
			conflict_block,
			choose,
			(vars + flank_vars) AS counts
		FROM
			conflicts AS c INNER JOIN regions AS r USING (regid)
		WHERE
			c.choose="NULL"
	'''

	df = pd.read_sql_query(sql, conn)
	#Split df into locid groups (retains INDEX of each entry, but in separate dfs)
	try:
		df = parseCountsMin(df)
	except:
		raise

	#Push modified DF to SQL as temp table
	updateChosenFromPandas(conn, df)

#Function for resolving conflict blocks by minimizing number of flanking SNPs 

def regionSelect_MINSNP(conn):
	cur = conn.cursor()

	#Fetch number of entries in conflict tables
	rows = getConflictNumRows(conn)

	#Make sure there is some data to work on
	if rows <= 0 :
		raise ValueError("There are no rows in <conflicts>!")

	#-Use RANDOM to resolve any remaining NULLs.
	#print("DIST IS ",dist)
	#Query conflicts temp table to make a pandas dataframe for parsing
	sql = '''
		SELECT
			c.regid,
			conflict_block,
			choose,
			(vars + vars_flank) as counts
		FROM
			conflicts AS c INNER JOIN regions AS r USING (regid)
		WHERE
			c.choose="NULL"
	'''

	#printFlankingSNPs_conflicts(conn, dist)
	#printFlankingSNPCounts_conflicts(conn, dist)
	df = pd.read_sql_query(sql, conn)

	#Split df into locid groups (retains INDEX of each entry, but in separate dfs)
	#Loop through groups and select highest
	try:
		df = parseCountsMin(df)
	except:
		raise

	#Push modified DF to SQL as temp table
	updateChosenFromPandas(conn, df)

#Function to push resolved TR conflicts to the regions table 

def pushResolvedConflicts(conn):

	#Check that all conflicts are resolved
	cur = conn.cursor()
	unres = pd.read_sql_query("SELECT COUNT(*) FROM conflicts WHERE choose='NULL'", conn)
	rows = unres.shape[0]
	if rows <= 0:
		print("Unresolved conflicts:")
		print(unres)
		raise RuntimeError("Error: There are still unresolved conflicts")
	else:
		#Hacky way to do it, but SQlite doesn't support FROM clause in UPDATEs...
		sql_update = '''
			UPDATE
				regions
			SET
				pass = (SELECT c.choose FROM conflicts c WHERE c.regid = regions.regid)
			WHERE
				regions.regid in (SELECT c.regid FROM conflicts c WHERE c.regid = regions.regid)
			AND
				pass = 1
		'''
		cur.execute(sql_update)

		#Clear up the temp table conflicts
		cur.execute("DROP TABLE IF EXISTS conflicts")
		conn.commit()


#Functon to filter targets by length 

def regionFilterMask(conn, maxprop):
	cur = conn.cursor()
	sql = '''
	UPDATE
		regions
	SET
		pass=0
	WHERE
		(mask > ?)
	'''
	#print(pd.read_sql_query(sql, conn))
	cur.execute(sql, (maxprop,))
	conn.commit() 

def baitFilterMask(conn, maxprop):
	cur = conn.cursor()
	sql = '''
	UPDATE
		baits
	SET
		pass=0
	WHERE
		(mask > ?)
	'''
	#print(pd.read_sql_query(sql, conn))
	cur.execute(sql, (maxprop,))
	conn.commit() 

def regionFilterGFF(conn, gff_type, dist):
	cur = conn.cursor()

	if getNumGFF(conn) > 0:
		if getNumPassedGFF(conn) > 0:
			df = pd.DataFrame() #empty pandas DF
			#If get GFF by type:
			if gff_type == "all":
				sql = """
					SELECT regid, regions.start, regions.stop, regions.pass,
						gffid, gff.start AS gff_start, gff.stop AS gff_stop
					FROM
						regions INNER JOIN gff ON regions.locid = gff.locid
					WHERE
						regions.pass = 1 AND gff.pass = 1
				"""
				df = pd.read_sql_query(sql, conn)
			else:
				#Query database to get targets with passing GFFs
				sql = """
					SELECT regid, regions.start, regions.stop, regions.pass,
						gffid, gff.start AS gff_start, gff.stop AS gff_stop
					FROM
						regions INNER JOIN gff ON regions.locid = gff.locid
					WHERE
						regions.pass = 1 AND gff.pass = 1 AND gff.type = ?
				"""
				df = pd.read_sql_query(sql, conn, params=(gff_type,))

			whitelist = parseJoinGFFTable(df, dist)
			removeRegionsByWhitelist(conn, whitelist)

			"""
			1. Fetch INNER JOINED db matching criterion
			2. Parse overlaps in Python (see function above)
			3. Return list of ones to keep.
			4. For UPDATE- Set pass to 0 if: pass NOT 1 in returned list, OR if already failed
				This should also fail the case where NO GFF RECORDS TO JOIN or gff record was failed
			"""
		else:
			cur.execute("UPDATE regions SET pass = 0")
			print("WARNING: No GFF records passed quality control. Because you chose to filter target regions on proximity to GFF records, no targets will be retained.")
	else:
		print("WARNING: No GFF records present in database. Skipping target region filtering on proximity to GFF records.")
	conn.commit()

#Function to filter targets by proximity or overlap with GFF records 

def getRegionWeightsByList_BAD(conn, fetch):
	cur = conn.cursor()
	if len(fetch) <= 0:
		return(None)
	df = pd.DataFrame({"regid" : fetch})
	df.to_sql('ttt', conn, if_exists='replace')

	sql = """
	SELECT
		regions.regid,
		(bad + gap + bad_flank + gap_flank) AS weight
	FROM
		regions
	WHERE
		regions.regid IN (SELECT regid FROM ttt)
	"""
	new_df = pd.read_sql_query(sql ,conn)

	cur.execute("DROP TABLE IF EXISTS ttt")
	conn.commit()

	#adjust weights to be: max(weight)-weight:
	max_weight = new_df["weight"].max()
	new_df["weight"] = max_weight - new_df["weight"]

	return(new_df)

#Function to return a pandas DF of regions, vars, and 'bad bases' of ONLY regids in a given list 

def QueryDB(query, conn):
    return pd.read_sql_query(query, conn) 

def read_sql_query(self, query):
        """
        検索条件を元にデータフレームを返す
        :param query: 検索条件
        :return: Dataframe
        """
        return pd.read_sql_query(sql=query, con=self.engine) 

def batter_event_by_at_bat(self, first_name, last_name, year, from_dt=DEFAULT_FROM_DT, to_dt=DEFAULT_TO_DT):
        """
        get batter at bat result
        :param first_name: batter first name
        :param last_name: batter last name
        :param year: season year
        :param from_dt: from date
        :param to_dt: to date
        :return: Dataframe
        """
        batter = self.get_player_data_one(year, first_name, last_name)
        params = self._batter_event_query_params(batter, year, from_dt, to_dt)
        return self.read_sql_query(self.QUERY_SELECT_BATTING_STATS_BY_AT_BAT.format(**params)) 

def _batter_event_query(self, first_name, last_name, year, from_dt, to_dt, event_codes):
        """
        batting result(event)
        :param first_name: batter first name
        :param last_name: batter last name
        :param year: season year
        :param from_dt: from date
        :param to_dt: to date
        :param event_codes: Event List
        :return: Dataframe
        """
        batter = self.get_player_data_one(year, first_name, last_name)
        params = self._batter_event_query_params(batter, year, from_dt, to_dt)
        params['event_codes'] = ",".join(event_codes)
        return self.read_sql_query(self.QUERY_SELECT_BATTING_STATS_BY_EVENT_CODES.format(**params)) 

def test_read_sql_iris(self):
        iris_frame = sql.read_sql_query("SELECT * FROM iris", self.conn)
        self._check_iris_loaded_frame(iris_frame) 

def test_read_sql_view(self):
        iris_frame = sql.read_sql_query("SELECT * FROM iris_view", self.conn)
        self._check_iris_loaded_frame(iris_frame) 

def test_to_sql_series(self):
        s = Series(np.arange(5, dtype="int64"), name="series")
        sql.to_sql(s, "test_series", self.conn, index=False)
        s2 = sql.read_sql_query("SELECT * FROM test_series", self.conn)
        tm.assert_frame_equal(s.to_frame(), s2) 

def test_roundtrip(self):
        sql.to_sql(self.test_frame1, "test_frame_roundtrip", con=self.conn)
        result = sql.read_sql_query("SELECT * FROM test_frame_roundtrip", con=self.conn)

        # HACK!
        result.index = self.test_frame1.index
        result.set_index("level_0", inplace=True)
        result.index.astype(int)
        result.index.name = None
        tm.assert_frame_equal(result, self.test_frame1) 

def test_roundtrip_chunksize(self):
        sql.to_sql(
            self.test_frame1,
            "test_frame_roundtrip",
            con=self.conn,
            index=False,
            chunksize=2,
        )
        result = sql.read_sql_query("SELECT * FROM test_frame_roundtrip", con=self.conn)
        tm.assert_frame_equal(result, self.test_frame1) 

def test_date_and_index(self):
        # Test case where same column appears in parse_date and index_col

        df = sql.read_sql_query(
            "SELECT * FROM types_test_data",
            self.conn,
            index_col="DateCol",
            parse_dates=["DateCol", "IntDateCol"],
        )

        assert issubclass(df.index.dtype.type, np.datetime64)
        assert issubclass(df.IntDateCol.dtype.type, np.datetime64) 

def test_timedelta(self):

        # see #6921
        df = to_timedelta(Series(["00:00:01", "00:00:03"], name="foo")).to_frame()
        with tm.assert_produces_warning(UserWarning):
            df.to_sql("test_timedelta", self.conn)
        result = sql.read_sql_query("SELECT * FROM test_timedelta", self.conn)
        tm.assert_series_equal(result["foo"], df["foo"].astype("int64")) 

def test_to_sql_index_label(self, index_name, index_label, expected):
        temp_frame = DataFrame({"col1": range(4)})
        temp_frame.index.name = index_name
        query = "SELECT * FROM test_index_label"
        sql.to_sql(temp_frame, "test_index_label", self.conn, index_label=index_label)
        frame = sql.read_sql_query(query, self.conn)
        assert frame.columns[0] == expected 

def test_multiindex_roundtrip(self):
        df = DataFrame.from_records(
            [(1, 2.1, "line1"), (2, 1.5, "line2")],
            columns=["A", "B", "C"],
            index=["A", "B"],
        )

        df.to_sql("test_multiindex_roundtrip", self.conn)
        result = sql.read_sql_query(
            "SELECT * FROM test_multiindex_roundtrip", self.conn, index_col=["A", "B"]
        )
        tm.assert_frame_equal(df, result, check_index_type=True) 

def test_escaped_table_name(self):
        # GH 13206
        df = DataFrame({"A": [0, 1, 2], "B": [0.2, np.nan, 5.6]})
        df.to_sql("d1187b08-4943-4c8d-a7f6", self.conn, index=False)

        res = sql.read_sql_query("SELECT * FROM `d1187b08-4943-4c8d-a7f6`", self.conn)

        tm.assert_frame_equal(res, df) 

def test_read_sql_delegate(self):
        iris_frame1 = sql.read_sql_query("SELECT * FROM iris", self.conn)
        iris_frame2 = sql.read_sql("SELECT * FROM iris", self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2)

        iris_frame1 = sql.read_sql_table("iris", self.conn)
        iris_frame2 = sql.read_sql("iris", self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2) 

def test_not_reflect_all_tables(self):
        # create invalid table
        qry = """CREATE TABLE invalid (x INTEGER, y UNKNOWN);"""
        self.conn.execute(qry)
        qry = """CREATE TABLE other_table (x INTEGER, y INTEGER);"""
        self.conn.execute(qry)

        with warnings.catch_warnings(record=True) as w:
            # Cause all warnings to always be triggered.
            warnings.simplefilter("always")
            # Trigger a warning.
            sql.read_sql_table("other_table", self.conn)
            sql.read_sql_query("SELECT * FROM other_table", self.conn)
            # Verify some things
            assert len(w) == 0 

def test_database_uri_string(self):

        # Test read_sql and .to_sql method with a database URI (GH10654)
        test_frame1 = self.test_frame1
        # db_uri = 'sqlite:///:memory:' # raises
        # sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near
        # "iris": syntax error [SQL: 'iris']
        with tm.ensure_clean() as name:
            db_uri = "sqlite:///" + name
            table = "iris"
            test_frame1.to_sql(table, db_uri, if_exists="replace", index=False)
            test_frame2 = sql.read_sql(table, db_uri)
            test_frame3 = sql.read_sql_table(table, db_uri)
            query = "SELECT * FROM iris"
            test_frame4 = sql.read_sql_query(query, db_uri)
        tm.assert_frame_equal(test_frame1, test_frame2)
        tm.assert_frame_equal(test_frame1, test_frame3)
        tm.assert_frame_equal(test_frame1, test_frame4)

        # using driver that will not be installed on Travis to trigger error
        # in sqlalchemy.create_engine -> test passing of this error to user
        try:
            # the rest of this test depends on pg8000's being absent
            import pg8000  # noqa

            pytest.skip("pg8000 is installed")
        except ImportError:
            pass

        db_uri = "postgresql+pg8000://user:pass@host/dbname"
        with pytest.raises(ImportError, match="pg8000"):
            sql.read_sql("select * from table", db_uri) 

def test_read_sql_delegate(self):
        iris_frame1 = sql.read_sql_query("SELECT * FROM iris", self.conn)
        iris_frame2 = sql.read_sql("SELECT * FROM iris", self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2)

        msg = "Execution failed on sql 'iris': near \"iris\": syntax error"
        with pytest.raises(sql.DatabaseError, match=msg):
            sql.read_sql("iris", self.conn) 

def test_datetime_with_timezone_roundtrip(self):
        # GH 9086
        # Write datetimetz data to a db and read it back
        # For dbs that support timestamps with timezones, should get back UTC
        # otherwise naive data should be returned
        expected = DataFrame(
            {"A": date_range("2013-01-01 09:00:00", periods=3, tz="US/Pacific")}
        )
        expected.to_sql("test_datetime_tz", self.conn, index=False)

        if self.flavor == "postgresql":
            # SQLAlchemy "timezones" (i.e. offsets) are coerced to UTC
            expected["A"] = expected["A"].dt.tz_convert("UTC")
        else:
            # Otherwise, timestamps are returned as local, naive
            expected["A"] = expected["A"].dt.tz_localize(None)

        result = sql.read_sql_table("test_datetime_tz", self.conn)
        tm.assert_frame_equal(result, expected)

        result = sql.read_sql_query("SELECT * FROM test_datetime_tz", self.conn)
        if self.flavor == "sqlite":
            # read_sql_query does not return datetime type like read_sql_table
            assert isinstance(result.loc[0, "A"], str)
            result["A"] = to_datetime(result["A"])
        tm.assert_frame_equal(result, expected) 

def test_datetime(self):
        df = DataFrame(
            {"A": date_range("2013-01-01 09:00:00", periods=3), "B": np.arange(3.0)}
        )
        df.to_sql("test_datetime", self.conn)

        # with read_table -> type information from schema used
        result = sql.read_sql_table("test_datetime", self.conn)
        result = result.drop("index", axis=1)
        tm.assert_frame_equal(result, df)

        # with read_sql -> no type information -> sqlite has no native
        result = sql.read_sql_query("SELECT * FROM test_datetime", self.conn)
        result = result.drop("index", axis=1)
        if self.flavor == "sqlite":
            assert isinstance(result.loc[0, "A"], str)
            result["A"] = to_datetime(result["A"])
            tm.assert_frame_equal(result, df)
        else:
            tm.assert_frame_equal(result, df) 

def test_nan_fullcolumn(self):
        # full NaN column (numeric float column)
        df = DataFrame({"A": [0, 1, 2], "B": [np.nan, np.nan, np.nan]})
        df.to_sql("test_nan", self.conn, index=False)

        # with read_table
        result = sql.read_sql_table("test_nan", self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql -> not type info from table -> stays None
        df["B"] = df["B"].astype("object")
        df["B"] = None
        result = sql.read_sql_query("SELECT * FROM test_nan", self.conn)
        tm.assert_frame_equal(result, df) 

def test_nan_string(self):
        # NaNs in string column
        df = DataFrame({"A": [0, 1, 2], "B": ["a", "b", np.nan]})
        df.to_sql("test_nan", self.conn, index=False)

        # NaNs are coming back as None
        df.loc[2, "B"] = None

        # with read_table
        result = sql.read_sql_table("test_nan", self.conn)
        tm.assert_frame_equal(result, df)

        # with read_sql
        result = sql.read_sql_query("SELECT * FROM test_nan", self.conn)
        tm.assert_frame_equal(result, df) 

def test_read_procedure(self):
        import pymysql

        # see GH7324. Although it is more an api test, it is added to the
        # mysql tests as sqlite does not have stored procedures
        df = DataFrame({"a": [1, 2, 3], "b": [0.1, 0.2, 0.3]})
        df.to_sql("test_procedure", self.conn, index=False)

        proc = """DROP PROCEDURE IF EXISTS get_testdb;

        CREATE PROCEDURE get_testdb ()

        BEGIN
            SELECT * FROM test_procedure;
        END"""

        connection = self.conn.connect()
        trans = connection.begin()
        try:
            r1 = connection.execute(proc)  # noqa
            trans.commit()
        except pymysql.Error:
            trans.rollback()
            raise

        res1 = sql.read_sql_query("CALL get_testdb();", self.conn)
        tm.assert_frame_equal(df, res1)

        # test delegation to read_sql_query
        res2 = sql.read_sql("CALL get_testdb();", self.conn)
        tm.assert_frame_equal(df, res2) 

def test_datetime_time(self):
        # test support for datetime.time, GH #8341
        df = DataFrame([time(9, 0, 0), time(9, 1, 30)], columns=["a"])
        df.to_sql("test_time", self.conn, index=False)
        res = read_sql_query("SELECT * FROM test_time", self.conn)
        if self.flavor == "sqlite":
            # comes back as strings
            expected = df.applymap(lambda _: _.strftime("%H:%M:%S.%f"))
            tm.assert_frame_equal(res, expected) 

def _get_index_columns(self, tbl_name):
        ixs = sql.read_sql_query(
            "SELECT * FROM sqlite_master WHERE type = 'index' "
            + "AND tbl_name = '{tbl_name}'".format(tbl_name=tbl_name),
            self.conn,
        )
        ix_cols = []
        for ix_name in ixs.name:
            ix_info = sql.read_sql_query(
                "PRAGMA index_info({ix_name})".format(ix_name=ix_name), self.conn
            )
            ix_cols.append(ix_info.name.tolist())
        return ix_cols 

def test_read_sql_iris(self):
        iris_frame = sql.read_sql_query(
            "SELECT * FROM iris", self.conn)
        self._check_iris_loaded_frame(iris_frame) 

def test_read_sql_view(self):
        iris_frame = sql.read_sql_query(
            "SELECT * FROM iris_view", self.conn)
        self._check_iris_loaded_frame(iris_frame) 

def test_to_sql_series(self):
        s = Series(np.arange(5, dtype='int64'), name='series')
        sql.to_sql(s, "test_series", self.conn, index=False)
        s2 = sql.read_sql_query("SELECT * FROM test_series", self.conn)
        tm.assert_frame_equal(s.to_frame(), s2) 

def test_roundtrip(self):
        sql.to_sql(self.test_frame1, 'test_frame_roundtrip',
                   con=self.conn)
        result = sql.read_sql_query(
            'SELECT * FROM test_frame_roundtrip',
            con=self.conn)

        # HACK!
        result.index = self.test_frame1.index
        result.set_index('level_0', inplace=True)
        result.index.astype(int)
        result.index.name = None
        tm.assert_frame_equal(result, self.test_frame1) 

def test_roundtrip_chunksize(self):
        sql.to_sql(self.test_frame1, 'test_frame_roundtrip', con=self.conn,
                   index=False, chunksize=2)
        result = sql.read_sql_query(
            'SELECT * FROM test_frame_roundtrip',
            con=self.conn)
        tm.assert_frame_equal(result, self.test_frame1) 

def test_date_and_index(self):
        # Test case where same column appears in parse_date and index_col

        df = sql.read_sql_query("SELECT * FROM types_test_data", self.conn,
                                index_col='DateCol',
                                parse_dates=['DateCol', 'IntDateCol'])

        assert issubclass(df.index.dtype.type, np.datetime64)
        assert issubclass(df.IntDateCol.dtype.type, np.datetime64) 

def test_timedelta(self):

        # see #6921
        df = to_timedelta(
            Series(['00:00:01', '00:00:03'], name='foo')).to_frame()
        with tm.assert_produces_warning(UserWarning):
            df.to_sql('test_timedelta', self.conn)
        result = sql.read_sql_query('SELECT * FROM test_timedelta', self.conn)
        tm.assert_series_equal(result['foo'], df['foo'].astype('int64')) 

def test_to_sql_index_label(self):
        temp_frame = DataFrame({'col1': range(4)})

        # no index name, defaults to 'index'
        sql.to_sql(temp_frame, 'test_index_label', self.conn)
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == 'index'

        # specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label='other_label')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "other_label"

        # using the index name
        temp_frame.index.name = 'index_name'
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "index_name"

        # has index name, but specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label='other_label')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "other_label"

        # index name is integer
        temp_frame.index.name = 0
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "0"

        temp_frame.index.name = None
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label=0)
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == "0" 

def test_to_sql_index_label_multiindex(self):
        temp_frame = DataFrame({'col1': range(4)},
                               index=MultiIndex.from_product(
                                   [('A0', 'A1'), ('B0', 'B1')]))

        # no index name, defaults to 'level_0' and 'level_1'
        sql.to_sql(temp_frame, 'test_index_label', self.conn)
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[0] == 'level_0'
        assert frame.columns[1] == 'level_1'

        # specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label=['A', 'B'])
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[:2].tolist() == ['A', 'B']

        # using the index name
        temp_frame.index.names = ['A', 'B']
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace')
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[:2].tolist() == ['A', 'B']

        # has index name, but specifying index_label
        sql.to_sql(temp_frame, 'test_index_label', self.conn,
                   if_exists='replace', index_label=['C', 'D'])
        frame = sql.read_sql_query('SELECT * FROM test_index_label', self.conn)
        assert frame.columns[:2].tolist() == ['C', 'D']

        # wrong length of index_label
        pytest.raises(ValueError, sql.to_sql, temp_frame,
                      'test_index_label', self.conn, if_exists='replace',
                      index_label='C') 

def test_multiindex_roundtrip(self):
        df = DataFrame.from_records([(1, 2.1, 'line1'), (2, 1.5, 'line2')],
                                    columns=['A', 'B', 'C'], index=['A', 'B'])

        df.to_sql('test_multiindex_roundtrip', self.conn)
        result = sql.read_sql_query('SELECT * FROM test_multiindex_roundtrip',
                                    self.conn, index_col=['A', 'B'])
        tm.assert_frame_equal(df, result, check_index_type=True) 

def test_categorical(self):
        # GH8624
        # test that categorical gets written correctly as dense column
        df = DataFrame(
            {'person_id': [1, 2, 3],
             'person_name': ['John P. Doe', 'Jane Dove', 'John P. Doe']})
        df2 = df.copy()
        df2['person_name'] = df2['person_name'].astype('category')

        df2.to_sql('test_categorical', self.conn, index=False)
        res = sql.read_sql_query('SELECT * FROM test_categorical', self.conn)

        tm.assert_frame_equal(res, df) 

def test_escaped_table_name(self):
        # GH 13206
        df = DataFrame({'A': [0, 1, 2], 'B': [0.2, np.nan, 5.6]})
        df.to_sql('d1187b08-4943-4c8d-a7f6', self.conn, index=False)

        res = sql.read_sql_query('SELECT * FROM `d1187b08-4943-4c8d-a7f6`',
                                 self.conn)

        tm.assert_frame_equal(res, df) 

def test_read_sql_delegate(self):
        iris_frame1 = sql.read_sql_query(
            "SELECT * FROM iris", self.conn)
        iris_frame2 = sql.read_sql(
            "SELECT * FROM iris", self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2)

        iris_frame1 = sql.read_sql_table('iris', self.conn)
        iris_frame2 = sql.read_sql('iris', self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2) 

def test_not_reflect_all_tables(self):
        # create invalid table
        qry = """CREATE TABLE invalid (x INTEGER, y UNKNOWN);"""
        self.conn.execute(qry)
        qry = """CREATE TABLE other_table (x INTEGER, y INTEGER);"""
        self.conn.execute(qry)

        with warnings.catch_warnings(record=True) as w:
            # Cause all warnings to always be triggered.
            warnings.simplefilter("always")
            # Trigger a warning.
            sql.read_sql_table('other_table', self.conn)
            sql.read_sql_query('SELECT * FROM other_table', self.conn)
            # Verify some things
            assert len(w) == 0 

def test_database_uri_string(self):

        # Test read_sql and .to_sql method with a database URI (GH10654)
        test_frame1 = self.test_frame1
        # db_uri = 'sqlite:///:memory:' # raises
        # sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near
        # "iris": syntax error [SQL: 'iris']
        with tm.ensure_clean() as name:
            db_uri = 'sqlite:///' + name
            table = 'iris'
            test_frame1.to_sql(table, db_uri, if_exists='replace', index=False)
            test_frame2 = sql.read_sql(table, db_uri)
            test_frame3 = sql.read_sql_table(table, db_uri)
            query = 'SELECT * FROM iris'
            test_frame4 = sql.read_sql_query(query, db_uri)
        tm.assert_frame_equal(test_frame1, test_frame2)
        tm.assert_frame_equal(test_frame1, test_frame3)
        tm.assert_frame_equal(test_frame1, test_frame4)

        # using driver that will not be installed on Travis to trigger error
        # in sqlalchemy.create_engine -> test passing of this error to user
        try:
            # the rest of this test depends on pg8000's being absent
            import pg8000  # noqa
            pytest.skip("pg8000 is installed")
        except ImportError:
            pass

        db_uri = "postgresql+pg8000://user:pass@host/dbname"
        with pytest.raises(ImportError, match="pg8000"):
            sql.read_sql("select * from table", db_uri) 

def test_read_sql_delegate(self):
        iris_frame1 = sql.read_sql_query("SELECT * FROM iris", self.conn)
        iris_frame2 = sql.read_sql("SELECT * FROM iris", self.conn)
        tm.assert_frame_equal(iris_frame1, iris_frame2)

        pytest.raises(sql.DatabaseError, sql.read_sql, 'iris', self.conn) 

def test_datetime_with_timezone_roundtrip(self):
        # GH 9086
        # Write datetimetz data to a db and read it back
        # For dbs that support timestamps with timezones, should get back UTC
        # otherwise naive data should be returned
        expected = DataFrame({'A': date_range(
            '2013-01-01 09:00:00', periods=3, tz='US/Pacific'
        )})
        expected.to_sql('test_datetime_tz', self.conn, index=False)

        if self.flavor == 'postgresql':
            # SQLAlchemy "timezones" (i.e. offsets) are coerced to UTC
            expected['A'] = expected['A'].dt.tz_convert('UTC')
        else:
            # Otherwise, timestamps are returned as local, naive
            expected['A'] = expected['A'].dt.tz_localize(None)

        result = sql.read_sql_table('test_datetime_tz', self.conn)
        tm.assert_frame_equal(result, expected)

        result = sql.read_sql_query(
            'SELECT * FROM test_datetime_tz', self.conn
        )
        if self.flavor == 'sqlite':
            # read_sql_query does not return datetime type like read_sql_table
            assert isinstance(result.loc[0, 'A'], string_types)
            result['A'] = to_datetime(result['A'])
        tm.assert_frame_equal(result, expected) 

def regionSelectRandom(conn):
	cur = conn.cursor()

	#Fetch number of entries in conflict tables
	rows = getConflictNumRows(conn)

	#Make sure there is some data to work on
	if rows is 0 or rows is None:
		raise ValueError("There are no rows in <conflicts>!")

	sql = '''
	UPDATE
		conflicts
	SET
		choose=1
	WHERE
		regid IN
			(SELECT
				regid
			FROM
				(SELECT
					*
				FROM
					conflicts
				WHERE
					choose="NULL"
				ORDER BY
					RANDOM()
				)
			GROUP BY
				conflict_block
			)
	'''
	#print(pd.read_sql_query(sql, conn))
	cur.execute(sql)
	conn.commit()

	#Set "unchosen" regions to 0/FALSE
	cur.execute("UPDATE conflicts SET choose=0 WHERE choose='NULL'")
	conn.commit()


#Function for resolving conflict blocks by number of flanking SNPs 

def test_to_sql_index_label_multiindex(self):
        temp_frame = DataFrame(
            {"col1": range(4)},
            index=MultiIndex.from_product([("A0", "A1"), ("B0", "B1")]),
        )

        # no index name, defaults to 'level_0' and 'level_1'
        sql.to_sql(temp_frame, "test_index_label", self.conn)
        frame = sql.read_sql_query("SELECT * FROM test_index_label", self.conn)
        assert frame.columns[0] == "level_0"
        assert frame.columns[1] == "level_1"

        # specifying index_label
        sql.to_sql(
            temp_frame,
            "test_index_label",
            self.conn,
            if_exists="replace",
            index_label=["A", "B"],
        )
        frame = sql.read_sql_query("SELECT * FROM test_index_label", self.conn)
        assert frame.columns[:2].tolist() == ["A", "B"]

        # using the index name
        temp_frame.index.names = ["A", "B"]
        sql.to_sql(temp_frame, "test_index_label", self.conn, if_exists="replace")
        frame = sql.read_sql_query("SELECT * FROM test_index_label", self.conn)
        assert frame.columns[:2].tolist() == ["A", "B"]

        # has index name, but specifying index_label
        sql.to_sql(
            temp_frame,
            "test_index_label",
            self.conn,
            if_exists="replace",
            index_label=["C", "D"],
        )
        frame = sql.read_sql_query("SELECT * FROM test_index_label", self.conn)
        assert frame.columns[:2].tolist() == ["C", "D"]

        msg = "Length of 'index_label' should match number of levels, which is 2"
        with pytest.raises(ValueError, match=msg):
            sql.to_sql(
                temp_frame,
                "test_index_label",
                self.conn,
                if_exists="replace",
                index_label="C",
            )