def user_from_table(connection: sqlite3.Connection, username: str) -> User:
    """ Функция возвращает пользователя по его имени

    аргументы:

    connection -- соединение с базой данных
    username -- имя пользователя
    """
    try:
        if connection is None:
            raise WrongDataBaseNameException(
                'Неправильное название базы данных.')
        cursor = connection.cursor()
        cursor.execute("""SELECT username FROM users WHERE username = ?""",
                       (username, ))
        if cursor.fetchall() is None:
            return None
        cursor.execute(
            """SELECT username, password FROM "users"
                          WHERE username = ?""", (username, ))
        try:
            data = cursor.fetchall()[0]
        except IndexError as e:
            print(e)
            return None
        return data

    except sqlite3.Error as e:
        print(e)
    finally:
        connection.close()
Example #2
0
 def fetch_unprocessed(self):
     """Returns one unprocessed row in a tuple(id, a, b, c, d, e, f, g, h)
     and set its status to 'processing'
     """
     connect = Connection(self.dbname)
     cursor = connect.cursor()
     cursor.execute("""
     SELECT
         *
     FROM
         'data'
     WHERE
         status = 'unprocessed'
         LIMIT 1
     """)
     unprocessed_row = cursor.fetchone()
     if unprocessed_row:
         cursor.execute(
             """
         UPDATE
             data
         SET
             status = 'processing'
         WHERE
             id = :id
         """,
             {'id': unprocessed_row[0]},
         )
     connect.commit()
     connect.close()
     if unprocessed_row:
         return unprocessed_row[:9]
Example #3
0
def show_all_created_tables_content(connection:sqlite3.Connection) -> None:
    """ Функция выводит список всех созданных таблиц с их содержанием

    аргумент:
    connection -- соединение с базой данных
    """
    try:
        if connection is None:
            raise WrongDataBaseNameException('Неправильное название базы данных. Введите существующую')
        cursor = connection.cursor()
        cursor.execute("""SELECT name FROM sqlite_master
                        WHERE type='table'""")
        tables_name = [table[0] for table in cursor.fetchall()]
        print("Список всех созданных таблиц и их содержание:")
        for table in tables_name:
            cursor.execute(f"""SELECT * FROM {table}""")
            print(table)
            for e, content in enumerate(cursor.fetchall()):
                print('  ', e+1, ' -- ', sep='', end='')
                print(*content, sep=', ')
        connection.commit()
    except sqlite3.Error as e:
        print(e)
    finally:
        connection.close()
Example #4
0
    def _update_dispersy(self):
        """
        Cleans up all SearchCommunity and MetadataCommunity stuff in dispersy database.
        """
        db_path = os.path.join(self.state_dir, u"sqlite", u"dispersy.db")
        if not os.path.isfile(db_path):
            return

        communities_to_delete = (u"SearchCommunity", u"MetadataCommunity")

        connection = Connection(db_path)
        cursor = connection.cursor()

        data_updated = False
        for community in communities_to_delete:
            try:
                result = list(cursor.execute(u"SELECT id FROM community WHERE classification == ?", (community,)))

                for community_id, in result:
                    self._logger.info(u"deleting all data for community %s...", community_id)
                    cursor.execute(u"DELETE FROM community WHERE id == ?", (community_id,))
                    cursor.execute(u"DELETE FROM meta_message WHERE community == ?", (community_id,))
                    cursor.execute(u"DELETE FROM sync WHERE community == ?", (community_id,))
                    data_updated = True
            except StopIteration:
                continue

        if data_updated:
            connection.commit()
        cursor.close()
        connection.close()
