article

Saturday, May 1, 2021

Python Flask jQuery Ajax POST and insert data to PostgreSQL

Python Flask jQuery Ajax POST and insert data to PostgreSQL

install psycopg2 https://pypi.org/project/psycopg2/
Psycopg is the most popular PostgreSQL database adapter for the Python programming language.
(venv) PS C:\flaskmyproject> pip install psycopg2

Create TABLE
CREATE TABLE users (
id serial PRIMARY KEY,
fullname VARCHAR ( 100 ) NOT NULL,
username VARCHAR ( 50 ) NOT NULL,
password VARCHAR ( 255 ) NOT NULL,
email VARCHAR ( 50 ) NOT NULL
);

PostgreSQL SERIAL To Create Auto-increment Column
CREATE TABLE users (
id serial PRIMARY KEY
);
app.py
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#app.py
from flask import Flask, render_template, request, jsonify
import psycopg2 #pip install psycopg2
import psycopg2.extras
from werkzeug.security import generate_password_hash, check_password_hash
 
app = Flask(__name__)
app.secret_key = 'cairocoders-ednalan'
 
DB_HOST = "localhost"
DB_NAME = "sampledb"
DB_USER = "postgres"
DB_PASS = "admin"
 
conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST)
  
@app.route('/')
def index():
    return render_template('home.html')
  
@app.route('/process', methods=['POST'])
def process():
    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 
    msg = ''
    fullname = request.form['fullname']
    username = request.form['username']
    txtmail = request.form['txtmail']
    password = request.form['password']
    _hashed_password = generate_password_hash(password)
    print(fullname)
    if fullname and txtmail and username and password:
        cursor.execute("INSERT INTO users (fullname, username, password, email) VALUES (%s,%s,%s,%s)", (fullname, username, _hashed_password, txtmail))
        conn.commit()
        print('success')
        msg = 'You have successfully registered!'
        return jsonify({'name' : msg})
 
if __name__ == "__main__":
    app.run(debug=True)
templates/home.html
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
//templates/home.html
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>Python Flask jQuery Ajax POST and insert data to PostgreSQL </title>
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
       <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script>
       <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
   <script>
   $(document).ready(function() {
       $('form').on('submit', function(event) {
           $('#loading').show();
           $.ajax({
               data : {
                   fullname : $('#fullname').val(),
                   username : $('#username').val(),
                   password : $('#password').val(),
                   txtmail : $('#txtmail').val()
               },
               type : 'POST',
               url : '/process'
           })
           .done(function(data) {
               if (data.error) {
                   $('#errorAlert').text(data.error).show();
                   $('#successAlert').hide();
                   $('#loading').hide();
               }
               else {
                   $('#successAlert').text(data.name).show();
                   $('#errorAlert').hide();
                   $('#loading').hide();
               }
     
           });
           event.preventDefault();
       });
   });
   </script>
   </head>
    <body>
         <div class="container">
           <p><h1>Python Flask jQuery Ajax POST and insert data to PostgreSQL</h1></p>
           <div class="col-md-4 col-md-offset-4" id="login">
               <section id="inner-wrapper" class="login">
                               <article>
                                   <form class="form-signin">
                                   <h2 class="form-signin-heading">Please Sign Up </h2>
                                       <div class="form-group">
                                           <div class="input-group">
                                               <span class="input-group-addon"><i class="fa fa-user"> </i></span>
                                               <input type="text" class="form-control" name="fullname" id="fullname" placeholder="Your Name" required autofocus>
                                           </div>
                                       </div>
                                       <div class="form-group">
                                           <div class="input-group">
                                               <span class="input-group-addon"><i class="fa fa-user"> </i></span>
                                               <input type="text" class="form-control" name="username" id="username" placeholder="Your Username" required>
                                           </div>
                                       </div>
                                       <div class="form-group">
                                           <div class="input-group">
                                               <span class="input-group-addon"><i class="fa fa-envelope"> </i></span>
                                               <input type="email" class="form-control" name="txtmail" id="txtmail" placeholder="Email Address" required>
                                           </div>
                                       </div>
                                       <div class="form-group">
                                           <div class="input-group">
                                               <span class="input-group-addon"><i class="fa fa-key"> </i></span>
                                               <input type="password" class="form-control" name="password" id="password" class="form-control" placeholder="Password" required>
                                           </div>
                                       </div>
                                         <button type="submit" class="btn btn-success btn-block">Register</button>
                                   </form>
                                   <br/>
                   <div id="successAlert" class="alert alert-success" role="alert" style="display:none;"></div>
                   <div id="errorAlert" class="alert alert-danger" role="alert" style="display:none;"></div>
                   <h4 id='loading' style="display:none;"><img src="{{ url_for('static', filename='img/loader.gif') }}"/> Loading..</h4>
                     
                     
                               </article>
               </section>           
           </div>               
   </div>       
   <style>
   body {
       background:#333;
   }
   h1 {color:#fff;}
   #login {
       -webkit-perspective: 1000px;
       -moz-perspective: 1000px;
       perspective: 1000px;
       margin-top:50px;
       margin-left:30%;
   }
   .login {
       font-family: 'Josefin Sans', sans-serif;
       -webkit-transition: .3s;
       -moz-transition: .3s;
       transition: .3s;
       -webkit-transform: rotateY(40deg);
       -moz-transform: rotateY(40deg);
       transform: rotateY(40deg);
   }
   .login:hover {
       -webkit-transform: rotate(0);
       -moz-transform: rotate(0);
       transform: rotate(0);
   }
   .login .form-group {
       margin-bottom:17px;
   }
   .login .form-control,
   .login .btn {
       border-radius:0;
   }
   .login .btn {
       text-transform:uppercase;
       letter-spacing:3px;
   }
   .input-group-addon {
       border-radius:0;
       color:#fff;
       background:#f3aa0c;
       border:#f3aa0c;
   }
   .forgot {
       font-size:16px;
   }
   .forgot a {
       color:#333;
   }
   .forgot a:hover {
       color:#5cb85c;
   }
     
   #inner-wrapper, #contact-us .contact-form, #contact-us .our-address {
       color: #1d1d1d;
       font-size: 19px;
       line-height: 1.7em;
       font-weight: 300;
       padding: 50px;
       background: #fff;
       box-shadow: 0 2px 5px 0 rgba(0, 0, 0, 0.16), 0 2px 10px 0 rgba(0, 0, 0, 0.12);
       margin-bottom: 100px;
   }
   .input-group-addon {
       border-radius: 0;
           border-top-right-radius: 0px;
           border-bottom-right-radius: 0px;
       color: #fff;
       background: #f3aa0c;
       border: #f3aa0c;
           border-right-color: rgb(243, 170, 12);
           border-right-style: none;
           border-right-width: medium;
   }
   </style>             
    </body>
    </html>

Posted by ednalan at 11:25 PM . Under , .

Related Post