[Python] Pandas - CSV to SQL database

I have now resolved the issues I was experiencing. Many thanks commenters. My amended and now functioning code is as follows:

import pandas as pd
from sqlalchemy import *
from sqlalchemy_utils import *

#create engine and connect to DB
engine = create_engine("mysql://root127.0.0.1:3306/polution")
if not database_exists("mysql://root127.0.0.1:3306/polution"):
create_database("mysql://root127.0.0.1:3306/polution")
else:
engine.connect()

#create tables and define
metadata_obj=MetaData()
sites = Table('sites', metadata_obj,
Column('SiteID',Integer, primary_key=True,nullable=False),
Column('Location',Text(48)),
Column('geo_point_2d', String(150)))
metadata_obj.create_all(engine)

#read in csv, set specific table-relevant dataframes, push to database
df = pd.read_csv("cleanedcropped_bristol-air-quality-data.csv", sep=",", parse_dates=["Date Time","DateStart","DateEnd"],low_memory=False)

dfsites=df[["SiteID", "Location", "geo_point_2d"]][:100]
dfsites.to_sql(name='sites',con=engine,index=False,if_exists='replace',)`


I'm really new to all this and I'm currently working on something that is stretching my skill and understanding so I'd appreciate some help please.

I have a large csv file that I want to read into a pandas data frame and then push the contents to an already created mariadb database made up of multiple entities. Currently, I'm trying to attempt this with the to_sql() function. Running the script seems not to actually push any data from the dataframe to the entity in the database as it remains empty after run.

FYI the dataframe contains multiple columns, only some of which I want to push to the relevant entity.

Any pointers greatly appreciated!

conn = sqlalchemy .create_engine("mysql://root127.0.0.1:3306")
cur = conn.cursor()

cur.execute(databse = "pollution")

sites= """CREATE TABLE IF NOT EXISTS `sites`
(`siteID` INT(20) NOT NULL,
`Location` VARCHAR(48) NULL,
`geo_point_2d` VARCHAR(150) NULL,
PRIMARY KEY (`siteID`))"""

cur.execute(sites)

df = pd.read_csv("data.csv", sep=",", parse_dates ["DateTime","DateStart","DateEnd"],low_memory=False)

df.to_sql('sites', conn, if_exists='append',index=False)
conn.commit()

Continue reading...