How to use psycopg2 to insert data into a postgres Database

Question

I have created an app using flask, it takes an input from a user and saves the input into the Postgres Database here's the code:

    from flask import Flask, request, render_template, jsonify
    import psycopg2 as pg2
    import exquixdb

    app = Flask(__name__)


    @app.route('/questions', methods=["POST"])
    def post_question():
        request_data = request.json

        table = ("INSERT INTO question(id, question) VALUES(%s,%s)", (1003, str(request_data['question'])))
        exquixdb.db.create(table)
        app.logger.info("adding questions")



        return "question saved"


    if __name__ == '__main__':
        app.run(debug=True, port=8989)

I've created a database Module the code is:

# Module exquizdb.py
import psycopg2 as pg2


class Database():
    def db_connect(self, db="exquizdb", user="jim", pas="password", port=5433):
        global conn
        self.conn = pg2.connect(database=db, user=user, password=pas, port=port)
        global cur
        self.cur = self.conn.cursor()

    def close(self):
        self.cur.close()
        self.conn.close()

    def query(self, query):
        self.cur.execute(query)
        self.close()

    def create(self, table):
        self.cur.execute(table)
        self.conn.commit()
        self.close()


db = Database()

table = ("INSERT INTO question(id, question) VALUES(%s,%s)", (1003, 'Test'))

db.create(table)

so when I run the code I get (AttributeError: 'Database' object has no attribute 'cur') I am new database and python. So i don't know how to fix it. How do I fix the problem. thank you.

sannitywo · Accepted Answer · 2020-05-18 03:09:52Z

4

The error is as a result of not making proper use of Class. The global is not needed as the variables could have been initiated in the class constructor.

Also, the create and query method can be defined as one. Closing the connection after every query means you will have to create a new connection before running a new query.

NOTE: I refactored the query and create method as execute_query. Also the methods can still be improved on for proper error handling. Adding a try and catch block can suffice.

# Module exquizdb.py
import psycopg2 as pg2


class Database:
  def __init__(self, db, username, password, port):
    self.db = db
    self.username = username
    self.password = password
    self.port = port
    self.cur = None
    self.conn = None

  def connect(self):
    self.conn = pg2.connect(database=self.db, user=self.user, password=self.password, port=self.port)
    self.cur = self.conn.cursor()

  def execute_query(self, query):
    self.cur.execute(query)
    self.conn.commit()

  def close(self):
    self.cur.close()
    self.conn.close()

db = Database(db="exquizdb", user="jim", pas="password", port=5433)

db.connect()

    from flask import Flask, request, render_template, jsonify
    import psycopg2 as pg2
    from exquixdb import db

    app = Flask(__name__)


    @app.route('/questions', methods=["POST"])
    def post_question():
        request_data = request.json

        table = ("INSERT INTO question(id, question) VALUES(%s,%s)", (1003, str(request_data['question'])))
        db.execute_query(table)
        app.logger.info("adding questions")


        return "question saved"


    if __name__ == '__main__':
        app.run(debug=True, port=8989)

answered May 18, 2020 at 3:09

sannitywo

563 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zedfoxus Over a year ago

Neat. Thanks for sharing the refactored example with your explanation.

jimrahman Over a year ago

Thank you very much for that explanation. your explanation really helped me complete the project. Thank you very much.

Collectives™ on Stack Overflow

How to use psycopg2 to insert data into a postgres Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related