I'm having this error message when connecting python to mysql using mariadb

Avatar
  • updated
  • Answered

This is my code...

# module imports
import mariadb
import sys
import csv
from datetime import datetime
import re

try:
# set the user and passoword
# connect to mariaDB platform
conn_mydb = mariadb.connect(
user="root",
password="",
host="127.0.0.1", # localhost will also do
port=3306 # possibly some other port
)

# make and get the cursor
cur = conn_mydb.cursor()

# create the database - as new
cur.execute("DROP DATABASE IF EXISTS pollutiondb2")
cur.execute("CREATE DATABASE pollutiondb2")

# empty list to hold records
empty_lists = [];

# read in the csv file as a list one at a time
with open('clean.csv','r') as csvfile:
reader = csv.reader(csvfile, delimiter=';')
for row in reader:
empty_lists.append(row)

# empty_lists[] is now a list of lists

# get rid of the header row
empty_lists.pop(0)

# get a database handle
cur.execute("USE pollutiondb2")

# define the SQL for the tables
Sites_sql = """CREATE TABLE `Sites`
(`SiteID` INT NOT NULL,
`Location` VARCHAR(45) NOT NULL,
`geo_point_2d` VARCHAR(45) NOT NULL,
PRIMARY KEY(`SiteID`));"""

Readings_sql = """CREATE TABLE `Readings`
(`DateTime` DATETIME,
`NOx` FLOAT,
`NO` FLOAT,
`NO2` FLOAT,
`pm10` FLOAT,
`nvpm10` FLOAT,
`vpm10` FLOAT,
`nvpm2.5` FLOAT,
`pm2.5` FLOAT,
`vpm2.5` FLOAT,
`CO` FLOAT,
`O3` FLOAT,
`SO2` FLOAT,
`Temperature` REAL,
`Rh` INT,
`AirPressure` INT,
`DateStart` DATETIME,
`DateEnd` DATETIME,
`Current` TEXT(5),
`InstrumentType` VARCHAR(45),
`SiteID` INT);"""

Schema_sql = """CREATE TABLE `Schema`
(`Measure` INT NOT NULL,
`Description` VARCHAR(45),
`Unit` VARCHAR(16),
PRIMARY KEY (`Measure`));"""

cur.execute(Sites_sql)
cur.execute(Readings_sql)
cur.execute(Schema_sql)
# add the relationships
cur.execute("ALTER TABLE Readings ADD FOREIGN KEY (`SiteID`) REFERENCES Sites(`SiteID`);")

# get the current timestamp
now = datetime.now()

for row in empty_lists:

# set the autocommit flag to false
conn_mydb.autocommit = False

#insert Sites
Sites_sql = """INSERT INTO Sites values(%s, %s, %s)"""
Sitevalues = ("",row[5], row[18], row[19])

cur.execute(Sites_sql, Sitevalues)
SiteId = cur.lastrowid

#insert Readings
Readings_sql = """INSERT INTO Readings VALUES (%s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s, %s,%s, %s)"""
Readingvalues = ("",row[1],row[2], row[3], row[4], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[20], row[21], row[22], row[23] , row[5])

cur.execute(Readings_sql, Readingvalues)
Readingval = cur.lastrowid

#Insert Schema

Schema_sql = """INSERT INTO Schema VALUES (%s, %s, %s)"""
Schemavalues = [
("DateTime", "Date and time of measurement", "datetime"),
("NOx", "Concentration of oxides of nitrogen", "㎍/m3"),
("NO2", "Concentration of nitrogen dioxide", "㎍/m3"),
("NO", "Concentration of nitric oxide","㎍/m3"),
("SiteID", "Site ID for the station", "integer"),
("PM10", "Concentration of particulate matter <10 micron diameter", "㎍/m3"),
("NVPM10", "Concentration of non - volatile particulate matter <10 micron diameter", "㎍/m3"),
("VPM10", "Concentration of volatile particulate matter <10 micron diameter ㎍/m3"),
("NVPM2.5", "Concentration of non volatile particulate matter <2.5 micron diameter", "㎍/m3"),
("PM2.5", "Concentration of particulate matter <2.5 micron diameter", "㎍/m3"),
("VPM2.5", "Concentration of volatile particulate matter <2.5 micron diameter", "㎍/m3"),
("CO", "Concentration of carbon monoxide", "㎎/m3"),
("O3", "Concentration of ozone", "㎍/m3"),
("SO2", "Concentration of sulphur dioxide", "㎍/m3"),
("Temperature", "Air temperature", "°C"),
("RH", "Relative Humidity", "%"),
("Air Pressure", "Air Pressure", "mbar"),
("Location", "Text description of location", "text"),
("geo_point_2d", "Latitude and longitude", "geo point"),
("DateStart", "The date monitoring started", "datetime"),
("DateEnd", "The date monitoring ended", "datetime"),
("Current", "Is the monitor currently operating", "text"),
("Instrument Type", "Classification of the instrument", "text")
]

cur.executemany(Schema_sql, Schemavalues)
print("insert Schema done")
conn_mydb.commit()
conn_mydb.close()

# catch and report on any error
# exit with 1 (non-error scripts automatically exit with 0)
except BaseException as err:
print(f"An error occured: {err}")
sys.exit(1)

HERE IS THE ERROR...

ERROR:root:Internal Python error in the inspect module.
Below is the traceback from this internal error.

An error occured: list index out of range
Traceback (most recent call last):
  File "C:\Users\Haywh\AppData\Local\Temp/ipykernel_19016/177183142.py", line 101, in <module>
    Readingvalues = ("",row[1],row[2], row[3], row[4], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[20], row[21], row[22], row[23] , row[5])
IndexError: list index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\Haywh\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py", line 3444, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "C:\Users\Haywh\AppData\Local\Temp/ipykernel_19016/177183142.py", line 144, in <module>
    sys.exit(1)
SystemExit: 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\Haywh\Anaconda3\lib\site-packages\IPython\core\ultratb.py", line 1101, in get_records
    return _fixed_getinnerframes(etb, number_of_lines_of_context, tb_offset)
  File "C:\Users\Haywh\Anaconda3\lib\site-packages\IPython\core\ultratb.py", line 248, in wrapped
    return f(*args, **kwargs)
  File "C:\Users\Haywh\Anaconda3\lib\site-packages\IPython\core\ultratb.py", line 281, in _fixed_getinnerframes
    records = fix_frame_records_filenames(inspect.getinnerframes(etb, context))
  File "C:\Users\Haywh\Anaconda3\lib\inspect.py", line 1541, in getinnerframes
    frameinfo = (tb.tb_frame,) + getframeinfo(tb, context)
AttributeError: 'tuple' object has no attribute 'tb_frame'
Avatar
IMH Support Agent 2
  • Answered

Hello Ogunsola Samuel,

Thanks for the question on connecting to the MySQL database (using MariaDB).  If you're an InMotion Hosting customer, you will need to create a RemoteMySQL connection in cPanel.  Then you can properly address it in your code.  You can see how to do it in this article:  Setting up a Remote MySQL Database Connection.

If you're not an InMotion customer, then you need to find out how you can open the ports and create that connection, then properly identify it in your code.  If you have any further questions or comments, please let us know.


Kindest Regards,

Arnel C.

Avatar
IMH Support Agent 1
  • Under review

Hello Ogunsola,

Thank you for contacting us about an error message when connecting python to a database. It's difficult to say without understanding the full scope of what you are coding, but I noticed you are getting the following error:

IndexError: list index out of range

I found a post via online search where they say you may be trying to access an element that is out of the range of the list. There is also a short example in that post.

Thank you,

John-Paul