Python Query.execute方法代码示例

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def test_delete():

    query = Query('Planeteers').delete('col1', 'col2', 'col3').where('userID', 'Captain')
    assert query.execute().strip() == "DELETE col1, col2, col3 FROM Planeteers WHERE userID = 'Captain'"

    query = Query('MastersOfTheUniverse').delete().where('mastersID', ('Man-At-Arms', 'Teela'), 'IN')
    assert query.execute().strip() == "DELETE FROM MastersOfTheUniverse WHERE mastersID IN ('Man-At-Arms', 'Teela')"

    query = Query('users').delete('email', 'phone').using(1318452291034).where('user_name', 'jsmith')
    assert query.execute().strip() == "DELETE email, phone FROM users USING TIMESTAMP 1318452291034 " \
                                      "WHERE user_name = 'jsmith'"

    query = Query('SomeTable').delete('col1').where('userID', 'some_key_value')
    assert query.execute().strip() == "DELETE col1 FROM SomeTable WHERE userID = 'some_key_value'"

    query = Query('SomeTable').delete('col1').where('userID', '(key1, key2)', 'IN', True)
    assert query.execute().strip() == "DELETE col1 FROM SomeTable WHERE userID IN (key1, key2)"

    query = Query('users').delete('phone').where('user_name', ('jdoe', 'jsmith'), 'IN')
    assert query.execute().strip() == "DELETE phone FROM users WHERE user_name IN ('jdoe', 'jsmith')"

    query = Query('users').delete("todo ['2012-9-24']").where('user_id', 'frodo')
    assert query.execute().strip() == "DELETE todo ['2012-9-24'] FROM users WHERE user_id = 'frodo'"

    query = Query('users').delete("top_places[3]").where('user_id', 'frodo')
    assert query.execute().strip() == "DELETE top_places[3] FROM users WHERE user_id = 'frodo'"

    query = Query('users').delete("emails").where('user_id', 'frodo')
    assert query.execute().strip() == "DELETE emails FROM users WHERE user_id = 'frodo'"

    query = Query('users').delete("name").where('userID', 'user2')
    assert query.execute().strip() == "DELETE name FROM users WHERE userID = 'user2'"

    """

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def _delete(self):
    Query.execute(
        'DELETE FROM %s WHERE id=%%s' % self.__table__ ,
        self.id,
        self.__db__
    )
    return True

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def main():
    args = docopt(__doc__)
    sql_str = args['SQL'] or sys.stdin.read()
    debug = args['--debug']
    execute = args['--execute']
    random_seed = args['--random-seed']
 
    if debug:
        logging.basicConfig(level=logging.DEBUG)
 
    parsed = parse(sql_str)
    relations, conditions = get_relations_and_conditions(parsed)
    sample_size = parsed.sample_size if parsed.sample_size != '' else None
 
    query = Query(
        relations, 
        conditions=conditions, 
        columns=parsed.column_definitions,
        sample_size=sample_size,
        random_seed=random_seed,
        is_top_level=True
    )
    result = query.execute()
    result_str = result.get_cmd_str(output_column_names=True)
 
    if execute:
        # explicitly use bash instead of the default for subprocess(..., shell=True) which is sh
        result_str = "({})".format(result_str)
        proc = subprocess.Popen(['/bin/bash', '-c', result_str])

    else:
        result_str = result_str + "\n"
        print(result_str, end="")

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def process_query(session, toklist, result):
    """ Check whether the parse tree is describes a query, and if so, execute the query,
        store the query answer in the result dictionary and return True """
    q = Query(session)
    if not q.parse(toklist, result):
        # if Settings.DEBUG:
        #     print("Unable to parse query, error {0}".format(q.error()))
        result["error"] = q.error()
        return False
    if not q.execute():
        # This is a query, but its execution failed for some reason: return the error
        # if Settings.DEBUG:
        #     print("Unable to execute query, error {0}".format(q.error()))
        result["error"] = q.error()
        return True
    # Successful query: return the answer in response
    result["response"] = q.answer()
    # ...and the query type, as a string ('Person', 'Entity', 'Title' etc.)
    result["qtype"] = qt = q.qtype()
    result["key"] = q.key()
    if qt == "Person":
        # For a person query, add an image (if available)
        img = get_image_url(q.key(), enclosing_session=session)
        if img is not None:
            result["image"] = dict(
                src=img.src,
                width=img.width,
                height=img.height,
                link=img.link,
                origin=img.origin,
                name=img.name,
            )
    return True

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def _update(self):
    query = [
        'UPDATE %s SET ' % self.__table__,
        ','.join(['`%s`=%%s'%f for f in self._updated]),
        ' WHERE id=%s '
    ]

    values = [getattr(self, f) for f in self._updated]
    values.append(self.id)

    cursor = Query.execute(' '.join(query), values, self.__db__)

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def test_insert():

    # Insert Test
    uuid = uuid4().hex
    data = dict({'user__uuid': uuid,
                 'fan': 'johndoe'})

    query = Query('NerdMovies', 'Hollywood').insert(data)
    assert query.execute().strip() == "INSERT INTO Hollywood.NerdMovies ( user__uuid, fan ) " \
                                      "VALUES ( " + uuid + ", 'johndoe' )"

    uuid = uuid4().hex
    data = dict({'user__uuid': uuid,
                 'fan': 'johndoe'})

    query = Query('NerdMovies', 'Hollywood').insert(data).ttl(1, 'day')
    assert query.execute().strip() == "INSERT INTO Hollywood.NerdMovies ( user__uuid, fan ) " \
                                      "VALUES ( " + uuid + ", 'johndoe' ) "\
                                      "USING TTL 86400"

    data = {'user_id': 'frodo',
            'first_name': 'Frodo',
            'last_name': 'Baggins',
            'emails': {'[email protected]', '[email protected]'}}
    query = Query('users').insert(data)
    assert query.execute().strip() == "INSERT INTO users ( first_name, last_name, user_id, emails ) "\
                                      "VALUES ( 'Frodo', 'Baggins', 'frodo', {'[email protected]', '[email protected]'} )"

    data = {'user_id': 'frodo',
            'todo': {'2012-10-2 12:10': 'die'}}
    query = Query('users').insert(data)
    assert query.execute().strip() == "INSERT INTO users ( todo, user_id ) "\
                                      "VALUES ( {'2012-10-2 12:10': 'die'}, 'frodo' )"

    data = {'id__uuid': '62c36092-82a1-3a00-93d1-46196ee77204',
            'song_order': 1,
            'song_id__uuid': 'a3e64f8f-bd44-4f28-b8d9-6938726e34d4',
            'title': 'La Grange',
            'artist': 'ZZ Top',
            'album': 'Tres Hombres',
            }
    query = Query('playlists').insert(data)
    assert query.execute().strip() == "INSERT INTO playlists ( id__uuid, album, song_order, artist, title, song_id__uuid ) "\
                                      "VALUES ( 62c36092-82a1-3a00-93d1-46196ee77204, 'Tres Hombres', 1, 'ZZ Top', 'La Grange', "\
                                      "a3e64f8f-bd44-4f28-b8d9-6938726e34d4 )"

    data = {'empid': 104,
            'deptid': 15}
    query = Query('emp').insert(data)
    assert query.execute().strip() == "INSERT INTO emp ( empid, deptid ) VALUES ( 104, 15 )"

    "INSERT INTO playlists (id, song_order, song_id, title, artist, album) VALUES (62c36092-82a1-3a00-93d1-46196ee77204, 1, a3e64f8f-bd44-4f28-b8d9-6938726e34d4, 'La Grange', 'ZZ Top', 'Tres; Hombres')"
    "INSERT INTO playlists (id, song_order, song_id, title, artist, album) VALUES (62c36092-82a1-3a00-93d1-46196ee77204, 2, 8a172618-b121-4136-bb10-f665cfc469eb, 'Moving in Stereo', 'Fu Manchu', 'We Must Obey')"
    "INSERT INTO playlists (id, song_order, song_id, title, artist, album) VALUES (62c36092-82a1-3a00-93d1-46196ee77204, 3, 2b09185b-fb5a-4734-9b56-49077de9edbf, 'Outside Woman Blues', 'Back Door; Slam', 'Roll Away')"
    '''

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
    def __new__(cls, name, bases, attrs):
        base0 = bases[0]
        if base0 is object:
            return super(_Model, cls).__new__(cls, name, bases, attrs)

        new_class = type.__new__(cls, name, bases, attrs)

        new_class.__table__ = table = name
        new_class.__db__ = db = DB.db(table)


        q = Query.execute('SELECT * FROM %s LIMIT 1' % name, (), db)
        new_class.__column__ = column = map(itemgetter0, q.description)

        if base0 is ModelMc:
            new_class.__key__ = '%s&%%s'%(_key_by_table(
                db, name, q
            ).replace('%', '%%'))
        return new_class

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def _insert(self):
    'Uses SQL INSERT to create new record'

    id = self.id
    fields = [
        '`%s`'%f for f in self.__column__
        if id is not None or f != 'id'
    ]
    values = [getattr(self, f, None) for f in self.__column__ if id is not None or f != 'id']


    query = 'INSERT INTO %s (%s) VALUES (%s)' % (
           self.__table__,
           ','.join(fields),
           ','.join(['%s'] * len(fields) )
    )
    cursor = Query.execute(query, values, self.__db__)

    if id is None:
        self.id = cursor.lastrowid

    return True

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def test_update():
    data = {'col1': 'val1',
            'col2': 'val2',}
    query = Query('Movies').update(data).where('movieID', 'key1', '=', True)
    assert query.execute().strip() == "UPDATE Movies SET col2 = val2, col1 = val1 WHERE movieID = key1"

    data = {'user_name': "'bob'"}

    query = Query('clicks', 'excelsior').update(data).where('userid', 'cfd66ccc-d857', '=', True).ttl(432000)
    assert query.execute().strip() == "UPDATE excelsior.clicks USING TTL 432000 SET user_name = 'bob' " \
                                      "WHERE userid = cfd66ccc-d857"

    data = {'total': 'total + 2'}
    query = Query('UserActionCounts').update(data).where('keyalias', 523)
    assert query.execute().strip() == "UPDATE UserActionCounts SET total = total + 2 WHERE keyalias = 523"

    data = {'user_name': "'bob'"}
    query = Query('clicks', 'excelsior').update(data).where('userid', 'cfd66ccc-d857-4e90-b1e5-df98a3d40cd6', '=', True)
    query.where('url', 'http://google.com').ttl(432000)
    assert query.execute().strip() == "UPDATE excelsior.clicks USING TTL 432000 SET user_name = 'bob' " \
                                      "WHERE userid = cfd66ccc-d857-4e90-b1e5-df98a3d40cd6 AND url = 'http://google.com'"

    data = {'col3': 'val3'}
    query = Query('Movies').update(data).where('movieID', '(key1, key2, key3)', 'IN', True)
    assert query.execute().strip() == "UPDATE Movies SET col3 = val3 WHERE movieID IN (key1, key2, key3)"

    data = {'state': "'TX'"}
    query = Query('users').update(data).where('user_uuid', '(88b8fd18-b1ed-4e96-bf79-4280797cba80, '
                                                           '06a8913c-c0d6-477c-937d-6c1b69a95d43, '
                                                           'bc108776-7cb5-477f-917d-869c12dfffa8)', 'IN', True)
    assert query.execute().strip() == "UPDATE users SET state = 'TX' " \
                                      "WHERE user_uuid IN (88b8fd18-b1ed-4e96-bf79-4280797cba80, " \
                                      "06a8913c-c0d6-477c-937d-6c1b69a95d43, bc108776-7cb5-477f-917d-869c12dfffa8)"

    data = {'col4': 22}
    query = Query('Movies').update(data).where('movieID', 'key4', '=', True)
    assert query.execute().strip() == "UPDATE Movies SET col4 = 22 WHERE movieID = key4"



    """

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def _execute(cls, sql, values=()):
    return Query.execute(sql, values, cls.__db__)

