Python pandas.read_sql_query() Examples

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 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 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 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 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(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_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 __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 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 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_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_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)