2017年5月31日水曜日

[Python][CGI]SQLクエリーを実行した結果を表示する

test3.html
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
    <title>Ajax Test, SQL query</title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
    <script type="text/javascript">
      $(function() {
        $('#sendButton').click(function(event) {
          $.post(
            "/cgi-bin/python_test/test3.py",
            { sendValue: $('#animal').val() },
            function(data, textStatus) {
              if (textStatus == 'success') {
                $('#textStatus').text('Success');
              }
              $("#result").html(data);
          }, 'html')
          .fail(function() {
              $("#result").html("Failed");
            }
          );
        });
      });
    </script>
  </head>
  <body>
    動物名<br/>
    <input type="text" name="animal" value="" id="animal"/><br/>
    <button type="button" id="sendButton">送信</button><br/><br/>
    textStatus: <span id="textStatus"></span><br/>
    Result: <span id="result"></span>
  </body>
</html>
test3.py
#!/usr/bin/python
#-*- coding:utf-8 -*-

import cgi
import psycopg2

# エラー発生時にレポートを表示
import cgitb
cgitb.enable()

html0 = """
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8"/>
"""

#######################################################################
# DBに接続
#######################################################################
def connectDb():
 connection = psycopg2.connect("dbname=testdb user=username password=password")
 cur = connection.cursor()

 return connection, cur

#######################################################################
# DBから切断
#######################################################################
def disconnectDb(connection, cur):
 # commit
 connection.commit()

 cur.close()
 connection.close()

#######################################################################
# SQL query
#######################################################################
def select(cur, animal):
 sql = "SELECT id FROM animals WHERE name='" + animal + "';"
 print(sql + '<br/>')
 cur.execute(sql)
 result = cur.fetchone()
 if result == None:
  print(animal + ' is not found.<br/>')
 else:
  print(animal + "'s id is " + str(result[0]))

#######################################################################
# Main function
#######################################################################
if __name__ == '__main__':
 print(html0)
 form = cgi.FieldStorage()
 animal = form['sendValue'].value
 print('animal: ' + animal + '<br/>')

 # DBに接続する
 connection, cur = connectDb()

 # SQL query (SELECT)
 id = select(cur, facility)

 # DBから切断する
 disconnectDb(connection, cur)
実行結果(ブラウザに渡されるHTMLソース)
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; CHARSET=UTF-8">
    <title>Ajax Test, SQL query</title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
    <script type="text/javascript">
      $(function() {
        $('#sendButton').click(function(event) {
          $.post(
            "/cgi-bin/python_test/test3.py",
            { sendValue: $('#animal').val() },
            function(data, textStatus) {
              if (textStatus == 'success') {
                $('#textStatus').text('Success');
              }
              $("#result").html(data);
          }, 'html')
          .fail(function() {
              $("#result").html("Failed");
            }
          );
        });
      });
    </script>
  </head>
  <body>
    動物名<br>
    <input type="text" name="animal" value="" id="animal"><br>
    <button type="button" id="sendButton">送信</button><br><br>
    textStatus: <span id="textStatus">Success</span><br>
    Result: <span id="result">
      <meta http-equiv="Content-Type" content="text/html; CHARSET=utf-8">
      animal: dog<br>
      SELECT id FROM animals WHERE name='dog';<br>
      dog's id is 3
    </span>
  </body>
</html>

0 件のコメント:

コメントを投稿