I'm having this error message when connecting python to mysql using mariadb
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'
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.