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.

Community
  • 1
  • 1
  • 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. – dhke Mar 06 '15 at 08: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`. –  Mar 06 '15 at 08:37

1 Answers1

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.

dhke
  • 15,008
  • 2
  • 39
  • 56
  • I'm using odbc's `pypyodbc` connection sir. Can that be possible? –  Mar 06 '15 at 09:16
  • Sorry, I cannot help you there. Tagging `pypyodbc` in your question might help, though. – dhke Mar 06 '15 at 09:20
  • Thank you Sir for giving answer. I think I should try the dbc to see how it works. –  Mar 06 '15 at 09:37