Python Forum
[mysql.connector] The database is never connected to and there are no errors
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[mysql.connector] The database is never connected to and there are no errors
#1
I made a class to manage a database, but there's a problem with connecting to the database. Line 11 just runs without any errors. If there was an error it should've printed on line 19, and I also tried removing the try statement, but still no error, it just runs continuously as the print statement on line 12 is never executed. Idk if it's the code or some other problem. Thanks in advance for any help.

import mysql.connector as conn

.
.
.

class DataBase:
    def __init__(self):
        try:
            print("Looking for database")
            self.db = conn.connect(host="127.0.0.1", port=8080, user="---------", passwd="----------", database="Socket_Online_Games_Database")
            print("Found already existing database")
            self.cursor = self.db.cursor()
            cursor.execute("SHOW TABLES")
            for table in tables:
                if table not in cursor:
                    self.cursor.execute("CREATE TABLE %s (%s)" % (table, ", ".join(tables[table])))
        except Exception as err:
            print(err)
            self.db = conn.connect(host="127.0.0.1", port=8080, user="---------", passwd="-----------")
            self.cursor = self.db.cursor()
            self.cursor.execute("CREATE DATABASE Socket_Online_Games_Database")
            print("Made new database")

            for table in tables:
                self.cursor.execute("CREATE TABLE %s (%s)" %(table, ", ".join(tables[table])))

    .
    .
    .
Reply
#2
Maybe unnecessary question, but do you instantiate the class?
Also, not related to your question, but I don't think if table not in cursor: would work as you expect. Not to mention tables are not defined (or are defined globally?)
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
(Jun-04-2020, 09:00 AM)buran Wrote: Maybe unnecessary question, but do you instantiate the class?
I'm using it with socket so server.py takes takes the DataBase class and instantiates it. That's when the init fires and the endless wait happens.
(Jun-04-2020, 09:00 AM)buran Wrote: Also, not related to your question, but I don't think if table not in cursor: would work as you expect. Not to mention tables are not defined (or are defined globally?)
tables is a list that I had defined before the class, I didn't include it in the code in the original question, but here it is below if you would like to see it.
tables = {
    "Users": ['id INT AUTO_INCREMENT PRIMARY KEY',  'username VARCHAR(20)', 'password VARCHAR(20)', 'dateJoined DATETIME', 'lastUsedMacAddress VARCHAR(17)', 'lastLog DATETIME']
}
I was thinking that the "SHOW TABLES" command will return each table as a string of it's name inside of a list as the value of cursor, well that's what I got from reading through the info here https://www.w3schools.com/python/python_..._table.asp. Is that not the case?

I just realized the for loop will have to change to this
for table in cursor:
    if table[0] not in tables:
        self.cursor.execute("CREATE TABLE %s (%s)" % (table, ", ".join(tables[table])))
Reply
#4
(Jun-04-2020, 05:41 PM)SheeppOSU Wrote: tables is a list that I had defined before the class,
that is my point - as is your class depends on global variable, defined outside the class.
If you want more flexibility - pass tables as argument to __init__ and [probably] make an instance variable out of it. If you want these and only these tables - make tables class variable. In any case - what you have now is not good.

as to the connection problem - no idea...
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#5
(Jun-04-2020, 05:41 PM)SheeppOSU Wrote: self.cursor.execute("CREATE TABLE %s (%s)" % (table, ", ".join(tables[table])))
table is still tuple, so what you pass will not work. and tables expect string as key
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#6
(Jun-04-2020, 06:56 PM)buran Wrote: table is still tuple
Oh, right, I forgot about that part, I'll be sure to change that.
(Jun-04-2020, 06:54 PM)buran Wrote: pass tables as argument to __init__
In that case I'll define it inside server.py and pass it into the DataBase init.
So this is the full code (settings contains the same ip and port shown in the original code). Also note that I have yet to test the class functions, I was trying to go error by error.
import mysql.connector as conn

