¿#j-DZddlmZmZmZmZmZmZmZmZm Z ddl Z ddl m Z ddl mZddlZddlZddlZddlZddlZddlZddlZddlZddlZddlZddlmZddlZddlmZmZmZm Z m!Z!m"Z"m#Z#m$Z$m%Z%m&Z&ddl'Z'ddl(m)Z) dd l*m+Z+d Z-ej\d Z/ej\d ej`ejbzZ2 ddl3Z4 ddl5Z6e d dZ7de7_8e ddZ9de9_8e ddZ:e ddZ;e ddZ<e ddZ=e ddZ>Gdde?Z@e#eAe:e&eAeAfe&eAeAeAfe&eAeAeAeAffZBe#e"eBe%eBfZCGdd ZDeDZEieFd!eGd"eHd"eAd#eId#eJd#eKd#eLjd$eLd$eNd$ej d#ejd#ejd#ejd#ejd!djd#e'jd#d#d"d!d$d#d#d"d"d!d$d$d% ZTe6r eTje6jd"e6jd"e6jd"e6jd"e6jd"e6jd"e6jd"e6jd"e6jd!e6jd!e6jd!i e4reTje4jd#iGd&d'e?ZcGd(d)e?ZdGd*d+e?ZeGd,d-e?ZfGd.d/e?ZgGd0d1e?ZhGd2d3e?ZiGd4d5eAZjGd6d7e?Zkd8jZmGd9d:ZnGd;d<ZoGd=d>eoZpGd?d@eoZqdAZrdBe$e#e eAeAfe%eAe&eAfdCeIfdDZsdEZtdFe"e eAeffdGZudHZvy#e,$rdZ+YwxYw#e,$rdZ4YwxYw#e,$rdZ6YwxYw#ea$rY"wxYw)I) chunks hash_recordsqlite3OperationalErrorsuggest_column_typestypes_for_column_typescolumn_affinity progressbarfind_spatialiteN) namedtuple)Mapping) rank_bm25) castAnyCallableDict GeneratorIterableUnionOptionalListTuple)pm)iterdumpz \s+|(".*?")a ^ # Start of string \s*CREATE\s+VIRTUAL\s+TABLE\s+ # CREATE VIRTUAL TABLE ( '(?P[^']*(?:''[^']*)*)' | # single quoted name "(?P[^"]*(?:""[^"]*)*)" | # double quoted name `(?P[^`]+)` | # `backtick` quoted name \[(?P[^\]]+)\] | # [...] quoted name (?P # SQLite non-quoted identifier [A-Za-z_\u0080-\uffff] # \u0080-\uffff = "any character larger than u007f" [A-Za-z_\u0080-\uffff0-9\$]* # zero-or-more alphanemuric or $ ) ) \s+(IF\s+NOT\s+EXISTS\s+)? # IF NOT EXISTS (optional) USING\s+(?P\w+) # for example USING FTS5 Column)cidnametypenotnull default_valueis_pkaF Describes a SQLite column returned by the :attr:`.Table.columns` property. ``cid`` Column index ``name`` Column name ``type`` Column type ``notnull`` Does the column have a ``not null`` constraint ``default_value`` Default value for this column ``is_pk`` Is this column part of the primary key ColumnDetails)tablecolumn total_rowsnum_null num_blank num_distinct most_common least_commonaH Summary information about a column, see :ref:`python_api_analyze_column`. ``table`` The name of the table ``column`` The name of the column ``total_rows`` The total number of rows in the table ``num_null`` The number of rows for which this column is null ``num_blank`` The number of rows for which this column is blank (the empty string) ``num_distinct`` The number of distinct values in this column ``most_common`` The ``N`` most common values as a list of ``(value, count)`` tuples, or ``None`` if the table consists entirely of distinct values ``least_common`` The ``N`` least common values as a list of ``(value, count)`` tuples, or ``None`` if the table is entirely distinct or if the number of distinct values is less than N (since they will already have been returned in ``most_common``) ForeignKeyr%r& other_table other_columnIndex)seqruniqueoriginpartialcolumnsXIndex)rr6 XIndexColumn)seqnorrdesccollkeyTrigger)rr%sqlc eZdZy)TransformErrorN__name__ __module__ __qualname__e/home/mi/.openclaw/workspace/fridge-app/backend/.venv/lib/python3.12/site-packages/sqlite_utils/db.pyr@r@rFr@c eZdZy)DefaultNrArErFrGrJrJrHrFrJFLOATINTEGERTEXTBLOB) rMrLrKrNtextstrintegerintfloatblobbytesceZdZdZy) AlterErrorzError altering tableNrBrCrD__doc__rErFrGrWrWsrFrWceZdZdZy)NoObviousTablez3Could not tell which table this operation refers toNrXrErFrGr[r[s9rFr[ceZdZdZy)NoTablezSpecified table does not existNrXrErFrGr]r]$rFr]ceZdZdZy) BadPrimaryKeyz0Table does not have a single obvious primary keyNrXrErFrGr`r`s6rFr`ceZdZdZy) NotFoundErrorzRecord not foundNrXrErFrGrbrbsrFrbceZdZdZy)PrimaryKeyRequiredz!Primary key needs to be specifiedNrXrErFrGrdrd s'rFrdceZdZdZy)InvalidColumnszSpecified columns do not existNrXrErFrGrfrfr^rFrfc eZdZy) DescIndexNrArErFrGrhrhrHrFrhceZdZdZdZy)BadMultiValuesz4With multi=True code must return a Python dictionaryc||_yN)values)selfrms rG__init__zBadMultiValues.__init__s  rFN)rBrCrDrYrorErFrGrjrjs :rFrjz] CREATE TABLE IF NOT EXISTS [{}]( [table] TEXT PRIMARY KEY, count INTEGER DEFAULT 0 ); c!,eZdZdZdZdZ d_deeee je jfde deede d e d eed e d e d e fdZd`dZej$dZej$dad eefdZdededfdZdefdZ dbdeede de deefdZdZdedeee jffdZ dad ed!eeeefdeeddffd"Z dad ed#eeeefde j<fd$Zd ede j<fd%Z dededfd&Z!d'edefd(Z"d)edefd*Z#d'edefd+Z$dcd,e d-e de%efd.Z&de%efd/Z'e(de%d0fd1Z)e(de%d2fd3Z*e(de%e+fd4Z,e(de-eeffd5Z.e(defd6Z/e(de fd7Z0e(de1e2d8ffd9Z3e(defd:Z4d;Z5d<Z6d=Z7d>Z8dad?eeede-ee2ffd@Z9dAZ: dad ed!eeeefde%efdBZ;dedCe dddedEe-ee?fdFee?dCee<dGee%edHeeedIee-ee?fdJeedKeeedLeee-eefe%efdMe d e defdNZ@ dededEe-ee?fdFee?dCee<dGee%edHeeedIee-ee?fdJeedKeeedLeee-eefe%efdMe de dOe dPe d e dd0f dQZAdedRefdSZB dcded edOe de fdTZCdUedVede%efdWZDdCee1eeeeffdXZEdYZFdZZGdad[ZHdeeddffd\ZIdad]eede fd^ZJy)fDatabasea Wrapper for a SQLite database connection that adds a variety of useful utility methods. To create an instance:: # create data.db file, or open existing: db = Database("data.db") # Create an in-memory database: dB = Database(memory=True) :param filename_or_conn: String path to a file, or a ``pathlib.Path`` object, or a ``sqlite3`` connection :param memory: set to ``True`` to create an in-memory database :param memory_name: creates a named in-memory database that can be shared across multiple connections :param recreate: set to ``True`` to delete and recreate a file database (**dangerous**) :param recursive_triggers: defaults to ``True``, which sets ``PRAGMA recursive_triggers=on;`` - set to ``False`` to avoid setting this pragma :param tracer: set a tracer function (``print`` works for this) which will be called with ``sql, parameters`` every time a SQL query is executed :param use_counts_table: set to ``True`` to use a cached counts table, if available. See :ref:`python_api_cached_table_counts` :param strict: Apply STRICT mode to all created tables (unless overridden) _countsFNfilename_or_connmemory memory_namerecreaterecursive_triggerstraceruse_counts_tableexecute_pluginsstrictc Bd|_d|_||s|r ||s |sJd|r=dj|} tj| dd|_d|_||_n|s|dk(r"tjd|_d|_nt |ttjfr[|r5tjj|r tj|tjt||_n|rJd||_||_|r|j!dt#|_||_|r*t(j*j-|j | |_y#t$rtjd|_wxYw) NFz5Either specify a filename_or_conn or pass memory=Truez file:{}?mode=memory&cache=sharedT)uricheck_same_threadz:memory:z4recreate cannot be used with connections, only pathszPRAGMA recursive_triggers=on;)conn)rurtformatrconnectr isinstancerPpathlibPathospathexistsremoveOSError_tracerexecuteset_registered_functionsryrhookprepare_connectionr{) rnrsrtrurvrwrxryrzr{r}s rGrozDatabase.__init__Gst   ,f[  $&K C B C  4;;KHC"'DI DK*D  ':5 3DIDK (3 *= >BGGNN+;<II./  ,<(=>DI W!W W<(DI  LL8 9*-%" 0  GG & &DII & 6 !!( ;DI s E99%Freturnc8|jjy)z=Close the SQLite connection, and the underlying database fileN)rcloserns rGrzDatabase.closezs rFc#K|jj} d|j_d||j_y#||j_wxYww)a Ensure autocommit is off for this database connection. Example usage:: with db.ensure_autocommit_off(): # do stuff here This will reset to the previous autocommit state at the end of the block. N)risolation_level)rnold_isolation_levels rGensure_autocommit_offzDatabase.ensure_autocommit_off~sE#ii77 <(,DII % (;DII %(;DII %sAAAAAc#rK|j}|xst|_ |||_y#||_wxYww)a Context manager to temporarily set a tracer function - all executed SQL queries will be passed to this. The tracer function should accept two arguments: ``sql`` and ``parameters`` Example usage:: with db.tracer(print): db["creatures"].insert({"name": "Cleo"}) See :ref:`python_api_tracing`. :param tracer: Callable accepting ``sql`` and ``parameters`` arguments N)rprint)rnrx prev_tracers rGrxzDatabase.tracers4"ll   'J&DL;DLs7+7 47 table_name)TableViewc$|j|S)a ``db[table_name]`` returns a :class:`.Table` object for the table with the specified name. If the table does not exist yet it will be created the first time data is inserted into it. :param table_name: The name of the table r%rnrs rG __getitem__zDatabase.__getitem__szz*%%rFc8dj|jS)Nz )rrrs rG__repr__zDatabase.__repr__s%%dii00rFfn deterministicreplacerc4fd}||S||y)af ``fn`` will be made available as a function within SQL, with the same name and number of arguments. Can be used as a decorator:: @db.register_function def upper(value): return str(value).upper() The decorator can take arguments:: @db.register_function(deterministic=True, replace=True) def upper(value): return str(value).upper() See :ref:`python_api_register_function`. :param fn: Function to register :param deterministic: set ``True`` for functions that always returns the same output for a given input :param replace: set ``True`` to replace an existing function with the same name - otherwise throw an error :param name: name of the SQLite function - if not specified, the Python function name will be used cxs |j}ttj|j}s||fj vr|Si}d}r- j j|||fit|dd}|sj j|||fi|j j||f|S#tj$rYUwxYw)NFT)r) rBleninspect signature parametersrrcreate_functiondictrNotSupportedErroradd) rfn_namearitykwargs registeredrrrrns rGregisterz,Database.register_function..registers)bkkG))"-889E/43M3MM FJ-DII--.26.N"&J) ))'5"GG  & & * *GU+; <I 00s,CCCNrE)rnrrrrrs` ``` rGregister_functionzDatabase.register_functions: * :O RLrFc4|jtddy)z`Register the ``rank_bm25(match_info)`` function used for calculating relevance with SQLite FTS4.T)rrN)rrrs rGregister_fts4_bm25zDatabase.register_fts4_bm25s ydKrFaliasfilepathcdjttj|j |j }|j |y)a  Attach another SQLite database file to this connection with the specified alias, equivalent to:: ATTACH DATABASE 'filepath.db' AS alias :param alias: Alias name to use :param filepath: Path to SQLite database file on disk z3 ATTACH DATABASE '{}' AS [{}]; N)rrPrrresolvestripr)rnrr attach_sqls rGattachzDatabase.attachsG F  X&..0 15 %'  Z rFr>paramsc#K|j||xs t}|jDcgc]}|d }}|D]}tt ||ycc}ww)a  Execute ``sql`` and return an iterable of dictionaries representing each row. :param sql: SQL query to execute :param params: Parameters to use in that query - an iterable for ``where id = ?`` parameters, or a dictionary for ``where id = :id`` r N)rtuple descriptionrzip)rnr>rcursordkeysrows rGqueryzDatabase.querysac6#4UW5$001!11 'Cs4~& & '2s-A! A&A!rc|jr|j||||jj||S|jj|S)a  Execute SQL query and return a ``sqlite3.Cursor``. :param sql: SQL query to execute :param parameters: Parameters to use in that query - an iterable for ``where id = ?`` parameters, or a dictionary for ``where id = :id`` )rrr)rnr>rs rGrzDatabase.executesK << LLj )  !99$$S*5 599$$S) )rFct|jr|j|d|jj|S)z Execute multiple SQL statements separated by ; and return the ``sqlite3.Cursor``. :param sql: SQL to execute N)rr executescriptrnr>s rGrzDatabase.executescript s. << LLd #yy&&s++rFc ||jvr t||fi|S|jd|jt ||fi|S)z Return a table object, optionally configured with default options. See :ref:`reference_db_table` for option details. :param table_name: Name of the table r{) view_namesr setdefaultr{r)rnrrs rGr%zDatabase.table*sK * *j3F3 3   h 4z4V4 4rFvaluecL|jdd|ijdS)z Apply SQLite string quoting to a value, including wrapping it in single quotes. :param value: String to quote zSELECT quote(:value)rr rfetchonernrs rGquotezDatabase.quote8s0|| " e   (*Q  rFrc|jddzr|dz }tj|}|Dcgc] }|s|dk7s |}}djd|DScc}w)a Escape special characters in a SQLite full-text search query. This works by surrounding each token within the query with double quotes, in order to avoid words like ``NOT`` and ``OR`` having special meaning as defined by the FTS query syntax here: https://www.sqlite.org/fts5.html#full_text_query_syntax If the query has unbalanced ``"`` characters, adds one at end. :param query: String to escape "z"" c3dK|](}|jdsdj|n|*yw)r"{}"N) startswithr).0bits rG z%Database.quote_fts..Ys. GJcnnS&9FMM# s B s.0)count _quote_fts_resplitjoin)rnrbitsbs rG quote_ftszDatabase.quote_ftsGsl ;;s a  SLE""5)3a1d33xx NR   4sAAActt|jdxrt|jdt|jdxrt|jdgr|St|j dvr|St|jdrdj |S|j |S)N'r) CURRENT_TIME CURRENT_DATECURRENT_TIMESTAMP)z({}))anyrPrendswithupperrrrs rGquote_default_valuezDatabase.quote_default_value]s E %%c*Gs5z/B/B3/GE %%c*Gs5z/B/B3/G  L u:   !V VL u:  s #==' 'zz%  rFfts4fts5cdg}|r|jd|r|jddjdj|}|j|j Dcgc]}|d c}Scc}w)z List of string table names in this database. :param fts4: Only return tables that are part of FTS4 indexes :param fts5: Only return tables that are part of FTS5 indexes ztype = 'table'zsql like '%USING FTS4%'zsql like '%USING FTS5%'z'select name from sqlite_master where {} AND r )appendrrrfetchall)rnrrwherer>rs rG table_nameszDatabase.table_namesoso""  LL2 3  LL2 37>>w||E?RS"ll3/88:;!;;;s+ A:cl|jdjDcgc]}|d c}Scc}w)z+List of string view names in this database.z2select name from sqlite_master where type = 'view'r )rrrnrs rGrzDatabase.view_names~s;\\Dhj   aD   s 1rcpttd|jDcgc]}|| c}Scc}w)z'List of Table objects in this database.r)rrrrnrs rGtableszDatabase.tabless0DM4;K;K;M#N4DJ#NOO#N 3 rcpttd|jDcgc]}|| c}Scc}w)z&List of View objects in this database.r)rrrrs rGviewszDatabase.viewss.DL$//:K"L$4:"LMM"Lrcr|jdjDcgc] }t| c}Scc}w)zRList of ``(name, table_name, sql)`` tuples representing triggers in this database.zDselect name, tbl_name, sql from sqlite_master where type = 'trigger')rrr=rs rGtriggerszDatabase.triggerss; \\Vhj   QK   s4cj|jDcic]}|j|jc}Scc}w)zBA ``{trigger_name: sql}`` dictionary of triggers in this database.rrr>rntriggers rG triggers_dictzDatabase.triggers_dict):>Gg gkk)GGG0cg}|jdjD]<}|d}|jjds|dz }|j |>dj |S)zSQL schema for this database.z3select sql from sqlite_master where sql is not nullr ; )rrrrrr)rnsqlsrr>s rGschemazDatabase.schemaso<< A (* Ca&C99;'',s  KK   yyrFcV djtjd}|j5|jj dj||jj dj|dddy#1swYyxYw#t $rYywxYw)z'Does this database support STRICT mode?zt{}z"create table {} (name text) strictz drop table {}NTF)rsecrets token_hexrr Exceptionrs rGsupports_strictzDatabase.supports_stricts g&7&7&;>@CSc!fll3/011rFcF|jdjdS)z| Current ``journal_mode`` of this database. https://www.sqlite.org/pragma.html#pragma_journal_mode zPRAGMA journal_mode;r rrs rG journal_modezDatabase.journal_modes"||23<<>qAArFc|jdk7r+|j5|jddddyy#1swYyxYw)zT Sets ``journal_mode`` to ``'wal'`` to enable Write-Ahead Log mode. walzPRAGMA journal_mode=wal;Nrrrrs rG enable_walzDatabase.enable_walsJ    %++- 9 78 9 9 & 9 9 <Ac|jdk7r+|j5|jddddyy#1swYyxYw)zKSets ``journal_mode`` back to ``'delete'`` to disable Write-Ahead Log mode.deletezPRAGMA journal_mode=delete;Nrrs rG disable_walzDatabase.disable_walsH    (++- < :; < < ) < .srs rG cached_countszDatabase.cached_countss.44T5L5LM  +22499;B>B BBc*|jDcgc]}|js|}}|j5|j||j}|j |j d|Ddddycc}w#1swYyxYw)z&Re-calculate cached counts for tables.c3VK|]!}|j|jd#yw))r%rN)r execute_countr0s rGrz(Database.reset_counts..s*$ **u/B/B/DE$s')N)rhas_counts_triggersrr(r' delete_where insert_all)rnr%r counts_tables rG reset_countszDatabase.reset_countss%)[[NEE4M4M%NN YY   % % ' 7 78L  % % '  # #$#$    O  sBBAB  Bc8t|j||Srl)listr)rnr>rs rGexecute_returning_dictsz Database.execute_returning_dictssDJJsF+,,rF foreign_keysc tt||}td|Drttt|Std|DrZg}|D]Q}tt |}|j |}|j|}|jt ||||S|Std|DsJdg}|D]}t|dk(r|d|k(sJdj||t|dvsJd t|d vr}t|dk(r&ttt t t f|d d }n"ttt t t f|}|jt ||d|d |d |jt ||d|d |j|d |S)a Given a list of differing foreign_keys definitions, return a list of fully resolved ForeignKey() named tuples. :param name: Name of table that foreign keys are being defined for :param foreign_keys: List of foreign keys, each of which can be a string, a ForeignKey() named tuple, a tuple of (column, other_table), or a tuple of (column, other_table, other_column), or a tuple of (table, column, other_table, other_column) c3<K|]}t|tywrl)rr-rfks rGrz0Database.resolve_foreign_keys..sAbz"j)Ac3<K|]}t|tywrl)rrPr@s rGrz0Database.resolve_foreign_keys..s:rz"c":rBc3HK|]}t|ttfywrl)rrr;r@s rGrz0Database.resolve_foreign_keys..#s  .0JrE4= ) s "z(foreign_keys= should be a list of tuplesr z$First item in {} should have been {})rrEz8foreign_keys= should be a list of tuple pairs or triples)rFrErNr) rrallrr-rPguess_foreign_tableguess_foreign_columnrrrr) rnrr=r%fksr&r/r0 tuple_or_lists rGresolve_foreign_keyszDatabase.resolve_foreign_keys sUDJ' ALA AZ(,7 7 :\: :C& Pc6*#77? $99+F  :dFKNO  P J 4@   6 5 6 ) M=!Q&!!$,V9@@PTUV,}%* JJ  J =!V+}%*$(sC})=}QR?P$QM$(sC})=}$MM mA. a0@-PQBR %a(%a(22=3CD - < rFr6pk column_ordernot_nulldefaultshash_idhash_id_columnsextracts if_not_existsc @  | r|d}|j||xsg}|D cic]} | j| }} t| } | jD]c\}}t |t rJ||j s|j|ttddt|<t|||d||<e|xs t}|xsi}sJdtfd|Ds@Jdjt|ttjtfd|DsIJd jtt|ttjt!jt#j}fd }|j%| |r|j'd |tf|}|D] j(|k(rj+ j,r. j,d k7s>t/ fd| j(j0Drjt3dj j( j,g}d}t |t"r&t5|dk(rt |d tr|d }t |tr/|}||Dcgc]}|d  c}vr|j'd |tf|D]\}}g}||k(r|j7d||vr|j7d||vr7||2|j7dj|j9||||vr<|j7dj||j(||j,t:|}| r|dk(rd}|j7dj|||rddj=|zndd}|N|rLt5|dkDr>djdj=|Dcgc]}dj|c}}d j=|}d!j| rd"nd|||| r|j>rd#nd$}|Scc} wcc}wcc}w)%ai Returns the SQL ``CREATE TABLE`` statement for creating the specified table. :param name: Name of table :param columns: Dictionary mapping column names to their types, for example ``{"name": str, "age": int}`` :param pk: String name of column to use as a primary key, or a tuple of strings for a compound primary key covering multiple columns :param foreign_keys: List of foreign key definitions for this table :param column_order: List specifying which columns should come first :param not_null: List of columns that should be created as ``NOT NULL`` :param defaults: Dictionary specifying default values for columns :param hash_id: Name of column to be used as a primary key containing a hash of the other columns :param hash_id_columns: List of columns to be used when calculating the hash ID for a row :param extracts: List or dictionary of columns to be extracted during inserts, see :ref:`python_api_extracts` :param if_not_exists: Use ``CREATE TABLE IF NOT EXISTS`` :param strict: Apply STRICT mode to table Nid)rVrrMz$Tables must have at least one columnc3&K|]}|v ywrlrErnr6s rGrz,Database.create_table_sql..z AL z0not_null set {} includes items not in columns {}c3&K|]}|v ywrlrErYs rGrz,Database.create_table_sql..r[r\z0defaults set {} includes items not in columns {}c>|dvrj|dSdS)Nr r)index)prNs rGsort_keyz+Database.create_table_sql..sort_keys(34Q4<3G|))!A$/PSPrFr<r rowidc3VK|] }|jjk(s|"ywrl)rr0)rcrAs rGrz,Database.create_table_sql..s%6166R__;T6s))No such column: {}.{}rz PRIMARY KEYzNOT NULLz DEFAULT {}z,REFERENCES [{other_table}]([{other_column}]))r/r0rKREALz/ [{column_name}] {column_type}{column_extras}r) column_name column_type column_extrasz, PRIMARY KEY ({pks})r-[{}])pksz, zSCREATE TABLE {if_not_exists}[{table}] ( {columns_sql}{extra_pk} ){strict}; IF NOT EXISTS z STRICT)rTr% columns_sqlextra_pkr{) rLr&resolve_extractsitemsrrr create_tablerRrPr-rrGrreprrvalidate_column_namesr;sortinsertr/getr0rr6rWrrrCOLUMN_TYPE_MAPPINGrr)rnrr6rMr=rNrOrPrQrRrSrTr{rAforeign_keys_by_columnextract_column extract_table column_itemsra column_defs single_pkrerirjrkcolumn_type_strrpr`ror>s ` ` ` rGcreate_table_sqlzDatabase.create_table_sqlGs> G00|7IrJ :F!GB"))R-!G!G#H--5^^-=  )NM.%0u &--/!!-c1Jt!T&)GN #5?nmT6 "> 2 $su>r>>>w "*   = D D NDW\\^!45    "*   = D D X c',,.&9!:   glln-GMMO,  # Q   (  +    GS> 2B B~~%'++boo*F')#6/7763!+222>>2??S    b$ CGqLZ1s5KAB b# I 51!A$55##ACy1(4  $KMi'$$]3h&$$Z0h&8K+@+L$$ ''(@(@+AV(WX44$$BII$:;$G$S$S%;K%H%U%UJ 2+>O/W4"(   AHH + /;Hsxx 66b I - >  B! 299II.s+#&Hh#338$#sTN)typesdroprNrOrPrM) rr6rMr=rNrOrPrQrRrSrTr{)rMr=rNrOrPrQrR)rrrr columns_dictrrr add_columnr;rrmrrPr6r!rrdefault_valuesrrrr%)rnrr6rMr=rNrOrPrQrRrSrTrrrr{r%should_transformmissing_columnsr&columns_to_droprr current_pks desired_pkrecurrent_not_nulldesired_not_nullr> created_tablers @rGrszDatabase.create_tablesfP :   E4:..T ! d**,T +E$ $11 "#*1--/#O &6!w9NO*9*?*?*A9&Hh$$Xx89/W@P5P#' )*+>S->?<O#' ))KJ"c" T !"X kZ7#' 05 K1K K08s8}ce #33#' H(<(<<#' !(!-%%  L##%%+'$   S %%+#  E=))G0 Ls H 'H HHnew_namecH|jdj||y)zx Rename a table. :param name: Current table name :param new_name: Name to rename it to z+ALTER TABLE [{name}] RENAME TO [{new_name}])rrN)rr)rnrrs rG rename_tablezDatabase.rename_tablePs) 9 @ @H A  rFc|r |rJddj||}|s|r?||jvr-|r|S|r'|||jk(r|S||j|j ||S)a Create a new SQL view with the specified name - ``sql`` should start with ``SELECT ...``. :param name: Name of the view :param sql: SQL ``SELECT`` query to use for this view. :param ignore: Set to ``True`` to do nothing if a view with this name already exists :param replace: Set to ``True`` to replace the view if one with this name already exists z0Use one or the other of ignore/replace, not bothzCREATE VIEW {name} AS {sql})rr>)rrrrr)rnrr>rr create_sqls rG create_viewzDatabase.create_view]s w > = > 399t9M Wt((K!T$Z%6%66# JOO% Z  rFr%r/cg}||h}|jD]R}|jDchc]}|j}}|j|s8|j |j T|Scc}w)a& Given two table names returns the name of tables that could define a many-to-many relationship between those two tables, based on having foreign keys to both of the provided tables. :param table: Table name :param other_table: Other table name )rr=r/ issupersetrr)rnr%r/ candidatesr table_objrA has_fks_tos rGm2m_table_candidateszDatabase.m2m_table_candidatesysq % 2I3<3I3IJR"..JJJ$$V,!!)..1  2 KsA*c td|DsJdg}|D]2\} ||jstdj|||}t |t stdj|t t |}|jvrtdj|| jstdj  dk7r, | jvrtdj t fd |jDr|j| f5i}|D]&}|j|d gj|(|jD](\}}t t ||j| *|jy ) z See :ref:`python_api_add_foreign_keys`. :param foreign_keys: A list of ``(table, column, other_table, other_column)`` tuples c3hK|]*}t|dk(xrt|ttf,yw)rEN)rrr;rr@s rGrz,Database.add_foreign_keys..s0 ?ACGqL :ZT5M: : s02zSforeign_keys must be a list of 4-tuples, (table, column, other_table, other_column)zNo such table: {}zMust be a table, not a view: {}zNo such column: {} in {}zNo such other_table: {}rczNo such other_column: {} in {}c3|K|]3}|jk(r"|jk(r|jk(r|5ywrlr&r/r0rrAr&r0r/s rGrz,Database.add_foreign_keys..s=99&NNk1OO|3 9<r )add_foreign_keysN)rGrrWrrrrrrr=rrrrrvacuum) rnr=foreign_keys_to_creater%rby_tablerArJr&r0r/s @@@rGrzDatabase.add_foreign_keyss EQ   a ` a "$9E  4E6; ;%%' !4!;!;E!BCCU Ii/ !B!I!I%!PQQUI.IY333 !;!B!B65!QRR $++- !:!A!A+!NOO' [(9(F(FF 4;;L+V#00'--FK>5 >%'( 6B   1r * 1 1" 5 6#..* EJE3 U $ . . . D E rFc<|jD]}||}|jDchc]*}t|jdk(s|jd,}}|jD]/}|j |vs|j |j gd1ycc}w)zKCreate indexes for every foreign key column on every table in the database.rr T)find_unique_nameN)rindexesrr6r=r& create_index)rnrr%iexisting_indexesrAs rGindex_foreign_keyszDatabase.index_foreign_keyss**, KJ$E&+mm !"s199~7J !    (( K99$44&& {T&J K  K s BBc&|jdy)z-Run a SQLite ``VACUUM`` against the database.zVACUUM;N)rrs rGrzDatabase.vacuums YrFcVd}||dj|z }|j|y)z Run ``ANALYZE`` against the entire database or a named table or index. :param name: Run ``ANALYZE`` against this specific named table or index ANALYZENz [{}])rr)rnrr>s rGanalyzezDatabase.analyzes.    7>>$' 'C SrFc#Ktrt|jEd{y |jjEd{y7)7#t$r tdwxYww)z=A sequence of strings representing a SQL dump of the databaseNz7conn.iterdump() not found - try pip install sqlite-dump)rrAttributeErrorrs rGrzDatabase.iterdumps_  * * * 99--/// +0! $M s8A'A A'AA A A' AA$$A'rc| t}|jjd|jj|d|j vry|j d}|j }|xrt|dS)a The ``init_spatialite`` method will load and initialize the SpatiaLite extension. The ``path`` argument should be an absolute path to the compiled extension, which can be found using ``find_spatialite``. Returns ``True`` if SpatiaLite was successfully initialized. .. code-block:: python from sqlite_utils.db import Database from sqlite_utils.utils import find_spatialite db = Database("mydb.db") db.init_spatialite(find_spatialite()) If you've installed SpatiaLite somewhere unexpected (for testing an alternate version, for example) you can pass in an absolute path: .. code-block:: python from sqlite_utils.db import Database from sqlite_utils.utils import find_spatialite db = Database("mydb.db") db.init_spatialite("./local/mod_spatialite.dylib") :param path: Path to SpatiaLite module on disk Tspatial_ref_sysFzselect InitSpatialMetadata(1)r )r renable_load_extensionload_extensionrrrbool)rnrrresults rGinit_spatialitezDatabase.init_spatialitesw: <"$D ''-   &  0 0 2 2=>")$vay/)rF) NFNFTNFTF)rNrl)NFFN)FF) NNNNNNNNFF NNNNNNNNFFFFF)KrBrCrDrYr'ryrrrPrrr Connectionrrror contextlibcontextmanagerrrxrrrrrrrrrCursorrrr%rrrrrrpropertyrrr=rrr rrrrRrrr r$r(r+r1r9r<ForeignKeysTyper-rLrrrsrrrrrrrrrrErFrGrqrq+s(0#TX%)#'%)!& $1"5gllG ? ' 4t# $ ' GK**$,U8T>-B$C* *",,, 5 5%2H 5 3 3  s s ,!!!$ <  ?- d- ;;'6; j ;B!26,0,0-1!%37?C#KKc3hK SM K / K tCy) K8C=)K4S>*K#K"(3-0K5c3hc!:;<KKK Kb!26,0,0-1!%37?C#!z*z*c3hz* SM z* / z* tCy) z*8C=)z*4S>*z*#z*"(3-0z*5c3hc!:;<z*z*z*z*z* !z*" #z*x      JO!+/BF8#CDI$5XeCc3 ?`` :param where_args: Parameters to use with that fragment - an iterable for ``id > ?`` parameters, or a dictionary for ``id > :id`` zselect count(*) from [{}] where r )rrrrr)rnrrr>s rG count_wherezQueryable.count_wheresU*00;   9u$ $CwwsJ$4"5>>@CCrFc"|jSrlrrs rGr4zQueryable.execute_count,s!!rFc"|jS)z*A count of the rows in this table or view.rrs rGrzQueryable.count0s!!rFc"|jS)zCIterate over every dictionaries for each row in this table or view.) rows_wherers rGrowszQueryable.rows5s  rForder_byselectlimitoffsetc#K|jsydj||j}||d|zz }||d|zz }||dj|z }||dj|z }|jj ||xsg}|j D cgc]} | d } } |D]} t t| | ycc} ww)a Iterate over every row in this table or view that matches the specified where clause. Returns each row as a dictionary. See :ref:`python_api_rows` for more details. :param where: SQL where fragment to use, for example ``id > ?`` :param where_args: Parameters to use with that fragment - an iterable for ``id > ?`` parameters, or a dictionary for ``id > :id`` :param order_by: Column or fragment of SQL to order by :param select: Comma-separated list of columns to select - defaults to ``*`` :param limit: Integer number of rows to limit to :param offset: Integer for SQL offset Nzselect {} from [{}]rz order by limit {} offset {}r )rrrrrrrr) rnrrrrrrr>rrer6rs rGrzQueryable.rows_where:s,{{} #**6499=   9u$ $C   <(* *C   ;%%e, ,C   <&&v. .Cj&6B7!'!3!34A1Q444 *Cs7C() ) *5sBC C *&Cc# K|jDcgc]}|j}}|jDcgc]}|js|j}}|s|jdddg}dj d|D} |j | |||||D]/ t fd|D} t| dk(r| d} | f1ycc}wcc}ww) aI Like ``.rows_where()`` but returns ``(pk, row)`` pairs - ``pk`` can be a single value or tuple. :param where: SQL where fragment to use, for example ``id > ?`` :param where_args: Parameters to use with that fragment - an iterable for ``id > ?`` parameters, or a dictionary for ``id > :id`` :param order_by: Column or fragment of SQL to order by :param select: Comma-separated list of columns to select - defaults to ``*`` :param limit: Integer number of rows to limit to :param offset: Integer for SQL offset r rc,c3>K|]}dj|ywrlNrrris rGrz/Queryable.pks_and_rows_where..xsU&-- 4U)rrrrrrc3(K|] }| ywrlrErrMrs rGrz/Queryable.pks_and_rows_where..s1r3r71rN)r6rr#rwrrrr) rnrrrrrr& column_namesrmrrow_pkrs @rGpks_and_rows_wherezQueryable.pks_and_rows_where`s&37,,? ? ?)-Fvv{{FF   7 +)CU UU??! # C1S11F6{a#+   @Fs!CCCCCBCrc|jsgS|jjdj|jj }|Dcgc] }t | c}Scc}w)zbList of :ref:`Columns ` representing the columns in this table or view.zPRAGMA table_info([{}]))rrrrrrr)rnrrs rGr6zQueryable.columnssU{{}Iww8?? JKTTV(,- ---sA+c||jDcic]"}|jt|j$c}Scc}w)zU``{column_name: python-type}`` dictionary representing columns in this table or view.)r6rr r rnr&s rGrzQueryable.columns_dicts0IM Uf _V[[99UUUs'9cr|jjd|jfjdS)z"SQL schema for this table or view.z,select sql from sqlite_master where name = ?r )rrrrrs rGrzQueryable.schemas2ww :TYYL (*Q rFNN)NNN*NN)NNNNN)rBrCrDrrrorrPrrrrRrr4rrrrrrrrrrr6rrrErFrGrrs $6:D}DU8T>23D D"""s""!idD 01!! $6:"&# $$*}$*U8T>23$*3- $*  $* } $* $* 4t# $$*P $6:"&# $ $}$U8T>23$3- $ } $  $ 5d#T4/ 0$L.h..Vd38nVVrFrc$V eZdZUdZdZeeed<dZee ed< dde de dee d ee d ee e d eee d eee e fd edee deee dedededeeee e fe e fdeedeee e fdef"fd Zde fdZedefdZdefdZede e fdZedefdZdeeee efdefdZede d fd!Zedee fd"Zede efd#Z ede e!fd$Z"ede e#fd%Z$edee e ffd&Z%edee e ffd'Z&edefd(Z' ddee e fdee d ee d ee e d eee d eee e fdee deee deeee e fe e fd)ededed*ededdfd+Z(d,e ddfd-Z)ddde*dddddddd. d/eed0eed1eedee d eee d eee e fd2eee d3ee d ee d ee e d4ee ddfd5Z+ddde*ddddddddd6 d/eed0eed1eedee d eee d eee e fd2eed3ee d ee d ee e d7ee d4ee de e fd8Z, ddee ee fd9ee d:ee d0eee e fddf d;Z- ddeee e.fded?ef d@Z/ ddAe dBee dCee dDee dEee f dFZ0ddefdGZ1dHe de fdIZ2dJe fdKZ3 ddHe dJee dLee defdMZ4dNZ5edefdOZ6 ddee dPe dQedRee def dSZ7dee ddfdTZ8ddUZ9dVZ:dee fdWZ;ddXZ< ddeee dYee dZeed[eed\ee d]ede fd^Z= dd_e dYee deee dZeed[eed\ee d`eeeefd]edaede>eddffdbZ?dcZ@deeee eeAfddfddZB dd\ee d`eeeefd?eddfdeZC ddeeee eeAfdfeededeeddf dgZD ddee e e fdheEdiee djee d1edked\ee d`eeeefdledmefdnZF ddoZGdpZHdqZIe*e*e*e*e*e*e*e*e*e*e*e*e*e*fdree e fd eee e eJfd eeee eJfd eeee e feJfdeee eJfdeeee eJfdeeeeJfdeeeeJfdeeeeJfdeeee e fe e eJfdeeee e feJfdeeee e feJfdeeeeJfddfdsZKe*e*e*e*e*e*e*e*e*e*e*de*e*e*dde*f ddtZLe*e*e*e*e*e*e*e*e*e*e*e*f dduZMe*e*e*e*e*e*e*e*e*e*e*e*de*f ddvZNdweee e fddfdxZO ddyee e fdzeee e fdee d ee d ee e d eee d eee e fdeeee e fe e fdeee e fdeee e fdeefd{ZPde*dddfdJee dfd|eeeee e fee e ffdeee eJfd}eee e fd~ee def dZQdZR ddHe dedededdf dZS dde de dede d edef dZTdefdZUxZVS)rak Tables should usually be initialized using the ``db.table(table_name)`` or ``db[table_name]`` methods. The following optional parameters can be passed to ``db.table(table_name, ...)``: :param db: Provided by ``db.table(table_name)`` :param name: Provided by ``db.table(table_name)`` :param pk: Name of the primary key column, or tuple of columns :param foreign_keys: List of foreign key definitions :param column_order: List of column names in the order they should be in the table :param not_null: List of columns that cannot be null :param defaults: Dictionary of column names and default values :param batch_size: Integer number of rows to insert at a time :param hash_id: If True, use a hash of the row values as the primary key :param hash_id_columns: List of columns to use for the hash_id :param alter: If True, automatically alter the table if it doesn't match the schema :param ignore: If True, ignore rows that already exist when inserting :param replace: If True, replace rows that already exist when inserting :param extracts: Dictionary or list of column names to extract into a separate table on inserts :param conversions: Dictionary of column names and conversion functions :param columns: Dictionary of column names to column types :param strict: If True, apply STRICT mode to table N last_rowidlast_pkFrrrMr=rNrOrP batch_sizerQrRalterrrrS conversionsr6r{clt|||t||||||| | | | | ||xsi|||_y)N)rMr=rNrOrPrrQrRrrrrSrr6r{)superror _defaults)rnrrrMr=rNrOrPrrQrRrrrrSrr6r{ __class__s rGrozTable.__init__sR( T"%%!+#)r rFrc dj|j|jsdSdjdjd|jDS)Nz z (does not exist yet)z ({})r-c34K|]}|jywrlrrres rGrz!Table.__repr__..s-Kaff-K)rrrrr6rs rGrzTable.__repr__sW$$ II{{}(  ^^DII-Kdll-K$KL   rFc|jjrJ|jj|jg}|r"t t |j S|jS)zWCount of the rows in this table - optionally from the table count cache, if configured.)rryr1rnextiterrmr)rncountss rGrz Table.countsS 77 # #WW**DII;7FD122!!rFcN|j|jjvSrl)rrrrs rGrz Table.existssyyDGG//111rFcz|jDcgc]}|js|j}}|sdg}|Scc}w)z#Primary key columns for this table.rc)r6r#r)rnr&namess rGrmz Table.pkss:,0<<H6<<HHIE Is88c<td|jD S)zXDoes this table use ``rowid`` for its primary key (no other primary keys are specified)?c3:K|]}|js|ywrl)r#)rr&s rGrz"Table.use_rowid..sI&FLLvIs)rr6rs rG use_rowidzTable.use_rowidsIDLLIIIIrF pk_valuesct|ttfs|g}|j}t |dk(r|dn|}t |t |k7rrr index_nameindex_name_quoted column_sqlr6r9rrr<defaults rGrz Table.indexes;s(..tyy9772237 )CVJ",,S1 j)  1778IJJG$(GGOOJ$?$H$H$J % sDt$ %$C N+.1 = C C E ' Wc>&CH ' NN5<3< (! )"rFcdj|j}g}|jj|D]}|d}|j dsdj|n|}dj|}g}|jj |j D]}|jt||jt|||S)zPList of indexes defined on this table using the more detailed ``XIndex`` format.rrrrzPRAGMA index_xinfo({})) rrrr<rrrrr8r7) rnr>rrrrr index_columnsinfos rGxindexeszTable.xindexesSs(..tyy9772237 >CVJ",,S1 j)  2889JKJM 3<<> :$$\4%89 : NN6*m< = >rFc|jjd|jfjDcgc] }t | c}Scc}w)z'List of triggers defined on this table.zUselect name, tbl_name, sql from sqlite_master where type = 'trigger' and tbl_name = ?)rrrrr=rs rGrzTable.triggersfsL WW__$ hj   QK   sA cj|jDcic]}|j|jc}Scc}w)zE``{trigger_name: sql}`` dictionary of triggers defined on this table.rrs rGr zTable.triggers_dictrr r c|jDcic].}|j |jt|j0c}Scc}w)zX``{column_name: default_value}`` dictionary of default values for columns in this table.)r6r"r_decode_default_valuers rGrzTable.default_valueswsI ,, ##/ KK.v/C/CD D   s3Ac|jjddjj}|jdDcgc]}|j}}d|vScc}w)zIs this a STRICT table?rrSTRICT)rrrr)rn table_suffixr table_optionss rGr{z Table.strictsd{{((-b1779??A 0<0B0B30GHH H=((IsA+rTrc|jDcic]\}}|| }}}|jj5|jj|j||||||||| | | | | |ddd|Scc}}w#1swY|SxYw)aG Create a table with the specified columns. See :ref:`python_api_explicit_create` for full details. :param columns: Dictionary mapping column names to their types, for example ``{"name": str, "age": int}`` :param pk: String name of column to use as a primary key, or a tuple of strings for a compound primary key covering multiple columns :param foreign_keys: List of foreign key definitions for this table :param column_order: List specifying which columns should come first :param not_null: List of columns that should be created as ``NOT NULL`` :param defaults: Dictionary specifying default values for columns :param hash_id: Name of column to be used as a primary key containing a hash of the other columns :param hash_id_columns: List of columns to be used when calculating the hash ID for a row :param extracts: List or dictionary of columns to be extracted during inserts, see :ref:`python_api_extracts` :param if_not_exists: Use ``CREATE TABLE IF NOT EXISTS`` :param replace: Drop and replace table if it already exists :param ignore: Silently do nothing if table already exists :param transform: If table already exists transform it to fit the specified schema :param strict: Apply STRICT mode to table ) rMr=rNrOrPrQrRrSrTrrrr{N)rrrrrsr)rnr6rMr=rNrOrPrQrRrSrTrrrr{rrs rGcreatez Table.createsJ5s rG duplicatezTable.duplicates {{}F499+_=> > WW\\ !JQQ"iiRC GGOOC  ! wwx   ! !s 9BB rrenamerrMrOrPdrop_foreign_keysrr=rN keep_tablerr4rr5rr6c T|jsJd|j||||||||| | |  } |jjdj d} | r|jjd|jj 5| D]}|jj|| r|jjdddd| r|jjd|S#1swY(xYw#| r|jjdwwxYw) aR Apply an advanced alter table, including operations that are not supported by ``ALTER TABLE`` in SQLite itself. See :ref:`python_api_transform` for full details. :param types: Columns that should have their type changed, for example ``{"weight": float}`` :param rename: Columns to rename, for example ``{"headline": "title"}`` :param drop: Columns to drop :param pk: New primary key for the table :param not_null: Columns to set as ``NOT NULL`` :param defaults: Default values for columns :param drop_foreign_keys: Names of columns that should have their foreign key constraints removed :param add_foreign_keys: List of foreign keys to add to the table :param foreign_keys: List of foreign keys to set for the table, replacing any existing foreign keys :param column_order: List of strings specifying a full or partial column order to use when creating the table :param keep_table: If specified, the existing table will be renamed to this and will not be dropped z/Cannot transform a table that doesn't exist yetr3zPRAGMA foreign_keysr zPRAGMA foreign_keys=0;zPRAGMA foreign_key_check;NzPRAGMA foreign_keys=1;)r transform_sqlrrrr)rnrr4rrMrOrPr5rr=rNr6rpragma_foreign_keys_was_onr>s rGrzTable.transformsF{{}OOO}!!/-%%!"  &*WW__5J%K%T%T%V & " :) 89 A)CGGOOC().GGOO$?@  A* 89  A A* 89*s%!3DAC;D;DD D') rr4rrMrOrPr5rr=rN tmp_suffixr6r:c  |xsi}xsi|xs t}g} | ,| td| td| j| ng} |jD]>\}}}}|||vs| j t |j |xs|||@||jj|j|D]d}| j t |jj |jxs |j|j|jfdj|j| xs#tjdj!}t#|j$j'}g}|Dcic]\}}|| }}}|D]O\}}|j |xs|}||vr||=#j |xs|}|j ||f|||<Q|t(ur4t+fd|j,D}t/|dk(r|d}n|}|j,Dchc]E}|j0r7|j|vr)j |jxs |jG}}t3|t4rY|j'D]E\}}j |xs|}|dur||vr|j7|5|j9|GnMt3|tr|j;fd |Dn"|snJd jt=||j,Dcic]P}|j>B|j|vr4j |jxs |j|j>R} }|F| j;|j'D!cic]\}}!j |xs||!c}!}| "| D"cgc]}"j |"xs|"} }"g}#|#j |jjA|t5|||| | | |jB jEg}$g}%|j'D]'\}&}'|%j |&|$j |')d |$vr$|$jGdd |%jGdd d j||jdjId|%DdjId|$D}(|#j |(| r,|#j dj|j| n*|#j dj|j|#j dj||j|jJD]})|)jLdk7s|jjOdd|)jijQdd}*|*&tSd|)jd|jd| r|#j d|)jd|)j,D]D}"|"jUvs|"|vstSd|)jd|"d|jd|*d |#j |*|#Scc}}wcc}wcc}wcc}!}wcc}"w) aD Return a list of SQL statements that should be executed in order to apply this transformation. :param types: Columns that should have their type changed, for example ``{"weight": float}`` :param rename: Columns to rename, for example ``{"headline": "title"}`` :param drop: Columns to drop :param pk: New primary key for the table :param not_null: Columns to set as ``NOT NULL`` :param defaults: Default values for columns :param drop_foreign_keys: Names of columns that should have their foreign key constraints removed :param add_foreign_keys: List of foreign keys to add to the table :param foreign_keys: List of foreign keys to set for the table, replacing any existing foreign keys :param column_order: List of strings specifying a full or partial column order to use when creating the table :param tmp_suffix: Suffix to use for the temporary table name :param keep_table: If specified, the existing table will be renamed to this and will not be dropped z5Cannot specify both foreign_keys and add_foreign_keysz6Cannot specify both foreign_keys and drop_foreign_keysz {}_new_{}c3K|]:}|jsj|jxs |j<ywrl)r#rxr)rr`r4s rGrz&Table.transform_sql..os5 12agg 166",aff, s A.Arr Fc3HK|]}j|xs|ywrl)rx)rkr4s rGrz&Table.transform_sql..s!(P!&**Q-*<1*<(Ps"z3not_null must be a dict or a set or None, it was {})rMrOrPr=rNr{rczOINSERT INTO [{new_table}] ({new_cols}) SELECT {old_cols} FROM [{old_table}];r-c3>K|]}dj|ywrrrcols rGrz&Table.transform_sql..Fcv}}S1Frc3>K|]}dj|ywrrrAs rGrz&Table.transform_sql..rCr)r1 old_tableold_colsnew_colsz ALTER TABLE [{}] RENAME TO [{}];zDROP TABLE [{}];rMzJSELECT sql FROM sqlite_master WHERE type = 'index' AND name = :index_name;rzIndex 'z ' on table 'z' does not have a CREATE INDEX statement. You must manually drop this index prior to running this transformation and manually recreate the new index after running this transformation.zDROP INDEX IF EXISTS [z];z ' column 'z' is not in updated table 'z'. You must manually drop this index prior to running this transformation and manually recreate the new index after running this transformation. The original index sql statement is: `z.`. No changes have been applied to this table.)+r ValueErrorextendr=rr-rxrrLrr&r/r0rrurandomhexr;rrrDEFAULTrr6rr!rrrrupdatertr"rr{rrwrrr4rrr@r)+rnrr4rrMrOrPr5rr=rNr:r6create_table_foreign_keysr%r&r/r0rAnew_table_namecurrent_column_pairsnew_column_pairs_ copy_from_tortype_r pks_renamedrecreate_table_not_nullr<rcreate_table_defaultsvrBrrGrFrrcopy_sqlr_ index_sqls+ ` rGr8zTable.transform_sqlsD 2}su?A!  #+ K!, L & , ,\ :)+ %<@RYYNNOO %++ IIz8RZZ]%6%6%8  $D$5$5$;$;$=>8LM961M M/ *KD%IIdO,uEt|!$'zz$'/4H  # #Xu$5 6!)L  * = 6:ll K;1$ ^  \\! yyvvT! JJqvv  (!&& (! ! h %&nn. 3 Ujjo,E>c-B&B)005)--c2  3# & ! ( ((Px(P P  OVVX 5 \\! *qvvT/AZZ  )166AOO ;! !   ! ( (3;>>3CD41aA#!Q&D   #>JKsFJJsO2s2KLK GG $ $%&..6){{ % eg &,,. !JE3 OOE " OOC  ! ( " OOAw ' OOAw 'ell$iiYYFXFFYYFXFF m  H  KK299$))ZP  KK*11$))< = . 5 5ndii P \\ 'E||t# GGOOd!5::.(*Q !"# $(!%**\$))Epp KK"8 B GH ==Cfkkm+sd{,%ejj\C5@[\`\e\e[fgEFOKO} I&- '. wN(! 0! ELs  Z<A [A[ [ 8[r% fk_columnc xsit|tr|g}t|jjj sK|]}dj|ywrrrs rGrz Table.extract..s(Raq)9(Rrc3>K|]}dj|ywrrrs rGrz Table.extract..s$G!V]]1%5$Gr) lookup_tablelookup_columns table_colsr%z^UPDATE [{table}] SET [{magic_lookup_column}] = (SELECT id FROM [{lookup_table}] WHERE {where})rc 3K|]6}djj|j|xs|8yw)z:[{table}].[{column}] IS [{lookup_table}].[{lookup_column}])r%rar& lookup_columnN)rrrx)rr&r4rnr%s rGrz Table.extract..-sK# QWW"ii%*%&,jj&8&BF X#s >EET$"3"3"8"8":;  *'*wwu~ 6!6 %nnY 1 8I8I8KL !--335% Sg~ZZ_ #c )% !%    06689BB))//1%QXX8   c0    ?FFs6::c?1c1FF!!.T!R  s z z"#yy(R>(RR99$Gw$GGii {   +S1  l s sii$7"ll##*# t  "  ,Avv %8 %L##$78..##AFF+  , W'3%   Yt4 U% 0Gs $MM!rr3rrc|;dj|jjdddj|}g}|D]7}t |t rd} nd} |j | j|9d} d} | rdj|| n|} tjdjj| |jd j||rd nd |rd nd } |jj|  |r|jj!| |S#t$rI} | jd}|r1|jdr |jdr| d} n| dz } Yd} ~ | d} ~ wwxYw)aT Create an index on this table. :param columns: A single columns or list of columns to index. These can be strings or, to create an index using the column in descending order, ``db.DescIndex(column_name)`` objects. :param index_name: The name to use for the new index. Defaults to the column names joined on ``_``. :param unique: Should the index be marked as unique, forcing unique values? :param if_not_exists: Only create the index if one with that name does not already exist. :param find_unique_name: If ``index_name`` is not provided and the automatically derived name already exists, keep incrementing a suffix number to find an available name. :param analyze: Run ``ANALYZE`` against this index after creating it. See :ref:`python_api_create_index`. Nz idx_{}_{}rrRz [{}] descrlr^z CREATE {unique}INDEX {if_not_exists}[{index_name}] ON [{table_name}] ({columns}); r-zUNIQUE rhrn)rrr6r3rTr zindex z already existsrr)rrrrrrhrtextwrapdedentrrrrargsrrr)rnr6rr3rTrrror&fmtsuffixcreated_index_namer>eargs rGrzTable.create_indexMs.  $++ !!#s+SXXg->J  3F&),!   szz&1 2  3!6<z62*  1#yy IIk2(.9B6C"2    $  GGOO. / #$ ffQi$x0 %67~!"! G s0D++ E=4=E86E88E=rrrAfk_colnot_null_defaultcd}|||jjvrtdj||6||j|jvrstdj|||j|j Dcgc]}|j s|}}|r|dj}|dj}nd}d}|t}d} |*dj|jj|} dj|j||xs t|| rd | znd } |jj| ||j||||Scc}w) a Add a column to this table. See :ref:`python_api_add_column`. :param col_name: Name of the new column :param col_type: Column type - a Python type such as ``str`` or a SQLite type string such as ``"BLOB"`` :param fk: Name of a table that this column should be a foreign key reference to :param fk_col: Column in the foreign key table that this should reference :param not_null_default: Set this column to ``not null`` and give it this default value Nztable '{}' does not existztable '{}' has no column {}r rcrLzNOT NULL DEFAULT {}zKALTER TABLE [{table}] ADD COLUMN [{col_name}] {col_type}{not_null_default};rrh)r%rrrv)rrrWrrr6r#rr rPrryrrh) rnrrrArurv fk_col_typererm not_null_sqlr>s rGrzTable.add_columnsi" >,,.. !  2v 6 /Bs E$ E$c |jjdj|jy#tj $r|sYywxYw)zz Drop this table. :param ignore: Set to ``True`` to ignore the error if the table does not exist zDROP TABLE [{}]Nrrrrrrrnrs rGrz Table.dropsG   GGOO-44TYY? @''  47AAr&c|j}|g}|jdr<|dd}|j||jds:|j|dzn%|jds|j|dz|jj Dcic]}|j|}}|D] }||vs||cSt dj |t|cc}w)a For a given column, suggest another table that might be referenced by this column should it be used as a foreign key. For example, a column called ``tag_id`` or ``tag`` or ``tags`` might suggest a ``tag`` table, if one exists. If no candidates can be found, raises a ``NoObviousTable`` exception. :param column: Name of column _idNr z7No obvious foreign key table for column '{}' - tried {})lowerrrrrr[rrt)rnr& possibilitiescolumn_without_idtexisting_tablesr%s rGrHzTable.guess_foreign_tables ??5 ! &s   !2 3$--c2$$%6%<=%  # .151D1D1FGA1779a<GG" .E'&u-- . E L L]+   Hs"C2r/c|j|jDcgc]}|js|}}t|dk7rt dj ||dj Scc}w)Nrz2Could not detect single primary key for table '{}'r )rr6r#rr`rr)rnr/rerms rGrIzTable.guess_foreign_columnsf''+.66BQ!''qBB s8q=DKKKX q6;;  Cs A+A+r0c4|jvrtdj|j|j |j j Dcgc]}|jk(s|c}s dk7rtdjtfd|jDr |r|Stdj|j j|jfg|Scc}w)a Alter the schema to mark the specified column as a foreign key to another table. :param column: The column to mark as a foreign key. :param other_table: The table it refers to - if omitted, will be guessed based on the column name. :param other_column: The column on the other table it - if omitted, will be guessed. :param ignore: Set this to ``True`` to ignore an existing foreign key - otherwise a ``AlterError`` will be raised. zNo such column: {}rcrfc3|K|]3}|jk(r"|jk(r|jk(r|5ywrlrrs rGrz(Table.add_foreign_key..& s= yyF"+-</  rz*Foreign key already exists for {} => {}.{}) rrWrrHrIrr6rrr=r)rnr&r/r0rres ``` rGrhzTable.add_foreign_key s ** *188@A A  226:K  44[AL!GGK088SqAFFlcdDchc]4}dj|jj|j|6}}|j |j j Scc}w)zK|]}dj|ywrrrs rGrz#Table.enable_fts.. s!Dq&--"2!Drz tokenize='{}',rh)r%r6rrFz{}_ftsT)_ai_ad_auc3>K|]}dj|yw)zold.[{}]Nrrs rGrz#Table.enable_fts..  G!!2!21!5 Grc3>K|]}dj|yw)znew.[{}]Nrrs rGrz#Table.enable_fts.. rra CREATE TRIGGER [{table}_ai] AFTER INSERT ON [{table}] BEGIN INSERT INTO [{table}_fts] (rowid, {columns}) VALUES (new.rowid, {new_cols}); END; CREATE TRIGGER [{table}_ad] AFTER DELETE ON [{table}] BEGIN INSERT INTO [{table}_fts] ([{table}_fts], rowid, {columns}) VALUES('delete', old.rowid, {old_cols}); END; CREATE TRIGGER [{table}_au] AFTER UPDATE ON [{table}] BEGIN INSERT INTO [{table}_fts] ([{table}_fts], rowid, {columns}) VALUES('delete', old.rowid, {old_cols}); INSERT INTO [{table}_fts] (rowid, {columns}) VALUES (new.rowid, {new_cols}); END; c3>K|]}dj|ywrrrs rGrz#Table.enable_fts.. s%H1fmmA&6%Hr)r%r6rFrG)rmrnrrrrrrrrr disable_ftsr populate_fts)rnr6rrrrcreate_fts_sqlshould_recreate fts_schemarqexpected_triggersrexisting_triggers has_triggersrFrGrs rG enable_ftszTable.enable_ftst s( OO  UW Vii !DG!DD'DL/66x@RT  "  twwxtyy9:AAC!;<CCJ^+"&BW XV!3 X  X15 ?A ?  ?,778IJL."&"      n- '" yy Gw GGHyy Gw GGH  )) II%H%HH%% ! . GG ! !( + U!Y ?s !HH ctjdjj|jdj d|D}|j j||S)z Update the associated SQLite full-text search index with the latest data from the table for the specified columns. :param columns: Columns to populate the data for zz INSERT INTO [{table}_fts] (rowid, {columns}) SELECT rowid, {columns} FROM [{table}]; r-c3>K|]}dj|ywrrrs rGrz%Table.populate_fts.. s2U6==3C2Ur)r%r6)rmrnrrrrrr)rnr6r>s rGrzTable.populate_fts sc OO   UW Vii2UW2U)U  c" rFc |j}|r|j|jtjdj j |}g}|jj|jD]}|j|d|jj5|D],}|jjdj |. ddd|S#1swY|SxYw)zQRemove any full-text search index and related triggers configured for this table.z SELECT name FROM sqlite_master WHERE type = 'trigger' AND sql LIKE '% INSERT INTO [{}]%' r zDROP TRIGGER IF EXISTS [{}]N) detect_ftsrrrmrnrrrrrr)rn fts_tabler>rr trigger_names rGrzTable.disable_fts sOO%  GGI  # # % OO  UW VI   77??3'002 )C  Q ( ) WW\\ T - T  = D D\ RS T T  T s <2C99Dc|j}| |j}|jjdj ||S)zRRun the ``rebuild`` operation against the associated full-text search index table.z3INSERT INTO [{table}]([{table}]) VALUES('rebuild');r)rrrrrrnrs rG rebuild_ftszTable.rebuild_fts sKOO%   I  A H H I   rFcNtjdj}dj|jdj|j|jd}|j j ||j}t|dk(ry|ddS)zCDetect if table has a corresponding FTS virtual table and return itac SELECT name FROM sqlite_master WHERE rootpage = 0 AND ( sql LIKE :like OR sql LIKE :like2 OR ( tbl_name = :table AND sql LIKE '%VIRTUAL TABLE%USING FTS%' ) ) z&%VIRTUAL TABLE%USING FTS%content=[{}]%z&%VIRTUAL TABLE%USING FTS%content="{}"%)likelike2r%r N) rmrnrrrrrrr)rnr>rors rGrzTable.detect_fts soo    %' =CCDIIN=DDTYYOYY  wwsD)224 t9>71: rFc|j}|9|jjdjj ||S)zSRun the ``optimize`` operation against the associated full-text search index table.zT INSERT INTO [{table}] ([{table}]) VALUES ("optimize"); r)rrrrrrs rGoptimizezTable.optimize! sHOO%  GGOO#  rFrrrr include_rankc :|jdk(rdndd}dj}|r2djd|D}djfd|D}|j} | s Jd j|j|j| j } t jd j} | d k(rd j| } n+|jjd j| } |r |d| zdzz }d} || dj|z } || dj|z } | j|j|rdj|nd||| |xs| | jjS)a " Return SQL string that can be used to execute searches against this table. :param columns: Columns to search against :param order_by: Column or SQL expression to sort by :param limit: SQL limit :param offset: SQL offset :param where: Extra SQL fragment for the WHERE clause :param include_rank: Select the search rank column in the final query original original_rz[{}].*z , c3>K|]}dj|ywrrrs rGrz#Table.search_sql..F s,O!V]]1-=,Orz, c3BK|]}dj|yw)z [{}].[{}]Nr)rrers rGrz#Table.search_sql..G s#545 ""8Q/5sz1Full-text search is not configured for table '{}'a with {original} as ( select rowid, {columns} from [{dbtable}]{where_clause} ) select {columns_with_prefix} from [{original}] join [{fts_table}] on [{original}].rowid = [{fts_table}].rowid where [{fts_table}] match :query order by {order_by} {limit_offset} FTS5z [{}].rankz$rank_bm25(matchinfo([{}], 'pcnalx'))z rankrhrrz where {})dbtable where_clauserr6columns_with_prefixrr limit_offset) rrrrrr*rmrnrr)rnr6rrrrrrocolumns_with_prefix_sqlrr*r>rank_implementationrrs @rG search_sqlzTable.search_sql. s(#'))z"9;z "*//("; ',,,Ow,OOK&/nn59@5' #OO%  MTT II  y#ggi0DDoo  & %'' ( & ("-"4"4Y"?  GG & & ("H"O"O#   #y3F'F'P P #   K..u5 5L   L//7 7LzzII;@)007b 74!4%++-   %' rFqrrc #vKd| r|jj|n|i} |rd|vr td|r| j||jj |j ||||||| } | j D cgc]} | d }} | D]} tt|| ycc} ww)a3 Execute a search against this table using SQLite full-text search, returning a sequence of dictionaries for each row. :param q: Terms to search for :param order_by: Defaults to order by rank, or specify a column here. :param columns: List of columns to return, defaults to all columns. :param limit: Optional integer limit for returned rows. :param offset: Optional integer SQL offset. :param where: Extra SQL fragment for the WHERE clause :param where_args: Arguments to use for :param placeholders in the extra WHERE clause :param include_rank: Select the search rank column in the final query :param quote: Apply quoting to disable any special characters in the search query See :ref:`python_api_fts_search`. rzJ'query' is a reserved key and cannot be passed to where_args for .search())rr6rrrrr N) rrrHrMrrrrr)rnrrr6rrrrrrrorrers rGsearchz Table.search| s8**1-A> 'Z/\   KK # OO!)     "(!3!34A1Q444 *Cs7C() ) *5sBB9 B4&B9c4|tur|j|S|Srl)rLr)rnr<rs rGvalue_or_defaultzTable.value_or_default s&+w&6t~~c"AEArFct|ttfs|g}|j||jDcgc]}dj |}}dj |j dj|}|jj5|jj||ddd|Scc}w#1swY|SxYw)z Delete row matching the specified primary key. :param pk_values: A single value, or a tuple of values for tables that have a compound primary key r z$delete from [{table}] where {wheres}r )r%rN) rr;rrxrmrrrrrr)rnr rrr>s rGr#z Table.delete s )dE]3" I <@HHE*##G,EE4;;))GLL$8< WW\\ , GGOOC + , F , sB<CC c|js|Sdj|j}||d|zz }|jj ||xsg|r|j |S)a Delete rows matching the specified where clause, or delete all rows in the table. See :ref:`python_api_delete_where`. :param where: SQL where fragment to use, for example ``id > ?`` :param where_args: Parameters to use with that fragment - an iterable for ``id > ?`` parameters, or a dictionary for ``id > :id`` :param analyze: Set to ``True`` to run ``ANALYZE`` after the rows have been deleted. zdelete from [{}]r)rrrrrr)rnrrrr>s rGr6zTable.delete_where sc {{}K '' 2   9u$ $C Z-2.  LLN rFupdatesc 2|xsi}|xsi}t|ttfs|g}|j||s|Sg}g}g}|j}t |j |jD]P\} } |jdj| |j| d|jt| R|D cgc]} dj| }} |j|dj|jdj|dj|} |jj5 |jj!| |j"} | d k(sJ d d d t+|d k(r |d |_|S||_|Scc} w#t$$rW}|rJd|j&d vr9|j)|g|jj!| |j"} nYd }~d }~wwxYw#1swYxYw) a Execute a SQL ``UPDATE`` against the specified row. See :ref:`python_api_update`. :param pk_values: The primary key of an individual record - can be a tuple if the table has a compound primary key. :param updates: A dictionary mapping columns to their updated values. :param alter: Set to ``True`` to add any missing columns. :param conversions: Optional dictionary of SQL functions to apply during the update, for example ``{"mycolumn": "upper(?)"}``. [{}] = {}r/r z*update [{table}] set {sets} where {wheres}r-r )r%setsr columnr Nr)rr;rrxrmrurrrrrjsonify_if_neededrIrrrrrrowcountrroadd_missing_columnsrr)rnr rrrrorrrmr<rrr>rrss rGrMz Table.update s&-R!'R )dE]3" I Khhglln-!--/ 2JC KK **3 S0IJ K KK)%0 1 2=@@*##G,@@ I:AA))$))D/',,v:NB WW\\ ! 77??35>>q= = !(+3x1}y|  ;D  'A$ i166!94,,gY7#wwsD9BBH   ! !s= F% H  &F*2H * H 3A HH H  H  Hroutput output_typemulti show_progress skip_falsec  t|tr|g}|r|j|d|||| S|9t|dk(sJd||jvr|j ||xsd|j ||t|z} t| | 5 fd} j} | d k(rd tt} |jj| | d j|jd j|Dcgc]}dj|xs||| c}|dj|nd}|jj 5|jj#||xsg|r|j%|dddddd|Scc}w#1swYxYw#1swY|SxYw)a Apply conversion function ``fn`` to every value in the specified columns. :param columns: A single column or list of string column names to convert. :param fn: A callable that takes a single argument, ``value``, and returns it converted. :param output: Optional string column name to write the results to (defaults to the input column). :param output_type: If the output column needs to be created, this is the type that will be used for the new column. :param drop: Should the original column be dropped once the conversion is complete? :param multi: If ``True`` the return value of ``fn(value)`` will be expected to be a dictionary, and new columns will be created for each key of that dictionary. :param where: SQL fragment to use as a ``WHERE`` clause to limit the rows to which the conversion is applied, for example ``age > ?`` or ``age > :age``. :param where_args: List of arguments (if using ``?``) or a dictionary (if using ``:age``). :param show_progress: Should a progress bar be displayed? See :ref:`python_api_convert`. r )rrrrNrz-output= can only be used with a single columnrO)lengthsilentcTjdr|s|St|S)Nr)rMr)rXbarrrs rG convert_valuez$Table.convert..convert_valueI s' 1 aH(A//rFzlambda_rz#update [{table}] set {sets}{where};r-z)[{output_column}] = {fn_name}([{column}])) output_columnr&rz where {}rh)r%rrr)rrP_convert_multirrrrr rBabshashrrrrrrrr)rnr6rrrrrrrrr todo_countrrr&r>rs ` ` @rGconvertz Table.convert s> gs #iG && %+ '   w<1$ U&U U$T... (=v>%%eZ83w<G  }3D E 1 0 kkG*$#CRM?3 GG % %m' % B7>>iiYY'.  # DJJ*0*:F#)$+K 493Dk((/"? C 1Z%526NNN0 15 1<  1 15 1< s7A2F9 F(+6F9!5F-F9(F9-F6 2F99Gci}i}|jDcgc]}|js|j} }| sdg} t|j| d5} |j dj d| gzD||D]tfd| D} t| dk(r| d } ||} | t| ts t| | rO| jD]7\} }|j| tjt!|9| || <| j#d dddt%|}|jD]&\}}||j&vs|j)||(t|j| d 5} |j*j,5|jD](\}}|j#||| j#d*|r|j/f ddddddycc}w#1swYxYw#1swY#xYw#1swYyxYw) Nrcz 1: Evaluating)rrlabelr-c3>K|]}dj|ywrrrs rGrz'Table._convert_multi..u s!3>FMM+.!r)rrrc3(K|] }| ywrlrErs rGrz'Table._convert_multi..{ s52s2w5rrr z 2: Updatingr)r6r#rr rrrrrrrrjrrrrrr rMrrrrrr)rnr&rrrrr pk_to_valuesnew_column_typesrmrrrmr<rcolumns_to_createrirjrMrrs @rGrzTable._convert_multig s= )-Fvv{{FF)C ::-&7   yy!CF&>!% ' 555v;!##AYFCK%j.F(00&,llnQ U(33C?CCDKPQ+1L( 1 #  .33CD(9(?(?(A : $K$"3"33 [9 : ::-&7}  3  3#/#5#5#7"KBKKG,JJqM"NN N2  3 3 3CG  @ 3 3 3 3s<HHC%HH56AH)H5H&)H2 .H55H>c d g} |r|d}t|}|D]g}D]h}tj|||k7rdn t|}||vr%||}|j|j d|i}|j |j| j |g}|rt|tr|g}n|}d|_ | D]}tt|t|}|r|j|dj|jdj!|Dcgc]}dj|c}dj!|Dcgc]}dc}}|j ||Dcgc]}| c}|xsgDcgc]}dc}zfDcgc] }||vs| }}|rd j|jdj! fd |Dd j!d |D }|j ||Dcgc]}| c}|Dcgc]}| c}zf| dk(sxt#fd|D|_ t%|jdk(s|jd|_ |Sd}| rd}n| rd}dj'j||jdj!dDdj! fd|D}tt)j*| }||fg}|Scc}wcc}wcc}wcc}wcc}wcc}wcc}w)NrVrz?INSERT OR IGNORE INTO [{table}]({cols}) VALUES({placeholders});r-rlr/)r%cols placeholdersrhz+UPDATE [{table}] SET {pairs} WHERE {wheres}c3bK|]&}dj|j|d(yw)rr/N)rrx)rrBrs rGrz8Table.build_insert_queries_and_params.. s1( #(..sKOOC4MN(s,/rc3>K|]}dj|yw)r Nr)rrMs rGrz8Table.build_insert_queries_and_params.. s+PbJ,=,=b,A+Pr)r%pairsrrc3(K|] }| ywrlrE)rrMrecords rGrz8Table.build_insert_queries_and_params.. s(B(Brr z OR REPLACE z OR IGNORE zW INSERT {or_what}INTO [{table}] ({columns}) VALUES {rows}; c3>K|]}dj|ywrrrs rGrz8Table.build_insert_queries_and_params.. s!Hq&--"2!Hrc 3K|]C}djdjDcgc]}j|dc}Eycc}ww)z({placeholders})r-r/)rN)rrrx)rrrB all_columnsrs rGrz8Table.build_insert_queries_and_params.. sV  '--%)YYBMN3[__S#6N&.OsAA A)or_whatr%r6r)rqrrxrrlookuprrrPrrrr;rIrrrrrr itertoolschain)rnrSchunkrrQrRupsertrMrOrnum_records_processedrrrm record_valuesr<rr|queries_and_paramsrmrr`r>rBrRset_colssql2r flat_valuesrs ` ` @rGbuild_insert_queries_and_paramsz%Table.build_insert_queries_and_params sY$ wG#H- )FM" ,)JJ #g~!!,V_!E  (?$,SMM GGM2997E:JKE$$U+ , MM- (# )&  "c"dDL!'% 7 c+}=>#Cy  ''1W^^))l#KFMM!$4#KL!%+FAC+F!G_ #))#636#;6x~SU9W"9WWX,7IC#S.CIIHOO"ii"ii('/( '||+PC+PP PD'-- 4<=SVC[=478bvbz89)A-#((Bc(B#BDL4<<(A-'+||A K% 7~"!/G'&ii !HK!HHYY #(    y78K#& "4!5 !!m$L+F79WJ>8s0L L0 L  L L#'L# L(  L-c|j|||||||| | | | | }|jj5d}|D]"\}} |jj||}$| dk(r|s|j|_ |j|_ |s|rv|jrjt|jd|jgd|r ||_ n4t|tr ||_ nt!fd|D|_ dddy#t$r}|r?d|j dvr.|j ||jj||}np|j ddk(r]|dt|dz}|t|dzd}|j||||||||| | | | | |j||||||||| | | | | nYd}~d}~wwxYw#1swYyxYw)Nrr ztoo many SQL variablesrrz rowid = ?c3(K|] }| ywrlrE)rr`rs rGrz%Table.insert_chunk..c s,@SV,@r)rrrrrrorr insert_chunk lastrowidrrr;rrrPr)rnrrSrrrQrRrrMrOrrrrrrrrrs first_half second_halfrs @rGrzTable.insert_chunk s "AA          !    WW\\; AF!3-  v,!WW__UF;F- \%)&"("2"2# rtt{T__>%*+ ))!$&'#+"$'1#" ))!$''#+"$'1#""U* ; Az s7 GD B%G GB1G G GGGrcD|j|g|||||||| | | | | ||S)a Insert a single record into the table. The table will be created with a schema that matches the inserted record if it does not already exist, see :ref:`python_api_creating_tables`. - ``record`` - required: a dictionary representing the record to be inserted. The other parameters are optional, and mostly influence how the new table will be created if that table does not exist yet. Each of them defaults to ``DEFAULT``, which indicates that the default setting for the current ``Table`` object (specified in the table constructor) should be used. :param record: Dictionary record to be inserted :param pk: If creating the table, which column should be the primary key. :param foreign_keys: See :ref:`python_api_foreign_keys`. :param column_order: List of strings specifying a full or partial column order to use when creating the table. :param not_null: Set of strings specifying columns that should be ``NOT NULL``. :param defaults: Dictionary specifying default values for specific columns. :param hash_id: Name of a column to create and use as a primary key, where the value of that primary key will be derived as a SHA1 hash of the other column values in the record. ``hash_id="id"`` is a common column name used for this. :param alter: Boolean, should any missing columns be added automatically? :param ignore: Boolean, if a record already exists with this primary key, ignore this insert. :param replace: Boolean, if a record already exists with this primary key, replace it with this new record. :param extracts: A list of columns to extract to other tables, or a dictionary that maps ``{column_name: other_table_name}``. See :ref:`python_api_extracts`. :param conversions: Dictionary specifying SQL conversion functions to be applied to the data while it is being inserted, for example ``{"name": "upper(?)"}``. See :ref:`python_api_conversions`. :param columns: Dictionary over-riding the detected types used for the columns, for example ``{"age": int, "weight": float}``. :param strict: Boolean, apply STRICT mode if creating the table. )rMr=rNrOrPrQrRrrrrSrr6r{r7)rnrrMr=rNrOrPrQrRrrrrSrr6r{s rGrwz Table.insertg sJf H%%+#  rFc|jd|}|jd|}|jd|}|jd|}|jd|}|jd|}|jd|}|jd| } |jd | } |jd | } |jd | } |jd |}|jd |xsi}|jd|}|jd|}| r|d}|r|s |s td|r |rJd| r|d}|r|}| r | rJdg}d}d}t|}t|} t |}t |j}|tksJdjttdt|t|z}d|_ d|_ | rD|jr4|jj!dj|j"t%t'j(|g||D]}t+|}|t |z }|r|js;t-|}|j/|xsi|j1|||||||| || t3}|D]!}|j/|j#t+t5|}|r2|j7d|n|D]}||Dcgc] }||vs| c}z }d}|j9| ||||| |||||| | |r|j;|S#t $r|cYSwxYwcc}w)a Like ``.insert()`` but takes a list of records and ensures that the table that it creates (if table does not exist) has columns for ALL of that data. Use ``analyze=True`` to run ``ANALYZE`` after the insert has completed. rMr=rNrOrPrrQrRrrrrSrr6r{NrVzupsert() requires a pkzUse either pk= or hash_id=z0Use either ignore=True or replace=True, not bothTr z%Rows can have a maximum of {} columnsrzDELETE FROM [{}];)rNrOrPrQrRrSr{F)rrdfix_square_bracesrr StopIterationrrSQLITE_MAX_VARSrmaxminrrrrrrrrrr;rrMr/rsortedrwrr)rnrecordsrMr=rNrOrPrrQrRrrrtruncaterSrr6rrr{rfirstr first_record num_columnsr column_typesall_columns_setrr&s rGr7zTable.insert_all s8 " "4 ,,,^\J ,,^\J ((X>((X>**<D '' 7;//0A?S%%gu5&&x8'' 7;((X>++M;GM2 '' 7;&&x8 wG 2g$%=> >A%AA# G B w > = >   !#G,w- =L,++-. ? * K 2 9 9/ J K *C O{,JKL   GGOO/66tyyA BIOO\NGDjQ0 EKE !SZ / !{{}#7#>L '' 26KK$$%1!)!) '(7!)% #&%#:F#**6;;=9:"6/#:; &&q'2#F-3$#)v[7P$K E   % E0 d  LLN A K N$s' M M) M)  M&%M&c@|j|g|||||||| | | | |  S)z Like ``.insert()`` but performs an ``UPSERT``, where records are inserted if they do not exist and updated if they DO exist, based on matching against their primary key. See :ref:`python_api_upsert`. ) rMr=rNrOrPrQrRrrSrr6r{) upsert_all)rnrrMr=rNrOrPrQrRrrSrr6r{s rGrz Table.upsert3 sC, H%%+#  rFcD|j||||||||| | | | | d||S)zM Like ``.upsert()`` but can be applied to a list of records. T)rMr=rNrOrPrrQrRrrSrr6rrr{r)rnrrMr=rNrOrPrrQrRrrSrr6rr{s rGrzTable.upsert_allY sJ* %%!+#!  rFrct|}|jDchc]}|j}}|jD]*\}}|j|vs|j ||,|Scc}wrl)rrrrrr)rnrneeded_columnsrecurrent_columnsrrs rGrzTable.add_missing_columns sr-g6.2.?.?@1779@@"0"6"6"8 4 Hh~~6(3 4 AsA2 lookup_values extra_valuesc t|tsJ|t|tsJt|} || j||jr|j | g|j D cgc]} t | j}} t |j|vr!|j|jd|Dcgc]}dj|}}t|jdj||jDcgc]\}}| c}}} |d|S|j!| ||||||| | |  j"}|j|jd|Scc} wcc}wcc}}w#t$r(|j!| ||||||| | |  j"cYSwxYw)a Create or populate a lookup table with the specified values. ``db["Species"].lookup({"name": "Palm"})`` will create a table called ``Species`` (if one does not already exist) with two columns: ``id`` and ``name``. It will set up a unique constraint on the ``name`` column to guarantee it will not contain duplicate rows. It will then insert a new row with the ``name`` set to ``Palm`` and return the new integer primary key value. An optional second argument can be provided with more ``name: value`` pairs to be included only if the record is being created for the first time. These will be ignored on subsequent lookup calls for records that already exist. All other keyword arguments are passed through to ``.insert()``. See :ref:`python_api_lookup_tables` for more details. :param lookup_values: Dictionary specifying column names and values to use for the lookup :param extra_values: Additional column values to be used only if creating a new record :param strict: Boolean, apply STRICT mode if creating the table. T)r3r r r ) rMr=rNrOrPrSrr6r{)rrrMrrrrr6rrrr;rrrrrrwr)rnrrrMr=rNrOrPrSrr6r{combined_valuesrunique_column_setsr&rrRrrs rGrz Table.lookup sJ-...  #lD1 11}-  #  " "< 0 ;;=  $ $o%6 7:>,,!GQ#aii.!G !G=%%'(0BB!!-"4"4"6t!D>KLFj''/LFLLL(ATATAV*WXQ5*WD  Awr{"))!!!' g    m0024  @IM"HM+X  {{#!-!-%%% +#!# '  s$5F F F9F.G  G record_or_iterabler m2m_tablect|tr+tt|jj ||}|j }| |Jd|Jdtt|j|jg}|D cgc]} dj| } } ||} n|jj|j|j} t| dk(r| d} nPt| dkDr/tdj|j|j|xsdj|} |jj | | | } |t|tr|g}ntt|}|D]x}|j!tt"||d | j }| j!dj|j|dj|j|id z|S|j%|}| j!dj|j|dj|j|id |Scc} w) a9 After inserting a record in a table, create one or more records in some other table and then create many-to-many records linking the original record and the newly created records together. For example:: db["dogs"].insert({"id": 1, "name": "Cleo"}, pk="id").m2m( "humans", {"id": 1, "name": "Natalie"}, pk="id" ) See :ref:`python_api_m2m` for details. :param other_table: The name of the table to insert the new records into. :param record_or_iterable: A single dictionary record to insert, or a list of records. :param pk: The primary key to use if creating ``other_table``. :param lookup: Same dictionary as for ``.lookup()``, to create a many-to-many lookup table. :param m2m_table: The string name to use for the many-to-many table, defaults to creating this automatically based on the names of the two tables. :param alter: Set to ``True`` to add any missing columns on ``other_table`` if that table already exists. rWz#Provide lookup= or record, not bothr]rr zANo single obvious m2m table for {}, {} - use m2m_table= parameterr^)rMr=T)rMrr)r)rrPrrrr%rr;rrrrrr[rrrwrr)rnr/r!rMrr"rour_idrrr6m2m_table_namer m2m_table_objrrrVs rGm2mz Table.m2m sOB k3 'udggmmKBm&GHK  %- T/T T-%1 X3X X1fdii)9)9:;<.457>>!$55  &N55diiAQAQRJ:!#!+AZ1$$W^^ ;#3#3"+!Engnnf.E nw W >,g6-.t%78!  ''v&2t5('$${'7'78"tyy16! % ( ##F+B  NN;#3#34bNN499-v !  [6s IcN|jj|jy)zRun ANALYZE against this tableN)rrrrs rGrz Table.analyze6s  "rF common_limitr+r,r$c  |j}|j}|||j}fd} |jdj ||j d} |jdj ||j d} |jdj ||j d} d} d}| dk(r>|jdj ||j d}| ||fg} n| |k7r|rl| |k(rd|fg} na|jd j |||||j Dcgc]}| |d|df} }| jd d |rh| |krd}n`|jd j |||||j Dcgc]}| |d|df}}|jdt|j||| | | | |Scc}wcc}w)aA Return statistics about the specified column. See :ref:`python_api_analyze_column`. :param column: Column to analyze :param common_limit: Show this many column values :param value_truncate: Truncate display of common values to this many characters :param total_rows: Optimization - pass the total number of rows in the table to save running a fresh ``count(*)`` query :param most_common: If ``True``, calculate the most common values :param least_common: If ``True``, calculate the least common values Nc~t|ttfr|St|}t |kDr|ddz}|S)Nz...)rrSrRrPr)rvalue_truncates rGrz&Table.analyze_column..truncateTsF%EE3<)H JE5zN*o~.6LrFz,select count(*) from [{}] where [{}] is nullr z)select count(*) from [{}] where [{}] = ''z%select count(distinct [{}]) from [{}]rzselect [{}] from [{}] limit 1zSselect [{}], count(*) from [{}] group by [{}] order by count(*) desc, [{}] limit {}c|d|dfSNrr rEr`s rGz&Table.analyze_column..zsAaD!A$<rFT)r<reversezSselect [{}], count(*) from [{}] group by [{}] order by count(*), [{}] desc limit {}c|d|dfSr.rEr/s rGr0z&Table.analyze_column..sQqT1Q4LrFrb) rrrrrrrrvr$)rnr&r)r,r'r+r,rr%rr(r)r*most_common_resultsleast_common_resultsrrs ` rGanalyze_columnzTable.analyze_column:sW*WW   EJ :: : A A% P (*QJJ 7 > >uf M (*Q zz 3 : :65 I (*Q ## 1 JJ/66vuEhjE%-UOZ#@"A  Z 'z),0*+=*>'"$qxx &vv|"#(* +"!A$1.+'+(,,1GQU,V</+/("$qxx &vv|"#(* ,"!A$1.,(,)--2H-I II         /+,s H>H ri geometry_typesridcoord_dimensionc |jjd|j||||t|g}|j }|xrt |dS)a In SpatiaLite, a geometry column can only be added to an existing table. To do so, use ``table.add_geometry_column``, passing in a geometry type. By default, this will add a nullable column using `SRID 4326 `__. This can be customized using the ``column_name``, ``srid`` and ``not_null`` arguments. Returns ``True`` if the column was successfully added, ``False`` if not. .. code-block:: python from sqlite_utils.db import Database from sqlite_utils.utils import find_spatialite db = Database("mydb.db") db.init_spatialite(find_spatialite()) # the table must exist before adding a geometry column table = db["locations"].create({"name": str}) table.add_geometry_column("geometry", "POINT") :param column_name: Name of column to add :param geometry_type: Type of geometry column, for example ``"GEOMETRY"`` or ``"POINT" or ``"POLYGON"`` :param srid: Integer SRID, defaults to 4326 for WGS84 :param coord_dimension: Dimensions to use, defaults to ``"XY"`` - set to ``"XYZ"`` to work in three dimensions :param not_null: Should the column be ``NOT NULL`` z+SELECT AddGeometryColumn(?, ?, ?, ?, ?, ?);r )rrrrRrr)rnrir6r7r8rOrrs rGadd_geometry_columnzTable.add_geometry_columnsZH 9 H    ")$vay/)rFcd|jd||jjvry|jjd|j|g}|j }|xrt |dS)a A spatial index allows for significantly faster bounding box queries. To create one, use ``create_spatial_index`` with the name of an existing geometry column. Returns ``True`` if the index was successfully created, ``False`` if not. Calling this function if an index already exists is a no-op. .. code-block:: python # assuming SpatiaLite is loaded, create the table, add the column table = db["locations"].create({"name": str}) table.add_geometry_column("geometry", "POINT") # now we can index it table.create_spatial_index("geometry") # the spatial index is a virtual table, which we can inspect print(db["idx_locations_geometry"].schema) # outputs: # CREATE VIRTUAL TABLE "idx_locations_geometry" USING rtree(pkid, xmin, xmax, ymin, ymax) :param column_name: Geometry column to create the spatial index against idx_rRFzselect CreateSpatialIndex(?, ?)r )rrrrrr)rnrirrs rGcreate_spatial_indexzTable.create_spatial_indexsn0$))Ak] +tww/B/B/D D - ;/G ")$vay/)rF)NNNNNdNNFFFNNNFr)NNN)NFFFF)NNNNF)NNF)rFNF)rr)NNNNNF)NNNNNNFF)NFN)NNFFNNFTr) NrVNNNNNNNF) NNTT)iXYF)WrBrCrDrYrrrR__annotations__rrrqrPrrrrrrrrorrrrrmrr;rrxr=r*r1rr7r$r=rr rr{r/r2rLrr8rkrhrrrrHrIrhr+r5rrrrrrrrrrrSr#r6rMrrrrrrJrwr7rrrrr'rr5r:r= __classcell__)rs@rGrrs4!%J $!GXc]! !26,0,0-1!%37?C&*,0%% % %  SM % / % tCy) % 8C=)% 4S>*% % #% "(3-0% % % % 5c3hc!:;<% d^!% "$sCx.)#% $%% N # "s""22T#YJ4JJ U4S#89 d 8d<0$2Xc]22e.$v,$  $w-    HtCH~HH S#X  )))!26,0,0-1!%37?C#8c3h8 SM8/ 8 tCy) 8 8C=) 84S>*8#8"(3-085c3hc!:;<888888 !8t!#!'!&!%!%#'#,0-1596:26,0$(@~@ @ x @ SM @8C=)@4S>*@$HSM2@#?3@/@tCy)@SM@ @J!%!%#'#,0-1046:26,0$($(L~L L x L SM L8C=)L4S>*L$H-L#?3L/LtCy)LSMLSML cLb $#'+/ jsHSM)*j}jC= j c3h( j  j^%)#!&M%Y/0MSMM M  M  MMd#' $*. 333-3 SM 3  3 #3- 3j 4  # # @&*&* 00c]0sm 0  0d/(bATAA" %"& T#TT T 3- T  TlHSMg,2 HSM6 ,0"&# $#"L(3-(L3-L} L  L } LL Lb#'+/# $#6:"1* 1*3-1*(3-( 1* } 1*  1*}1*U8T>231*1*1* 4t# $1*fBdE3U&B C& $6: }U8T>23   :#'&* 6uc3566$6 6 d^ 6  6x!%%)#6:#PsDI~&P P P c] P  PP}PU8T>23PPPfGK/3bo"b\B "?@C dGm,-C tW}-.C %g ./C 5c3hcG!CDEC eDcNG$;<=C %S#X 789C tW}-.!C " #C P )E* +ET $  $ R !& " #& P8DcN+C26 26,0,0-1?C04,0!&SCH~StCH~.S SM S / S tCy) S8C=)S4S>*S5c3hc!:;<Sd38n-S$sCx.)SSt ,3+/#'V3<(V% (4S>*DcN: ; V U3<( ) Vc3h(VC=VVp# !X X X  X X  X |# 1*1*1* 1*  1*  1* 1*f*4*rFrc,eZdZdZdefdZddZdZy)rcy)NTrErs rGrz View.existssrFrczdj|jdjd|jDS)Nzr-c34K|]}|jywrlrrs rGrz View.__repr__..s >A >r)rrrr6rs rGrz View.__repr__s1&& IItyy > >>  rFc |jjdj|jy#tj $r|sYywxYw)zx Drop this view. :param ignore: Set to ``True`` to ignore the error if the view does not exist zDROP VIEW [{}]Nr{r|s rGrz View.dropsG  GGOO,33DII> ?''  r}ctd)z9``enable_fts()`` is supported on tables but not on views.z4enable_fts() is supported on tables but not on views)NotImplementedError)rnrors rGrzView.enable_ftss! B  rFNr?)rBrCrDrrPrrrrErFrGrrs #  rFrct|tjr t|St|tt t frtj|tdSt|tjtjtjfr|jSt|tjr t|St|t j"r t|S|S)NF)r  ensure_ascii)rdecimalDecimalrSrr;rjsondumpsrtdatetimetimedate isoformat timedeltarPuuidUUID)rs rGrrs%)U|%$e,-zz%EBB EHMM8==(:K:KL M  E8-- .5z E499 %5z rFrSrcd|i}t|ttfr|Dcic]}||}}|Scc}wrl)rr;r)rSitems rGrqrqs>(T5M*+344D$J44 O5s -c0|D]}d|vrd|vr Jdy)N[]z*'[' and ']' cannot be used in column namesrE)r6r&s rGrurus08 v #V"3 8 7 8 38rFrc #K|D]m}td|jDrG|jDcic](\}}|jddjdd|*c}}j|oycc}}ww)Nc30K|]}d|vxsd|vyw)r[r\NrE)rr<s rGrz$fix_square_braces..)s ACscz'SCZ'Asr[rRr\)rrrrr)rrr<rs rGr r 'sy A6;;=A A#),,.C C%--c37>  Ls9A<-A6(A<c6|jdr|jdr|ddS|jr t|S|jdr+|jdr|dd}t j |S t |S#t$rY|SwxYw)Nrrr*zX'r)rrisdigitrRbinascii unhexlifyrSrH)r to_decodes rGr(r(2s !4Qr{ }}5z %.."5!BK !!),, U|   L s B BB)wutilsrrrrrrr r r ra collectionsr collections.abcrrrQrMrrrOrrrer sqlite_fts4rrmtypingrrrrrrrrrrrVsqlite_utils.pluginsr sqlite_dumpr ImportErrorr compilerVERBOSE IGNORECASErpandaspdnumpynprrYr$r-r1r7r8r=rr@rPForeignKeyIndicatorrrJrLrSrRrrrr;rUr memoryviewrSrRrUrNrWryrMint8int16int32int64uint8uint16uint32uint64float16float32float64r TimestamprWr[r]r`rbrdrfrhrjrr&rqrrrrrqrur r(rErFrGrs   "#  !    #$  >* $"** JJ#(  J ,    8D  7UV H1 2C  Y 8 9 Y  #s(O #sC- #sC  !45t