First time with mysql - Printable Version

First time with mysql - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: First time with mysql (/thread-35041.html)

First time with mysql - pascal111 - Sep-24-2021

I'm a Python beginner, and tried to learn mysql, I typed this code:

import mysql.connector

mydb = mysql.connector.connect(user="pascal", database="gogo")

print(mydb)

I tried to run the code but found errors:

Quote:pascal@pascal-Lenovo-ideapad-330-15AST:~/Computer/Python$ python3 j3.py
Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 239, in _open_connection
self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on '127.0.0.1:3306' (111)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "j3.py", line 3, in <module>
mydb = mysql.connector.connect(user="pascal", database="gogo")
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/__init__.py", line 272, in connect
return CMySQLConnection(*args, **kwargs)
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 85, in __init__
self.connect(**kwargs)
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/abstracts.py", line 1009, in connect
self._open_connection()
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 241, in _open_connection
raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (111)

RE: First time with mysql - bowlofred - Sep-24-2021

Without providing a server and a port, it assumes the server is 127.0.0.1 (your local machine) and the port is 3306. But you're not running a mysql server there (or if you are, it isn't reachable).

You need to put the server that is running the mysql in your connect() initializer.

RE: First time with mysql - pascal111 - Sep-24-2021

(Sep-24-2021, 08:19 PM)bowlofred Wrote: Without providing a server and a port, it assumes the server is 127.0.0.1 (your local machine) and the port is 3306. But you're not running a mysql server there (or if you are, it isn't reachable).

You need to put the server that is running the mysql in your connect() initializer.

How can I do that especially that I'm a beginner and don't know much terms?

RE: First time with mysql - deanhystad - Sep-24-2021

You can look for a tutorial.

RE: First time with mysql - bowlofred - Sep-24-2021

(Sep-24-2021, 08:24 PM)pascal111 Wrote: How can I do that especially that I'm a beginner and don't know much terms?

https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

RE: First time with mysql - Pedroski55 - Sep-25-2021

Of course you must first have a MySQL database and in that database a table.

My table names look like: allstudentsAnswers20BE

host is the ip of the remote or local server: host='123.456.789.123'
user is your user name in the that database: user='my_username'
password is the password for this user on that database: password = 'my_database_password',
db is the name of the database you want to access: db='my_database_name'

I have several different classes of students and each class has a table.

I need to collect the data in the 2 columns studentnr and score and write them to a dictionary.

So I pass clas and weeknr to the function below and it works just like magic!

So fast, if you blink, you missed it.

def mysqlRemoteCW(clas, weeknr): 
        # To connect remote MySQL database 
        conn = pymysql.connect( 
            host='123.456.789.123', 
            user='my_username',  
            password = 'my_database_password', 
            db='my_database_name', 
            ) 
          
        cur = conn.cursor()
    
        # Select query 
        #cur.execute(f"SELECT studentnr, score FROM allstudentsAnswers{clas} WHERE weeknr = '{weeknr}'")
        #cursor.execute("SELECT spam FROM eggs WHERE lumberjack = ?", (lumberjack,))
        sql = f"SELECT studentnr, score FROM allstudentsAnswers{clas}CW WHERE weeknr = %s"
        cur.execute(sql, (weeknr,))
        output = cur.fetchall() 
          
        #for i in output: 
            #print(i) 
          
        # To close the connection 
        conn.close()
        return output

results = mysqlRemoteCW(clas, weeknr)
# now put results in a dictionary

And yeah, work through a tutorial. MySQL takes some getting used to.

Good Luck!