class DataBase:
    def __init__(self, settings, tables):
        try:
            print("Looking for database")
            self.db = conn.connect(host=settings["IP"], port=settings["PORT"], user="***", passwd="***", database="Socket_Online_Games_Database")
            print("Found already existing database")
            self.cursor = self.db.cursor()
            cursor.execute("SHOW TABLES")
            for table in cursor:
                if table[0] not in tables:
                    self.cursor.execute("CREATE TABLE %s (%s)" % (table[0], ", ".join(tables[table[0]])))
        except Exception as err:
            print(err)
            self.db = conn.connect(host=settings["IP"], port=settings["PORT"], user="***", passwd="***")
            self.cursor = self.db.cursor()
            self.cursor.execute("CREATE DATABASE Socket_Online_Games_Database")
            print("Made new database")

            for table in tables:
                self.cursor.execute("CREATE TABLE %s (%s)" %(table, ", ".join(tables[table])))

    def insert_column(self, table, values):
        valueNames = ''.join(str(values[0]).split('\''))

        cmdDict = {True: self.cursor.execute, False: self.cursor.executemany}
        cmdDict[len(values)==1](f"INSERT INTO {valueNames} VALUES ({', '.join('%s'*len(values[0]))})", values[1:])

        self.db.commit()

    def get_column(self, table, columns, filter='', limit='', limitOffset=''):
        if filter != '':
            filter = f" WHERE {filter[0]} ='{filter[1]}'"
        if limit != '':
            limit = f" LIMIT {str(limit)}"
            if limitOffset != '':
                limitOffset = f" OFFSET {str(limitOffset)}"
        self.cursor.execute(f"SELECT {', '.join(columns)} FROM {table}" + filter + limit + limitOffset)
        return self.cursor.fetchall()

    def delete_column(self, table, filter):
        self.cursor.execute(f"DELETE FROM {table} WHERE {filter[0]} = '{filter[1]}'")
        self.db.commit()

    def delete_table(self, table):
        self.cursor.execute("DROP TALE IF EXISTS %s" %table)

    def update_column(self, table, columnInfo):
        self.cursor.execute(f"UPDATE {table} SET {columnInfo[0]} = '{columnInfo[2]}' WHERE {columnInfo[0]} = '{columnInfo[1]}'")
        self.db.commit()
As for the original error I'll continue to see if I can solve it myself. Thanks for helping.
Edit: Forgot about the username and password thank you for removing it
Reply
#7
(Jun-04-2020, 07:52 PM)SheeppOSU Wrote:
for table in cursor:
    if table[0] not in tables:
         self.cursor.execute("CREATE TABLE %s (%s)" % (table[0], ", ".join(tables[table[0]])))
you should iterate over keys in tables and if not present in the db - create the table

not tested but
db_tables = [table[0] for table in cursor]
for table, fields in tables.items():
    if table not in db_tables:
        self.cursor.execute("CREATE TABLE %s (%s)" % (table, ", ".join(fields)))
or
db_tables = set(table[0] for table in cursor)
for table in set(tables.keys()).difference(db_tables.inte):
    self.cursor.execute("CREATE TABLE %s (%s)" % (table, ", ".join(tables[table])))     
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#8
I made the for loop so that it also detects changes in the columns, though I haven't tested it yet. For the original problem, I opened up cmder, ran a Python shell, and I get an error when I try to create a database
db_tables = {table[0]: table[1:] for table in cursor}
for table, fields in tables.items():
    if table not in db_tables or len(tuple(set(fields).difference(set(db_tables[table]))) + tuple(set(db_tables[table]).difference(set(fields)))) > 0:
        self.delete_table(table)
        self.cursor.execute("CREATE TABLE %s (%s)" % (table, ", ".join(fields)))
Error:
Traceback (most recent call last): File "C:\Users\Sheep\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\mysql\connector\network.py", line 509, in open_connection self.sock.connect(sockaddr) ConnectionRefusedError: [WinError 10061] No connection could be made because the target machine actively refused it During handling of the above exception, another exception occurred: Traceback (most recent call last): File "<stdin>", line 1, in <module> File "C:\Users\Sheep\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\mysql\connector\__init__.py", line 179, in connect return MySQLConnection(*args, **kwargs) File "C:\Users\Sheep\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\mysql\connector\connection.py", line 95, in __init__ self.connect(**kwargs) File "C:\Users\Sheep\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\LocalCache\local-packages\Python38\site-packages\mysql\connector\abstracts.py", line 716, in connect self._open_connection()
I don't know what to do to fix this error.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Display name of a pc connected over lan fatopeo 1 1,365 Mar-16-2022, 10:10 AM
Last Post: Gribouillis
  mysql connector/telnet issue (re: text game) rebubula76 1 2,505 Feb-06-2018, 08:00 PM
Last Post: rebubula76

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020