Inserting data from a table to another (in same db)

[firebird]

Hello Everyone!
Would you kindly help figure out how to manage to carry out the following task. I have a database with two table, jobs (where there some data I'm interested in) and picks (where I'm looking to copy/insert data coming from jobs.
I'm working in Flask and Postgres, with my current code (below) I get error message: "Invalid syntax". Thank you for your help.

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

@app.route('/apply/<job_id>/', methods=['GET', 'POST']) @login_required def apply(job_id):     for_listing = Job.query.get(job_id)     applicant = session['username']     date_applied = datetime.now()     try:         connection = psycopg2.connect(user="postgres",                                   password="mypassword",                                   host="127.0.0.1",                                   port="5432",                                   database="jobapplied")         cursor = connection.cursor()         postgres_insert_query = INSERT INTO picks (for_listing, applicant, date_applied, jobname, jobdesc )                                 (SELECT job_name, job_desc, FROM jobs WHERE id = for_listing)                  cursor.execute(postgres_insert_query)         connection.commit()         flash ("Record inserted successfully into the picks table")     except (Exception, psycopg2.Error) as error :         if(connection):             flash ("Failed to insert record into picks table", error)     finally:         #closing database connection.         if(connection):             cursor.close()             connection.close()

[buran]

This is very poor design. why would you copy data from one table to another?

[firebird]

This is very poor design. why would you copy data from one table to another?

Hi, thank you for your reply and your observation. I'm prototyping out a project that involves posting on a job board and job seekers selecting jobs. When a job seeker has chosen a job that job is to be saved in that job seeker's profile. Though there're probably others and better ways to do that, I thought of inserting in a table (a table associated with the job seeker) data coming from the job post table (having information of being selected by job seeker). The id of the job selected by the job seeker is obtain as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

#================in jinja2 template======================================    {% if session.logged_in %}           <a href="/apply/{{ job.id }}"><btn class="btn btn-primary">Apply</btn></a>         {% endif %} #=======================in the route =================================== @app.route('/apply/<job_id>/', methods=['GET', 'POST']) @login_required def apply_job(job_id):     ...     ... #==========================in the model file=============================== class Job(db.Model):     __tablename__ = 'jobs'      id = db.Column(db.Integer, primary_key=True)     job_name = db.Column(db.Unicode)     job_posted = db.Column(db.DateTime)     category = db.Column(db.Unicode)     job_desc = db.Column(db.Unicode)     def __init__(self, job_name, job_posted, category, job_desc):         self.job_name = job_name         self.job_posted = job_posted         self.category = category         self.job_desc = job_desc

[buran]

In my opinion you should have
one table for advertised jobs, most likely linked to some other tables, e.g. departments
One table for applicants/job seekers. maybe linked to some other tables. Basically this will be their profile - name, contact info, etc.
One table where you will keep info about who interested in what job. One applicant may be interested in more than one job advertisement. Probably linked to other tables. In this table, as minimum you will have the id of the job, id of the participants and then may be some other info. no need to copy anything from jobs table - you will use JOINs to combine info from different tables
of course, variations are possible here and there, or slightly different approach

[firebird]

In my opinion you should have
one table for advertised jobs, most likely linked to some other tables, e.g. departments
One table for applicants/job seekers. maybe linked to some other tables. Basically this will be their profile - name, contact info, etc.
One table where you will keep info about who interested in what job. One applicant may be interested in more than one job advertisement. Probably linked to other tables. In this table, as minimum you will have the id of the job, id of the participants and then may be some other info. no need to copy anything from jobs table - you will use JOINs to combine info from different tables
of course, variations are possible here and there, or slightly different approach

Hello, again thank you for your reply and continuing help. Trying to follow up on the suggestion you made I'm working out the following (the below)models. Please any constructive suggestion is welcome. Thank you.

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

class User(db.Model):#<----Parent table of the Applied class     __tablename__ = 'users'     id = db.Column(db.Integer, primary_key=True)     username = db.Column(db.Unicode)     email = db.Column(db.Unicode)     password = db.Column(db.Unicode)     job_applied = db.relationship('Applied', backref='users')           def __init__(self, username, email, password):         self.username = username         self.email = email         self.password = password           class Job(db.Model):#<---How should I link this table to the other ones?     __tablename__ = 'jobs'     id = db.Column(db.Integer, primary_key=True)     job_name = db.Column(db.Unicode)     job_posted = db.Column(db.DateTime)     category = db.Column(db.Unicode)     job_desc = db.Column(db.Unicode)           def __init__(self, job_name, job_posted, category, job_desc):         self.job_name = job_name         self.job_posted = job_posted         self.category = category         self.job_desc = job_desc   class Applied(db.Model):#<----Child table of User class,     __tablename__ = 'applications'     id = db.Column('id', db.Integer, primary_key=True)     date_applied = db.Column(db.DateTime)     jobseeker_id = db.Column(db.Integer, db.ForeignKey('users.id'))       def __init__(self, date_applied):         self.date_applied = date_applied

[buran]

Not using sqlalchemy myslef, but I think in applications table you need one more field - job_id, to hold id of the job, linked to jobs table, the same way you link jobseeker_id to users

if you have users
1,John
2,Jane

in jobs

1,job1
2,job2

in applications you can have

id,jobseeker_id,job_id
1,1,1
2,2,1
3,1,2

For brevity I omit some of the columns, but basically John has applied for job1 and job2, while Jane has applied only for job1
So when someone apply - you insert their id and the id of the jon they applied for in applications table and that's it - no need to copy anything