2

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()
snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
Ricky
  • 284
  • 1
  • 3
  • 19

4 Answers4

0

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})
...
metatoaster
  • 17,419
  • 5
  • 55
  • 66
  • You can probably see that I accidentally left a single quote at the third last character (copy/paste fail), I removed it. – metatoaster Aug 25 '14 at 06:35
  • Would also help if you post some traceback to show us exactly where your errors are. – metatoaster Aug 25 '14 at 06:36
  • I was unable to see stacktrace because i am not having any IDE for application development in python – Ricky Aug 25 '14 at 06:40
  • `More information about this error may be available in the server error log. Apache/2.4.6 (Ubuntu) Server at localhost Port 80` Refer to your logs, also your python CGI environment may be misconfigured (unless your Hello World example worked). – metatoaster Aug 25 '14 at 06:46
  • thnks for your kind information @kecer i check it . – Ricky Aug 25 '14 at 06:49
0

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])
Community
  • 1
  • 1
kecer
  • 3,031
  • 2
  • 21
  • 24
  • `cursor.execute` still has wrong indentation, double check it in your code. After that, I see no other error. I recommend you wrapping importing MySQLdb and creating instance in `db = MySQLdb.connect("localhost","root","123456789","testdrive" )` with try&except to see if error is not there. If it isn't, then your cgi is most likely misconfigured (although I don't think I have ever seen that, assuming you have default values). Did you find anything in logs? /var/log/apache2/error.log most likely. ALSO, try replacing `\n\n` with `\r\n\r\n` in your first print – kecer Aug 25 '14 at 07:03
0

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]
    ...
lenhhoxung
  • 2,530
  • 2
  • 30
  • 61
0

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

SMshrimant
  • 638
  • 9
  • 15