How To Fetch data in Python from mysql database [duplicate]

Question

I am new to python and i am facing problem while fetching data from mysql db while i am passing parameters in mysql query i think my mysql syntax is incorrect .

Here is the Error displayed on Screen Like this.

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator at webmaster@localhost to inform them of the time this error occurred, and the actions you performed just before this error.

More information about this error may be available in the server error log. Apache/2.4.6 (Ubuntu) Server at localhost Port 80

Here Is My Code For Select query in that I want to fetch data from get parameter of Url.

   #!/usr/bin/python2.7

import cgi;
import cgitb; cgitb.enable();
import time, calendar;

print "Content-type: text/html\n\n";

print "<h1>Hello Python</h1>";

#!/usr/bin/python

import MySQLdb

# Create instance of FieldStorage 
form = cgi.FieldStorage() 

# Get data from fields
first_name = form.getvalue('first_name')
last_name  = form.getvalue('last_name')

# Open database connection
db = MySQLdb.connect("localhost","root","123456789","testdrive" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database
sqlstmt = "SELECT * FROM EMPLOYEE WHERE FIRST_NAME = %(first_name)s AND LAST_NAME = %(last_name)s"
    
try:
   # Execute the SQL command
 cursor.execute(sqlstmt, {'first_name': first_name, 'last_name': last_name})
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"

# disconnect from server
db.close()

Answer 1

You have an error in this line

sql = "SELECT * FROM EMPLOYEE WHERE FIRST_NAME = '".first_name."' AND LAST_NAME = '".last_name."'"

This isn't PHP, meaning you can't concat strings and variables like this. I assume you wan't to make prepared statements. In that case, you should read following reply.

Relevant part of your code would look like this:

cursor.execute("SELECT * FROM EMPLOYEE WHERE FIRST_NAME = %s AND LAST_NAME = %s", [first_name, last_name])

Answer 2

First off, you should try abstracting all that into a single function you can call outside of CGI, but that's a whole other exercise now. Anyway, if you had done that you can get the stacktrace much easier to see what you did wrong, however, I can see you have a syntax error in the code you helpfully included

sql = "SELECT * FROM EMPLOYEE WHERE FIRST_NAME = '".first_name."' AND LAST_NAME = '".last_name."'"

Python string concatenation uses the + operator, not . like it is in PHP.

Second, this code is not secure. See http://xkcd.com/327/

To fix this, the cursor.execute method provides a second argument to fill out the tokens, this is what you should do

sqlstmt = "SELECT * FROM EMPLOYEE WHERE FIRST_NAME = %(first_name)s AND LAST_NAME = %(last_name)s"

try:
    cursor.execute(sqlstmt, {'first_name': first_name, 'last_name': last_name})
...

Answer 3

I think we don't need fetchall() here, which maybe legacy from sqlite code. Just do something like:

for row in cursor:
    x = row[0]
    y = row[1]
    ...

Answer 4

Try this code

import mysql.connector
from mysql.connector import Error
try:
   mySQLconnection = mysql.connector.connect(host='localhost',
                             database='python_database',
                             user='username',
                             password='passw0rd')
   sql_select_Query = "select * from tablename"
   cursor = mySQLconnection .cursor()
   cursor.execute(sql_select_Query)
   records = cursor.fetchall()

   for row in records:
       print("Sr No = ", row[0], )
       print("Name = ", row[1])
       print("Age  = ", row[2])
       print("Gender  = ", row[3], "\n")
   cursor.close()

except Error as e :
    print ("Error connecting MySQL", e)
finally:
    #closing database connection.
    if(mySQLconnection .is_connected()):
        connection.close()
        print("MySQL connection is closed Now"

Ref