Example #5
0
def edit_info_in_table(connection:sqlite3.Connection,
                       table_name:str, _id:int, data:dict) -> None:
    """ Функция изменяет нужные данные в таблице

    аргументы:
    connection -- соединение с базой данных
    table_name -- название таблицы
    _id -- идентификационный номер записи в таблице
    data -- данные для изменения
    """
    try:
        if connection is None:
            raise WrongDataBaseNameException('Неправильное название базы данных. Введите существующую')
        if type(table_name) is not str:
            raise TypeError('Название таблицы может быть только строковое')
        if type(_id) is not int:
            raise TypeError('Идентификатор записи в таблице должен быть числом')
        if type(data) is not dict:
            raise TypeError('данные должны быть в формате словаря')
        cursor = connection.cursor()
        cursor.execute(f"""UPDATE {table_name.lower()}
                           SET {', '.join(f'{e} = "{b}"' for e,b in data.items())}
                           WHERE id = ?""", (_id,))
        connection.commit()
    except sqlite3.Error as e:
        print(e)
    finally:
        connection.close()
Example #6
0
def save_crashstats_packages(statsid: int,
                             packages: List[str],
                             con: sqlite3.Connection = None) -> None:
    close = False
    if con is None:
        con = init_crashstats_db()
        close = True

    query = con.cursor()
    for package in packages:
        pkgdata = parse_rpm_name(package)
        if pkgdata["name"] is None:
            continue

        ver = "%s-%s" % (pkgdata["version"], pkgdata["release"])
        query.execute("SELECT id FROM packages WHERE name = ? AND version = ?",
                      (pkgdata["name"], ver))
        row = query.fetchone()
        if row:
            pkgid = row[0]
        else:
            query.execute("INSERT INTO packages (name, version) VALUES (?, ?)",
                          (pkgdata["name"], ver))
            pkgid = query.lastrowid

        query.execute(
            """
          INSERT INTO packages_tasks (taskid, pkgid) VALUES (?, ?)
          """, (statsid, pkgid))

    con.commit()
    if close:
        con.close()
Example #7
0
    def _update_dispersy(self):
        """
        Cleans up all SearchCommunity and MetadataCommunity stuff in dispersy database.
        """
        db_path = os.path.join(self.session.get_state_dir(), u"sqlite", u"dispersy.db")
        if not os.path.isfile(db_path):
            return

        communities_to_delete = (u"SearchCommunity", u"MetadataCommunity")

        connection = Connection(db_path)
        cursor = connection.cursor()

        data_updated = False
        for community in communities_to_delete:
            try:
                result = list(cursor.execute(u"SELECT id FROM community WHERE classification == ?", (community,)))

                for community_id, in result:
                    self._logger.info(u"deleting all data for community %s...", community_id)
                    cursor.execute(u"DELETE FROM community WHERE id == ?", (community_id,))
                    cursor.execute(u"DELETE FROM meta_message WHERE community == ?", (community_id,))
                    cursor.execute(u"DELETE FROM sync WHERE community == ?", (community_id,))
                    data_updated = True
            except StopIteration:
                continue

        if data_updated:
            connection.commit()
        cursor.close()
        connection.close()
Example #8
0
    def _purge_old_search_metadata_communities(self):
        """
        Cleans up all SearchCommunity and MetadataCommunity stuff in dispersy database.
        """
        db_path = os.path.join(self.session.get_state_dir(), u"sqlite", u"dispersy.db")
        if not os.path.isfile(db_path):
            return

        communities_to_delete = (u"SearchCommunity", u"MetadataCommunity", u"TunnelCommunity")

        connection = Connection(db_path)
        cursor = connection.cursor()

        for community in communities_to_delete:
            try:
                result = list(cursor.execute(u"SELECT id FROM community WHERE classification == ?;", (community,)))

                for community_id, in result:
                    cursor.execute(u"DELETE FROM community WHERE id == ?;", (community_id,))
                    cursor.execute(u"DELETE FROM meta_message WHERE community == ?;", (community_id,))
                    cursor.execute(u"DELETE FROM sync WHERE community == ?;", (community_id,))
            except StopIteration:
                continue

        cursor.close()
        connection.commit()
        connection.close()