# 需要导入模块: from query import Query [as 别名]
# 或者: from query.Query import execute [as 别名]
def test_select():

    query = Query('peers').select()
    assert query.execute().strip() == "SELECT * FROM peers"

    query = Query('users').select().compare('first_name', 'jane').compare('last_name', 'smith', '=')
    assert query.execute().strip() == "SELECT * FROM users WHERE first_name = 'jane' AND last_name = 'smith'"

    query = Query('clicks', 'excelsior').select().ttl('name').compare('url', 'http://apache.org')
    assert query.execute().strip() == "SELECT TTL (name) FROM excelsior.clicks WHERE url = 'http://apache.org'"

    query = Query('clicks', 'excelsior').select().writetime('name').compare('url', 'http://apache.org')
    assert query.execute().strip() == "SELECT WRITETIME (name) FROM excelsior.clicks WHERE url = 'http://apache.org'"

    query = Query('clicks', 'excelsior').select().writetime('date').compare('url', 'http://google.org')
    assert query.execute().strip() == "SELECT WRITETIME (date) FROM excelsior.clicks WHERE url = 'http://google.org'"

    query = Query('users').select().compare('gender', 'f').compare('state', 'TX').compare('birth_year', 1968, '>')
    query.allowFiltering()
    assert query.execute().strip() == "SELECT * FROM users WHERE gender = 'f' AND state = 'TX' AND birth_year > 1968 " \
                                      "ALLOW FILTERING"

    query = Query('test').select().compareToken('k', 42, '>')
    assert query.execute().strip() == "SELECT * FROM test WHERE TOKEN (k) > TOKEN (42)"

    query = Query('myTable').select().compare('t', "maxTimeuuid('2013-01-01 00:05+0000')", '>', True)
    query.compare('t', "minTimeuuid('2013-02-02 10:00+0000')", '<', True)
    assert query.execute().strip() == "SELECT * FROM myTable WHERE t > maxTimeuuid('2013-01-01 00:05+0000') " \
                                      "AND t < minTimeuuid('2013-02-02 10:00+0000')"

    query = Query('People').select('Name', 'Occupation').compare('empID', (199, 200, 207), 'IN')
    assert query.execute().strip() == "SELECT Name, Occupation FROM People WHERE empID IN (199, 200, 207)"

    query = Query('users').select('COUNT(*)')
    assert query.execute().strip() == "SELECT COUNT(*) FROM users"

    query = Query('users').select().count()
    assert query.execute().strip() == "SELECT COUNT(*) FROM users"

    query = Query('big_table').select().count().limit(50000)
    assert query.execute().strip() == "SELECT COUNT(*) FROM big_table LIMIT 50000"

    query = Query('Migrations', 'system').select().count()
    assert query.execute().strip() == "SELECT COUNT(*) FROM system.Migrations"

    query = Query('ruling_stewards').select().compare('king', 'Brego', '=').compare('reign_start', 2450, '>=')
    query.compare('reign_start', 2500, '<').allowFiltering()
    assert query.execute().strip() == "SELECT * FROM ruling_stewards WHERE king = 'Brego' AND reign_start >= 2450 " \
                                      "AND reign_start < 2500 ALLOW FILTERING"

    query = Query('ruling_stewards').select().compare('king', 'none').compare('reign_start', 1500, '>=')
    query.compare('reign_start', 3000, '<').allowFiltering().limit(10)
    assert query.execute().strip() == "SELECT * FROM ruling_stewards WHERE king = 'none' AND reign_start >= 1500 " \
                                      "AND reign_start < 3000 LIMIT 10 ALLOW FILTERING"

    query = Query('periods').select().compareToken('period_name', 'Third Age', '>')
    query.compareToken('period_name', 'Fourth Age', '<')
    assert query.execute().strip() == "SELECT * FROM periods WHERE TOKEN (period_name) > TOKEN ('Third Age') " \
                                      "AND TOKEN (period_name) < TOKEN ('Fourth Age')"

    query = Query('playlists').select().compare('id__uuid', '62c36092-82a1-3a00-93d1-46196ee77204')
    query.limit(50).orderBy('song_order', 'desc')
    assert query.execute().strip() == "SELECT * FROM playlists WHERE id__uuid = 62c36092-82a1-3a00-93d1-46196ee77204 " \
                                      "ORDER BY song_order DESC LIMIT 50"

    query = Query('playlists').select('title').compare( 'artist', 'Fu Manchu')
    assert query.execute().strip() == "SELECT title FROM playlists WHERE artist = 'Fu Manchu'"

    query = Query('People').select()
    assert query.execute().strip() == "SELECT * FROM People"


    """