CODEWITHSUNDEEP
pythonmysqlsql
Huzefa Sadikot
Huzefa Sadikot

Reputation: 581

Sql Select statement Optimization

I have made an test table in sql with the following information schema as shown:

Database Schema and Table data

Now I extract this information using the python script the code of which is as shown:

import pandas as pd
import mysql.connector
 
db = mysql.connector.connect(host="localhost", user="root", passwd="abcdef")
pointer = db.cursor()
pointer.execute("use holdings")
x = "Select * FROM orders where tradingsymbol like 'TATACHEM'"
pointer.execute(x)
rows = pointer.fetchall()
rows = pd.DataFrame(rows)
stock = rows[1]

SQL TABLE Imported as Pandas DF

The production table contains 200 unique trading symbols and has the schema similar to the test table.

My doubt is that for the following statement:

x = "Select * FROM orders where tradingsymbol like 'TATACHEM'"

I will have to replace value of tradingsymbols 200 times which is ineffective.

Is there an effective way to do this?

Upvotes: 0

Views: 143

Answers (1)

C Hecht
C Hecht

Reputation: 1016

If I understand you correctly, your problem is that you want to avoid sending multiple queries for each trading symbol, correct? In this case the following MySQL IN might be of help. You could then simply send one query to the database containing all tradingsymbols you want. If you want to do different things with the various trading symbols, you could select the subsets within pandas.

Another performance improvement could be pandas.read_sql since this speeds up the creation of the dataframe somewhat

Two more things to add for efficiency:

  • Ensure that tradingsymbols is indexed in MySQL for faster lookup processes
  • Make tradingsymbols an ENUM to ensure that no typos or alike are accepted. Otherwise the above-mentioned "IN" method also does not work since it does a full-text comparison.

Upvotes: 1

Related Questions