-1

Since I am new to python programming, I don’t know more about querying a data from a database. First, I’ve created my database in SQL Server, with 2 columns: The String column and the Numeric column.

+--------+---------+ | String | Numeric | +--------+---------+ | One | 1 | | Five | 5 | | Three | 3 | | Seven | 7 | | Eight | 8 | | Two | 2 | +--------+---------+

For example:

X = [(‘three’,’odd’), (‘one’,’odd’), (‘two’,’even’)]

Now I want that the output follow this sequence: odd – even – odd so it will be like this 1 2 3. +-----+------+-----+ | Odd | Even | Odd | +-----+------+-----+ | 1 | 2 | 3 | +-----+------+-----+

How to query the numeric value of the string based on if odd or even the string is in database?

Jisoo
  • 1
  • 2

1 Answers1

0

Your query is not clear for me. You mix SQL data structures and Python data structures and it is not clear if this will be called from Python or from SQL. Your query should contain some code you are trying to execute.

With such a simple data in your SQL tables you can think of SQL tables as of Python dictionaries. NUMSTR dict represents you first SQL table, and I change X list of tuples into dict:

NUMSTR = {1: 'one', 2: 'two', 3: 'three'}
X = {'three': 'odd', 'one': 'odd', 'two': 'even'}

def show_even_odd(in_numbers):
    numbers_info = []
    for i in in_numbers:
        try:
            num_name = NUMSTR[i]
            eo = X[num_name]
        except KeyError:
            eo = '???'
        numbers_info.append(eo)
    print(' | '.join(['%4s' % x for x in numbers_info]))
    print(' | '.join(['%4s' % x for x in in_numbers]))


def test():
    show_even_odd([1, 2, 3])

I stored data in numbers_info to display it in one line. It would be easier to show info about each number in separate line.

EDIT

If your problem is to get first value of each tuple and display its numeric value then such code looks like:

X = [('three','odd'), ('one','odd'), ('two','even')]
for nn, eo in X:
    print('%s - %s' % (get_number(nn), eo))

Now you must define get_number() function. With global cursor this may work:

def get_number(number_name):
    result = number_name
    cursor.execute('SELECT numeric FROM my_table WHERE String = ?', number_name)
    for txt in cursor.fetchall():
        result = txt[0]
    return result

PS In this code I used ? in SELECT to make prepared statement. It should be replaced with number_name by ODBC driver. Such operation can be done by Python: "SELECT ... WHERE String = '%s'" % (number_name), but prepared statements are much better. They prevent against SQL Injection and database can better cache query plan for such statements.

Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
  • Sorry sir, maybe I gave a wrong example. I am using SQL Server as my database and Im using pypyodbc to connect python into my database so data will be retrieved from SQL. I didnt use the dictionary of python. From my example above, `X = [(‘three’,’odd’), (‘one’,’odd’), (‘two’,’even’)]`, I want to access the first element of each tuples, and display their `numeric` value. Let's just say that the datatype of `numeric` column is `varchar`. – Jisoo Mar 07 '15 at 02:29
  • Try to make some code that shows your problem. It is easier to work on examples. Also divide your big problem into smaller problems, example: "1. how to get first element of each tuple?", "2. how to make prepared query to SQL database?", "3. how to print firsts elements of tuples in one line, and seconds in second line?" – Michał Niklas Mar 10 '15 at 09:26