Question 195131 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Queries on Python

I am new on Python and SQL. I’m trying to create a simple code to create and then access a database and then a table to try to understand how sql works. In order to do so, I have the following code:

import mysql.connector
from companyClient import client

def createTable():
    cursor.execute("CREATE TABLE Clients" 
         "(id INT NOT NULL AUTO_INCREMENT,"
        "name VARCHAR (32) NOT NULL,"
        "surname VARCHAR (64) NOT NULL,"
        "PRIMARY KEY (id));")

    print("Table created!")


def showTable():
    print("************** Clients Table *************n")
    tableContent = cursor.execute("SELECT * FROM Clients")
    

def createEntry():
    cursor.execute("INSERT INTO Company.Clients (name, surname) VALUES ('John','Smith')")

def addUser(userToAdd):
    try:
        cursor.execute("INSERT INTO Company.Clients VALUES %s, %s, %s", userToAdd.id, userToAdd.name, userToAdd.surname)
    except:
        print("It is not possible to add the user. Please try again.")

def findUser(userToFind):
    if(cursor.execute("SELECT * FROM Company.Clients WHERE id = %s "), userToFind.id) :
        return True
    else:
        return False

def removeUser(userToRemove):
    try:
        cursor.execute("REMOVE * FROM Company.Clients WHERE id = %s ", userToRemove.id)
    except:
        print("It is not possible to remove this user. Please try again.")

#server connection 
conexion = mysql.connector.connect(
    host="localhost",
    user="********",
    passwd="*********",
    database="Company"
)

cursor = conexion.cursor()
showTable()

I am using SQL workbench to manage the database. I have already created the table "Clients" in Company database calling the createTable function. Using SQL workbench, I have added an entry just to check how I can get that entry on Python. However, when I called the function showTable, the value of tableContent is null. Apart from the current query SELECT * FROM Clients, I have also tried SELECT * FROM Company.Clients, getting the same exact result. How can I properly access the table entry?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have the problem. I have seen that the only thing that I needed to do is to add

    records = cursor.fetchall()

    after launching the query to get the actual entries.


  2. 0

    cursor.execute() does not return a result of the query.You need to fetch the results stored in the cursor.
    The modified function below should behave as per your expectation

    def showTable():
    print("************** Clients Table *************n")
    cursor.execute("SELECT * FROM Clients")
    tableContent = cursor.fetchall()
    return tableContent
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search