Example #9
0
def selectByDateRange(prec_no, block_no, fromDate, toDate):
    __mylogger.info('selectByDateRange start!')
    
    try:
        #dbのオープン
        db = Connection(dbFilePath)
        
        #データ取得
        statement = "SELECT * FROM WeatherInfo WHERE"
        statement = statement + " date BETWEEN '{fromDate}' and '{toDate}' ".format(fromDate=fromDate, toDate=toDate)
        result = np.array([])
        for item in db.cursor().execute(statement):
            if len(result) > 0:
                result = np.vstack([result, item])
            else:
                result = np.array(item)
            #result.append(item)
        return result
    
    except Exception as ex:
        __mylogger.error('error has occured.')
        __mylogger.error(ex)
        if db != None:
            db.rollback()
    finally:
        if db != None:
            db.close()
        __mylogger.info('selectByDateRange ended')
Example #10
0
def main(connection: Connection):
    user, last_year, last_month = get_starting_user(connection)
    while user:
        print(f'Consuming {user}...')

        try:
            with connection:
                connection.execute('update users set started = 1 where id = ?',
                                   [user])
        except Exception as e:
            print(e)
            sys.exit(1)

        today = datetime.today()
        starting_year = last_year or 1999
        for year in range(starting_year, today.year + 1):
            starting_month = 1 if (year > last_year
                                   or not last_month) else last_month
            for month in range(starting_month, 13):
                if year == today.year and month > today.month:
                    break
                print(f'Looking for {user} in {year}-{month}...')

                games = reversed(get_games(user, year, month))
                for game in games:
                    add_user(connection, game.black)
                    add_user(connection, game.white)

                    if game.id:
                        add_game(connection, game)

                update_user_progress(connection, user, year, month)

                cursor = connection.cursor()
                cursor.execute('select count(*) from games')
                count = cursor.fetchone()[0]
                print(
                    f'Finished {year}-{month}.  There are now {count} games.\n'
                )
                print()

        count = count_games(connection, user)
        print(
            f'Finished consuming {user}.  This user has {count} viewable games.'
        )

        try:
            with connection:
                connection.execute(
                    'update users set finished = 1 where id = ?', [user])
        except Exception as e:
            print(e)
            sys.exit(1)

        user, last_year, last_month = get_starting_user(connection)

    connection.close()

    print('Done!')
Example #11
0
def _close_db(conn: sqlite3.Connection) -> None:
    """Close a database.

    This is split into a separate function for the benefit of testing, because
    the close member of the object itself is read-only and hence cannot be
    patched.
    """
    conn.close()
Example #12
0
class Graph(object):
    """
    Initializes a new Graph object.

    :param uri: The URI of the SQLite db.
    :param graphs: Graphs to create.
    """
    def __init__(self, uri, graphs=()):
        self.uri = uri
        self.db = Connection(database=uri)
        self.setup_sql(graphs)

    def setup_sql(self, graphs):
        """
        Sets up the SQL tables for the graph object,
        and creates indexes as well.

        :param graphs: The graphs to create.
        """
        with closing(self.db.cursor()) as cursor:
            for table in graphs:
                cursor.execute(SQL.CREATE_TABLE % (table))
                for index in SQL.INDEXES:
                    cursor.execute(index % (table))
            self.db.commit()

    def close(self):
        """
        Close the SQLite connection.
        """
        self.db.close()

    __del__ = close

    def __contains__(self, edge):
        """
        Checks if an edge exists within the database
        with the given source and destination nodes.

        :param edge: The edge to query.
        """
        with closing(self.db.cursor()) as cursor:
            cursor.execute(*SQL.select_one(edge.src, edge.rel, edge.dst))
            return bool(cursor.fetchall())

    def find(self, edge_query):
        """
        Returns a Query object that acts on the graph.
        """
        return Query(self.db)(edge_query)

    def transaction(self):
        """
        Returns a Transaction object. All modifying
        operations, i.e. ``store``, ``delete`` must
        then be performed on the transaction object.
        """
        return Transaction(self.db)
