CODEWITHSUNDEEP
pythonpython-3.xpandassqlite
LOKE2707
LOKE2707

Reputation: 312

data from website table to sqlite database

Instead of actual numbers or text, the data object is being extracted from the table from the website.

I am trying to extract info from a website table and store in an SQLite database. I am getting errors due to data that are extracted are of dtype: object and not text or numbers etc. can anyone help me with the best and shortest way to achieve my goal?

so data2 is the data frame made by pd.read_html('weblink')

enter image description here

db = sqlite3.connect('test.sqlite')
db.execute("CREATE TABLE IF NOT EXISTS data(Time TEXT, fut_volume INTEGER, fut_turnover REAL, fut_OI INTEGER, opt_volume INTEGER,opt_turnover REAL, opt_OI INTEGER)")
cursor = db.cursor()

fut_volume = data2[1][data2[1][0]=='Index Futures'][1]
fut_turnover = data2[1][data2[1][0]=='Index Futures'][2]
fut_OI = data2[1][data2[1][0]=='Index Futures'][4]
opt_volume = data2[1][data2[1][0]=='Index Options'][1]
opt_turnover = data2[1][data2[1][0]=='Index Options'][2]
opt_OI = data2[1][data2[1][0]=='Index Options'][4]
str = f"INSERT INTO contacts VALUES({datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')},
        {fut_volume},{fut_turnover},{fut_OI},{opt_volume},{opt_turnover},{opt_OI})"

enter image description here

enter image description here

Upvotes: 1

Views: 190

Answers (1)

dbz
dbz

Reputation: 431

Parse objects first, this is an example parsing dtype object to integer.

data2['column'].astype(str).astype(int)

Upvotes: 1

Related Questions