0

I'm totally new to working with databases in python. However, I made it to query data (which still doesn't exist but I got the table-column names) from my DB in python using ipython-sql. No I tried to use this to also push values into my database like this:

for hero in heroes:

    h_id = hero[0]
    h_name = hero[1]
    h_system_name = hero[2]

    %%sql

    INSERT INTO heroes (hid, name, system_name) VALUES (hero[0], hero[1], hero[2])

However, when running this code I get an error telling me there is an error at the end of INTO. So in order to find out how to solve this I googled a bit and I'm totally confused how to approach this properly. Coming from Java I'm used to simply use a JDBC driver and everything works within seconds but it seems in Python there are tons of different DB connector modules. I installed by now ipython-sql, sqlalchemy, psycopg2 and pyodbc and all of them seem to work a bit differently. As pyodbc reminded me of JDBC by its name I wanted to try this one but it returns me this error:

'IM002', '[IM002] [Microsoft][ODBC Driver Manager] The data source name was not found and no default driver was specified (0) (SQLDriverConnect)'

I have already read that there is (or should be) something called a DSN file but I seriously don't have a clue where this is located or should be located. Running odbcinst -j as suggested here doesn't work as my command line returns the following:

The command "odbcinst" is either misspelled or could not be found.

Perhaps this is a command only executable under Linux systems, I don't know, but I tried to search for pyodbc in my appadata folder where python packages seem to be installed and also found pyodbc.py but I don't know where in these thousands of subfolders I should look in order to find the DSN file.

Also, if I finally found that file, what am I supposed to do with it? Where do I need to place it as it obviously isn't located where it should be located right now? And what does this file need to consist of at least?

Is there any easier option to get done what I want to achieve? And if so, could someone please point me towards it as I feel as if I was drowning in an increasing amount of modules to choose from - which clearly doesn't help in being less confused.

Currently my code using pyodbc looks like this:

import pyodbc

conn = pyodbc.connect(
    "DRIVER={PostgreSQL Unicode(x64)};"
    "DATABASE=DotA_Enhanced_Data;"
    "UID=postgres;"
    "PWD=xxxxxxxxx;"
    "SERVER=localhost;"
    "PORT=55917;")

cursor = conn.cursor()

for hero in heroes:

    h_id = hero[0]
    h_name = hero[1]
    h_system_name = hero[2]

    cursor.execute('''INSERT INTO heroes (hid, name, system_name) values (hero[0], hero[1], hero[2])''')


conn.commit()

I tried adapting my connection string to the answer of this SO article. I tried their driver string as well as the one currently included in the query which originates from this reddit post.

I'd be very very happy if there was anyone who could give me some insight into this confusing DB-connection hell that Python currently seems to be for me. Thanks in advance for any help and let me know if you need any additional information.

Samaranth
  • 385
  • 3
  • 16
  • `%%sql` isn't Python. Are you working in a Jupyter notebook or something? – ChrisGPT was on strike Dec 14 '20 at 23:53
  • And you might have _installed_ SQLAlchemy and Psycopg2, but I don't see them being used here. It looks like you're using ODBC. It's probably better to remove those tags, and any reference to them, and replace with a relevant Jupyter / IPython tag. – ChrisGPT was on strike Dec 14 '20 at 23:54
  • Yes, currently I'm working in Jupyter notebook in order to setup the data queries for the final .py file in which I'm merging all smaller functions previously tested in Jupyter notebook. I'm editing the .py file in Spyder. – Samaranth Dec 14 '20 at 23:57
  • 1
    If you're not going to use Jupyter in "the final .py file" you probably shouldn't be using Jupyter magic `%%sql` stuff. It isn't legal in Python. – ChrisGPT was on strike Dec 14 '20 at 23:58
  • I already figured moving to something like `pyodbc` as the better solution also because I'm getting the mentioned error when running the `INSERT INTO` statement. However, this is also where the problems begin as I don't really know where to start from here because there are many pages suggesting different modules and approaches. – Samaranth Dec 15 '20 at 00:02
  • 1
    This has a lot wrong with it. Working of your final code example: 1) Where is `heroes` defined and how? 2) Either use '"' or '""""' not three single quotes. 3) Use parameters for the values being passed in as `hero[0]` will not work. Furthermore you already set that equal to `h_id` so you are duplicating yourself. 4) Why use ODBC when you can use psycopg2? – Adrian Klaver Dec 15 '20 at 00:04
  • @AdrianKlaver I'm using it for two reasons: 1.) I'm totally confused by all these modules so pyodbc at least sounded familiar coming from JDBC and 2.) because it threw me errors like "module not defined" and stuff but apparently now it's working. Only need to figure out now how to properly pass the values into the query. – Samaranth Dec 15 '20 at 00:13
  • Just `pip install psycopg2-binary`. Then you have access to [Docs](https://www.psycopg.org/docs/) and people who will answer your questions. ODBC is just another layer of headaches. If you insist on using it then [Cursor](https://github.com/mkleehammer/pyodbc/wiki/Cursor) – Adrian Klaver Dec 15 '20 at 00:56

0 Answers0