Example #13
0
def importCsv(csvName):
    __mylogger.info('importing start!')
    
    try:
        #csvファイル読み込み
        df = pd.read_csv(csvName, encoding='utf-8', parse_dates=True, header=0)
        
        #INTEGERの精度変更 objectにする
        #なぜかint64のままだとsqliteにBLOBとして登録される。int8やint32に変更すると文字化けしてしまう。
        #objectにすると自動判定でintと判断されるようである。
        df['県番号'] = df['県番号'].astype('object')
        df['地区番号'] = df['地区番号'].astype('object')
        df['時刻'] = df['時刻'].astype('object')
        df['湿度(%)'] = df['湿度(%)'].astype('object')
        
        '''
        #欠損値の補完
        def __conv00(val):
            if val == '--' or val == None:
                return 0.0
            else:
                return val
        df['降水量'] = df['降水量'].apply(__conv00)
        df['日照時間(h)'] = df['日照時間(h)'].fillna(0.0)
        df['全天日射量(MJ/m2)'] = df['全天日射量(MJ/m2)'].fillna(0.0)
        df['降雪(cm)'] = df['降雪(cm)'].fillna(0.0)
        df['降雪(cm)'] = df['降雪(cm)'].apply(__conv00)
        df['積雪(cm)'] = df['積雪(cm)'].fillna(0.0)
        df['積雪(cm)'] = df['積雪(cm)'].apply(__conv00)
        df['天気'] = df['天気'].fillna('')
        df['雨雲'] = df['雨雲'].fillna('')
        df['視程(km)'] = df['視程(km)'].fillna(0.0)
        '''
        
        #dbのオープン
        db = Connection(dbFilePath)
        
        #データ一括登録
        for idx in range(0, len(df)):
            __mylogger.debug("\n" + str(df.iloc[idx]))
            statement = "INSERT INTO WeatherInfo VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "
            try:
                db.cursor().execute(statement, df.iloc[idx])
            except DatabaseError as insertEx:
                __mylogger.error('error has occured.')
                __mylogger.error(insertEx)
        
        db.commit()
        
    except Exception as ex:
        __mylogger.error('error has occured.')
        __mylogger.error(ex)
        if db != None:
            db.rollback()
    finally:
        if db != None:
            db.close()
        __mylogger.info('importing ended')
Example #14
0
def close_db(db: sqlite3.Connection) -> None:
    """Close the database.

    Args:
        db:  Database (:class:`sqlite3.Connection`)

    Returns:
        None
    """
    db.close()
Example #15
0
def close_db(connection: sqlite3.Connection) -> bool:
    """Closes the provided db connection object

    :param connection: [description]
    :type connection: sqlite3.Connection
    :return: [description]
    :rtype: bool
    """
    connection.close()
    return True
Example #16
0
def make_memory_database(design_names, design_data,
                         out_connection: Connection):
    # Table idents: 5 is enough to stitch the entire ward output together with the hypercube designs
    # These should ensure third normal form, although I haven't checked this in detail
    design_table_name: str = "design_table"
    output_table_name: str = "output_table"
    day_table_name: str = "day_table"
    run_table_name: str = "run_table"

    key_column = design_names[0]

    design_table_schema = make_design_table_schema(key_column,
                                                   design_names[1:],
                                                   design_table_name)
    output_channel_schema: str = f"create table {output_table_name} (id integer not null primary key, name text);"
    day_table_schema: str = f"create table {day_table_name}(day integer not null primary key,date text not null);"
    run_table_schema: str = f"create table {run_table_name}(run_index integer not null primary key," \
                            f"design_index integer not null,end_day integer not null," \
                            f"mw_folder text not null," \
                            f"foreign key (design_index) references {design_table_name}({key_column}));"

    try:

        # Create in memory first (small database, + we want to ensure it is well-formed)
        database = connect(":memory:")
        cursor: Cursor = database.cursor()

        # Flags / pragmas
        cursor.execute(f'PRAGMA encoding = "UTF-8";')
        cursor.execute(f"PRAGMA foreign_keys = ON;")

        # Create table structure
        cursor.execute(design_table_schema)
        cursor.execute(output_channel_schema)
        cursor.execute(day_table_schema)
        cursor.execute(run_table_schema)

        # Global writes

        # Write the design table
        for i, row in enumerate(design_data):
            vals = tuple([i] + row[:-1])
            cursor.execute(
                f"insert into design_table ({','.join(design_names)}) "
                f"values ({','.join(['?'] * len(design_names))})", vals)
        database.commit()

        # Now save to disk
        database.backup(out_connection)

    finally:
        if database:
            database.close()
        if out_connection:
            out_connection.close()
