1

I'm trying to populate a couple databases with psycopg2 within a server I am not the root user of (don't know if it's relevant or not). My code looks like

import json
from  psycopg2 import connect

cors = connect(user='jungal01', dbname='course')
req = connect(user="jungal01", dbname='requirement')

core = cors.cursor()
reqs = req.cursor()

with open('gened.json') as gens:
    geneds = json.load(gens)

for i in range(len(geneds)):
    core.execute('''insert into course (number, description, title)
                    values({0}, {1}, {2});''' .format(geneds[i]["number"], geneds[i]['description'], geneds[i]['title'] ))

reqs.execute('''insert into requirement (fulfills)
                values({0});''' .format(geneds[i]['fulfills'] ))
db.commit()

when I execute the code, I get the above pycopg2 error. I know that these particular databases exist, but I just can't figure out why it won't connect to my databases. (side quest, I am also unsure about that commit statement. Should it be in the for loop, or outside of it? It suppose to be database specific?)

Allen
  • 29
  • 1
  • 2
  • 10
  • `insert into course`? That'd be a **table**, not a **database** – OneCricketeer Apr 25 '17 at 01:32
  • Also read about SQL injection and how to properly use `execute` and "parameter binding".. Hint: `format` isn't necessary – OneCricketeer Apr 25 '17 at 01:33
  • @cricket_007 in postgres, tables are databases. Schema hold db's, but they aren't particularly necessary – Allen Apr 25 '17 at 01:39
  • But your error is clearly "database does not exist". Can you connect using `psql`, and edit your question with the `\list` output? – OneCricketeer Apr 25 '17 at 01:40
  • In other words, show some sample queries that work in `psql` that you'd like to convert to Python, and we can work from there – OneCricketeer Apr 25 '17 at 01:41
  • well at the moment, my databases are empty. This file is specifically for putting data into the db's. But I guess for a specific example in `psq`, I need something like `INSERT INTO course(number, description, title) VALUES('DAN 100', 'Intensive research on the history of dance', 'Dance History');` – Allen Apr 25 '17 at 01:54
  • You have to create a database and a table before you can insert data. Please show the output of a `\list`, as stated. – OneCricketeer Apr 25 '17 at 01:57
  • You need to run `createdb`, and you connect to **that** from python. You can use one database with many tables. https://www.postgresql.org/docs/9.6/static/tutorial-createdb.html – OneCricketeer Apr 25 '17 at 02:00
  • `\list` on this server is a bunch of databases full of usernames, of which my username is one – Allen Apr 25 '17 at 02:06
  • Alright, then `dbname='your_username'` is what you should do. Then, you still probably need to run a `CREATE TABLE` there if you haven't already – OneCricketeer Apr 25 '17 at 02:10

3 Answers3

2

Allen, you said: "in postgres, tables are databases." That's wrong. Your error message results from this misunderstanding. You want to connect to a database, and insert into a table that exists in that database. You're trying to insert into a database -- a nonsensical operation.

Chris Johnson
  • 20,650
  • 6
  • 81
  • 80
2

First, you have db is not a defined variable, so you code shouldn't run completely anyway.

\list on this server is a bunch of databases full of usernames, of which my username is one

Then the following is how you should connect. To a database, not a table, and the regular pattern is to put the database name, and then the user/pass.

A "schema" is a loose term in relational database. Both tables and databases have schemas, but you seem to be expecting to connect to a table, not a database.

So, try this code with an attempt at fixing your indentation and SQL injection problem -- See this documentation

Note that you first must have created the two tables in the database you are connecting to. 

import json
from  psycopg2 import connect

username = 'jungal01'
conn = connect(dbname=username, user=username)
cur = conn.cursor()

with open('gened.json') as gens:
    geneds = json.load(gens)

    for g in geneds:
        cur.execute('''insert into course (number, description, title)
                        values(%(number)s, %(description)s, %(title)s);''', g)

        cur.execute('''insert into requirement (fulfills)
                    values(%(fulfills)s);''', g)
    conn.commit()
OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
  • You've been such a great help with this. I'm entirely new to this world of sql injections etc. This code got me out of the initial problem, but psycopg2 says there is a syntax error on the first comma at values(?, ?, ?); – Allen Apr 25 '17 at 03:26
  • I think the question marks are the problem. http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries – OneCricketeer Apr 25 '17 at 03:30
  • You are the best! Thank you so much! – Allen Apr 25 '17 at 03:53
0

Make sure you are giving the catalog name as database name and not the schema's under catalog.

Catalog is confusing and quite unnecessary. More details below: What's the difference between a catalog and a schema in a relational database?

Mohan Krishna Yarramsetty
  • 1
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Antimony Nov 14 '17 at 22:41