0

For example I have my dictionary: my_dict = {'2':'even','1':'odd','3':'odd'} And this is my database:

+---------+--------+ | Numeric | String | +---------+--------+ | 5 | five | | 4 | four | | 3 | three | | 2 | two | | 1 | one | +---------+--------+

In my_dict dictionary,using their keys, I want to get their String value in my database (SQL SERVER) and using their values, sort their String value in this sequence:

odd - even - odd to be liked this

one two three.

And print the output like this: Let's count one two three. Actually I just based from Jisoo's question Querying a list of tuples in Database coz we're quite similar but in mine, is about dictionary.

Improve this question
2
  • Can you elaborate on the intended sorting order? Because ('even', 'odd', 'odd') has no total order, i.e. the order would be ambiguous the way I read it. Commented Mar 6, 2015 at 8:11
  • Oh yes, sorry I didn't realize it that way. Let's just disregard the sequence and sorting instead using their values, display the String values of the keys to be like this two is divisible by 2, one is not divisible by 2, three is not divisible by 2. Commented Mar 6, 2015 at 8:37

1 Answer 1

Reset to default
0

There's two sides to this question: The first one is the SQL query part. That is the simpler one:

Assuming the database table is something like

 CREATE TABLE number (value INTEGER, name STRING);

the SQL is more or less straightforward

 from contextlib import closing

 [...]

 my_dict = {'2':'even','1':'odd','3':'odd'}

 # database connection dbc, see python dbapi
 with closing(dbc.cursor()) as cur:
     cur.execute('SELECT value, name FROM number WHERE value IN (1, 2, 3)')
     for value, name in cur:              
         print "%s is %s" % (name, my_dict[str(value)])

The python problem here is to get the dictionary keys into the SQL IN clause as this is not directly possible with DBAPI2 parameter expansion (see here).

Hence:

 my_dict = {'2':'even','1':'odd','3':'odd'}

 # database connection dbc, see python dbapi
 with closing(dbc.cursor()) as cur:
     stmt = 'SELECT value, name FROM number WHERE id IN (%s)' % ','.join('?' for i in my_dict))
     cur.execute(stmt, my_dict.keys())
     for value, name in cur:              
         print "%s is %s" % (name, my_dict[str(value)])

One also that the '?' is dependent on the paramstyle of the database involved.

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

3 Comments

I'm using odbc's pypyodbc connection sir. Can that be possible?
Sorry, I cannot help you there. Tagging pypyodbc in your question might help, though.
Thank you Sir for giving answer. I think I should try the dbc to see how it works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.