Example #17
0
    async def release(self, conn: sqlite3.Connection):
        """
        Releases a connection back to the pool of available connections.
        """
        async with threadpool():
            # rollback anything stale left in the DB
            if conn.in_transaction:
                conn.close()
                conn = self._new_connection()

        self.queue.put_nowait(conn)
Example #18
0
    def __close_connection(self, cur: sqlite3.Cursor,
                           conn: sqlite3.Connection):
        if cur:
            cur.close()
        else:
            print("Warning: No cursor defined.")

        if conn:
            conn.close()
        else:
            print("Warning: No connection defined.")
Example #19
0
def close(con: sqlite3.Connection) -> None:
    """
    Closes the connection to the sqlite db.

    Parameters
    ----------
    con : sqlite3.Connection
        The connection to the db.
    """
    con.commit()
    con.close()
Example #20
0
    def close_db(self, db_connection: sqlite3.Connection):
        """Committing and closing the database.

        :param db_connection: Object - Connection to the database.
        """

        try:
            db_connection.close()
        except sqlite3.Error as e:
            raise Exception(
                f'An error occurred while accessing the service "Closing the database": {e}'
            )
Example #21
0
def add_to_db(articles: list):
    connect = Connection(DB_FILENAME)
    cursor = connect.cursor()
    for article in articles:
        # print(article)
        values_string = '"' + '", "'.join(
            article) + '"'  # генеруємо рядок для запиту
        query = 'INSERT INTO articles ' \
                f'VALUES ({values_string})'
        cursor.execute(query)
    connect.commit()
    connect.close()
Example #22
0
def __isExistsDb():
    if os.path.exists(dbFilePath) == False:
        return False
    
    db = None
    try:
        db = Connection(dbFilePath)
        return True
    except Exception as ex:
        return False
    finally:
        if db != None:
            db.close()
Example #23
0
def create_schema(conn: Connection):
    """ Creates the main database schema. It should only be called if it is the first time the DB is created """
    try:
        c = conn.cursor()
        for query in __schema:
            c.execute(query)
        conn.commit()
        c.close()
    except Error as e:
        print(e)
        logging.critical('Could not create the database schema')
        conn.close()
        sys.exit(-1)
Example #24
0
 def _finalize_instance(
     cls: typing.Type["CacheDict[KT, VT]"],
     *,
     self_repr: str,
     conn: sqlite3.Connection,
 ) -> None:
     log.info("_finalize_instance [%s] conn: [%r]", self_repr, conn)
     try:
         conn.close()
     except Exception:  # pragma: no cover
         # We don't really care what the exception is as we cannot do
         # anything about it. If it's rethrown it will just be output
         # to stderr
         log.error("exception when closing conn: [%r]", conn, exc_info=True)
Example #25
0
def mutator(mac):

    #category of some common variations, default is "variations on first byte" seen later
    category = {
        0: 'Registered OUI',
        1: 'Second nibble - 2',
        2: 'Second nibble - 6',
        3: 'Second nibble - 10'
    }

    #macvendors database taken from oui.txt from IEEE
    OUI_CON = Connection('./databases/macvendors.db')
    OUI_CUR = OUI_CON.cursor()

    #strip down the input mac or oui to just 6 hex digits, in text, uppercased
    oui = mac.replace(':', '').replace('-', '').replace('.', '').upper()[0:6]

    #pull first and second nibbles
    n1 = int(oui[0], 16) * 16
    n2 = int(oui[1], 16)

    #define all possible byte variations for later matching
    bytes = [n1 + n2, (n1 + n2) - 2, (n1 + n2) - 6, (n1 + n2) - 10,\
      n2 - 2, (0x10 + n2) - 2, (0x20 + n2) - 2, (0x30 + n2) - 2,\
      (0x40 + n2) - 2, (0x50 + n2) - 2, (0x60 + n2) - 2,
      (0x70 + n2) - 2, (0x80 + n2) - 2, (0x90 + n2) - 2,\
      (0xa0 + n2) - 2, (0xb0 + n2) - 2, (0xc0 + n2) - 2,\
      (0xd0 + n2) - 2, (0xe0 + n2) - 2, (0xf0 + n2) - 2]
    print bytes

    #uppercase and convert to hex strings and add back the rest of the oui, padd back leading zeroes
    bytes = [(hex(byte)[2:].upper() + oui[2:]).zfill(6) for byte in bytes]
    print bytes

    #look for all of them in the database using enumerate
    #using enumerate will keep a running tab of the current index to be
    #able to use the category dictionary above without if statements
    for index, byte in enumerate(bytes):
        OUI_CUR.execute(
            'SELECT vendor FROM macvendors WHERE mac="{}";'.format(byte))
        hit = OUI_CUR.fetchone()
        if hit != None:
            result, oui_match = hit[0].rstrip(), byte
            OUI_CON.close()
            return (oui, oui_match, result,
                    category.get(index, 'Variations on first byte')
                    )  #default category

    return (oui, 'None', 'No Vendor Found', 'Unknown - Possibly random')
Example #26
0
 def insert_parsed_data(self, parsed_data, primary_key):
     """Insert parsed data into row with given promary key
     """
     connect = Connection(self.dbname)
     cursor = connect.cursor()
     cursor.execute(
         """
     UPDATE
         data
     SET
         top_tube_min = :top_tube_min,
         top_tube_max = :top_tube_max,
         seat_tube_cc_min = :seat_tube_cc_min,
         seat_tube_cc_max = :seat_tube_cc_max,
         seat_tube_ct_min = :seat_tube_ct_min,
         seat_tube_ct_max = :seat_tube_ct_max,
         stem_min = :stem_min,
         stem_max = :stem_max,
         bb_saddle_min = :bb_saddle_min,
         bb_saddle_max = :bb_saddle_max,
         saddle_handlebar_min = :saddle_handlebar_min,
         saddle_handlebar_max = :saddle_handlebar_max,
         saddle_setback_min = :saddle_setback_min,
         saddle_setback_max = :saddle_setback_max,
         seatpost_setback = :seatpost_setback,
         status = :status
     WHERE
         id = :id
     """, {
             'top_tube_min': parsed_data['Top Tube'][0],
             'top_tube_max': parsed_data['Top Tube'][1],
             'seat_tube_cc_min': parsed_data['Seat Tube Range CC'][0],
             'seat_tube_cc_max': parsed_data['Seat Tube Range CC'][1],
             'seat_tube_ct_min': parsed_data['Seat Tube Range CT'][0],
             'seat_tube_ct_max': parsed_data['Seat Tube Range CT'][1],
             'stem_min': parsed_data['Stem Length'][0],
             'stem_max': parsed_data['Stem Length'][1],
             'bb_saddle_min': parsed_data['BB Saddle Position'][0],
             'bb_saddle_max': parsed_data['BB Saddle Position'][1],
             'saddle_handlebar_min': parsed_data['Saddle Handlebar'][0],
             'saddle_handlebar_max': parsed_data['Saddle Handlebar'][1],
             'saddle_setback_min': parsed_data['Saddle Setback'][0],
             'saddle_setback_max': parsed_data['Saddle Setback'][1],
             'seatpost_setback': 1 if parsed_data['Saddle Setback'] else 0,
             'status': 'processed',
             'id': primary_key,
         })
     connect.commit()
     connect.close()
Example #27
0
class Decoder:
    def __init__(self):
        self.conn = Connection('nes.sqlite')
        self.conn.row_factory = Row

    def __del__(self):
        self.conn.close()

    def decode(self, opcode):
        c = self.conn.cursor()
        c.execute('select * from instruction where opcode=?', [opcode])
        row = c.fetchone()
        if row:
            return dict(zip(row.keys(), row))
        else:
            raise NotImplementedError('Undocumented Opcode: ' + str(opcode))
Example #28
0
 def heal(self):
     """Change status from processing to unprocess for all rows in case of
     failed attempt
     """
     connect = Connection(self.dbname)
     cursor = connect.cursor()
     cursor.execute("""
     UPDATE
         "data"
     SET
         status = "unprocessed"
     WHERE
         status = "processing"
     """)
     connect.commit()
     connect.close()
Example #29
0
 def get_unprocessed_count(self):
     """Return number of unprocessed rows
     """
     connect = Connection(self.dbname)
     cursor = connect.cursor()
     cursor.execute("""
     SELECT
         count(*)
     FROM
         "data"
     WHERE
         status = "unprocessed"
     """)
     unprocessed = cursor.fetchone()
     connect.commit()
     connect.close()
     return unprocessed[0]
Example #30
0
 def insert_input_data(self, inseam, trunk, forearm, arm, thigh, leg, notch,
                       height):
     """Insert data into a table
     """
     connect = Connection(self.dbname)
     cursor = connect.cursor()
     cursor.execute(
         """
     INSERT INTO data
         (
             inseam,
             trunk,
             forearm,
             arm,
             thigh,
             lower_leg,
             sternal_notch,
             height,
             status
         )
     VALUES
         (
             :inseam,
             :trunk,
             :forearm,
             :arm,
             :thigh,
             :leg,
             :notch,
             :height,
             :status
         )
     """, {
             'inseam': inseam,
             'trunk': trunk,
             'forearm': forearm,
             'arm': arm,
             'thigh': thigh,
             'leg': leg,
             'notch': notch,
             'height': height,
             'status': 'unprocessed',
         })
     connect.commit()
     connect.close()
def add_user_to_table(connection: sqlite3.Connection, data: tuple) -> NoReturn:
    """ Функция добавления информации в имеющуюся таблицу в базе данных

    аргументы:
    connection -- соединение с базой данных
    data -- данные пользователя вида (username, password (уже хешированный))
    """
    try:
        if connection is None:
            raise WrongDataBaseNameException(
                'Неправильное название базы данных.')
        cursor = connection.cursor()
        cursor.execute("""INSERT INTO users VALUES (?, ?)""", data)
        connection.commit()
    except sqlite3.Error as e:
        print(e)
    finally:
        connection.close()
Example #32
0
def convert_weatherdata(con: sqlite3.Connection, input_filepath: str):
    """convert the weather csv file"""
    # create the table
    con.execute("""
        CREATE TABLE weather (
            id INTEGER PRIMARY KEY,
            datetime TEXT,
            temperature REAL,
            humidity INTEGER,
            wind_speed REAL,
            precipitation REAL,
            condition TEXT,
            fog INTEGER,
            rain INTEGER,
            snow INTEGER,
            hail INTEGER,
            thunder INTEGER
        )
        """)

    # for an `n` item list, there are `n-1` splits
    # in order to merge the first two fields, split from the right and
    # leave out the final split, i.e., have `n-1-1` splits

    with con, open(input_filepath, 'rt') as f:
        # skip the header and determine the number of splits
        split_len = len(next(f).split(',')) - 2

        for i, line in enumerate(f, start=1):
            # make datetime a single field
            line = [
                l.replace(',', ' ')
                for l in line.rsplit(',', maxsplit=split_len)
            ]
            # replace empty string with NULL
            line = [l if l != '' else None for l in line]

            con.execute(
                "INSERT INTO weather VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                (i, *line),
            )

    con.close()