Suite

Fonction ST_AsGeoJSON lente dans PostGIS


J'essaie actuellement d'extraire les données de quartier de la page Neighborhood de Zillow (http://www.zillow.com/howto/api/neighborhood-boundaries.htm), puis de les charger sur une carte de dépliant, openstreetmap. J'ai créé une table "Neighborhood" et ajouté les données dans PostGIS à l'aide de shp2pgsql. Tout fonctionne très bien, je peux interroger les données et récupérer GeoJSON en utilisant ST_AsGeoJSON, mais la requête est vraiment lente. Je réfléchis à la façon dont je pourrais accélérer ce processus car il semble que ST_AsGeoJSON soit le principal coupable. Cette table ne sera pas mise à jour si souvent, donc je me demande s'il serait logique de simplement stocker le GeoJSON dans une autre colonne dans Neighborhood, puis de le renvoyer au lieu de convertir la colonne geom à chaque actualisation de page.

Voici un exemple de ma table de voisinage.

CREATE TABLE "quartier" (gid serial, "état" varchar(2), "comté" varchar(43), "ville" varchar(64), "nom" varchar(64), "regionid" numérique); ALTER TABLE "quartier" AJOUTER LA CLÉ PRIMAIRE (gid); SELECT AddGeometryColumn(",'neighborhood','geom','0','MULTIPOLYGON',2) ;

L'enquête de @BradHards m'a conduit à étudier comment mon ORM exécutait la requête. Au lieu d'appliquer ST_AsGeoJSON() à la colonne, j'appliquais par inadvertance la fonction à chaque enregistrement de données, ajoutant ainsi une requête supplémentaire pour chaque enregistrement renvoyé. Après avoir exécuté la requête manuellement, tout a fonctionné beaucoup plus rapidement !

AVANT:

SELECT quartier.gid AS quartier_gid, quartier.état AS quartier_état, quartier.comté AS quartier_comté, quartier.ville AS quartier_ville, quartier.nom AS nom_quartier, quartier.régionid AS quartier_régionid, ST_AsBinary(neighborhood.geom) AS quartier_geom FROM quartier O quartier. état = %(état_1)s ET quartier.ville = %(ville_1)s INFO sqlalchemy.engine.base.Engine {'ville_1': 'Detroit', 'état_1': 'MI'}

Cette requête suivante a été activée pour chaque enregistrement que je renvoyais, provoquant le long temps de requête :

SELECT ST_AsGeoJSON(ST_GeomFromWKB(%(ST_GeomFromWKB_1)s, %(ST_GeomFromWKB_2)s), %(param_1)s) AS "ST_AsGeoJSON_1" 'ST_GeomFromWKB_1' : }

APRÈS:

SELECT quartier.nom AS nom_quartier, ST_AsGeoJSON(neighborhood.geom, %(param_1)s) AS "ST_AsGeoJSON_1" FROM quartier O quartier.état = %(état_1)s ET quartier.ville = %(ville_1)s

SIG Google Maps gère les grands # polygones ajouter/supprimer

J'étudie une application qui sera basée sur le Web/mobile. L'application est conçue pour fournir aux utilisateurs finaux des informations géospatiales. La source des données géospatiales est ESRI Shapefiles. J'ai un peu d'expérience avec Google Maps, j'ai donc pensé à les convertir en KML, puis à analyser ce fichier kml dans une base de données. Certaines des fonctionnalités de base seront les suivantes

Le plus gros obstacle que je pense devoir surmonter est que cette carte soit à la fois interactive tout en conservant les performances. L'une des fonctionnalités est la possibilité d'ajouter/supprimer dynamiquement des polygones spécifiques. Dans le passé, j'avais rendu un grand nombre de polygones via des fichiers KML. Cependant, comme je dois ajouter/supprimer 1 ou 2 polygones de ce fichier à la fois, je ne sais pas comment j'y arriverais.

Quelqu'un a-t-il une recommandation sur la façon de gérer un grand nombre de marqueurs et de polygones, avec la possibilité de supprimer/ajouter des polygones un par un ?


Comment optimiser les performances tout en sérialisant de nombreux champs géométriques GeoDjango ?

Je développe une application GeoDjango qui utilise le modèle WorldBorder fourni dans le didacticiel. J'ai également créé mon propre modèle de région qui est lié à WorldBorder. Ainsi, un WorldBorder/Country peut avoir plusieurs régions qui contiennent également des bordures (champ MultiPolygon).

J'ai créé l'API à l'aide de DRF, mais c'est tellement lent qu'il faut 16 secondes pour charger toutes les WorldBorder et les régions au format GeoJSON. La taille JSON renvoyée est cependant de 10 Mo. Est-ce raisonnable ?

Je change même le sérialiseur en serpy, ce qui est beaucoup plus rapide que le sérialiseur DRF GIS mais n'offre qu'une amélioration des performances de 10 %.

Il s'avère qu'après le profilage, la plupart du temps est passé dans les fonctions SIG pour convertir le type de données dans la base de données en liste de coordonnées au lieu de WKT. Si j'utilise WKT, la sérialisation est beaucoup plus rapide (1.7s contre 11.7s, le WKT est uniquement pour WorldBorder MultiPolygon, tout le reste est toujours dans GeoJson)

J'ai également essayé de compresser le MultiPolygon à l'aide de ST_SimplifyVW avec une faible tolérance (0,005) pour préserver les précisions, ce qui réduit la taille JSON à 1,7 Mo. Cela porte la charge totale à 3,5 s. Bien sûr, je peux toujours trouver quelle est la meilleure tolérance pour équilibrer précision et vitesse.

Vous trouverez ci-dessous les données de profilage (l'augmentation soudaine des requêtes dans le MultiPolygon simplifié est due à une mauvaise utilisation de l'API Django QS pour utiliser ST_SimplifyVW)

EDIT : j'ai corrigé la requête de base de données afin que les appels de requête restent les mêmes à 75 requêtes et, comme prévu, cela n'augmente pas les performances de manière significative.

EDIT : j'ai continué à améliorer mes requêtes de base de données. Je l'ai réduit à seulement 8 requêtes maintenant. Comme prévu, cela n'améliore pas beaucoup les performances.

Vous trouverez ci-dessous le profilage des appels de fonction. Je souligne la partie qui a pris le plus de temps. Celui-ci utilise la mise en œuvre du SIG vanille DRF.

Ci-dessous, lorsque j'utilise WKT pour l'un des champs MultiPolygon sans ST_SimplifyVW.

Voici les modèles demandés par @Udi

Voici mon sérialiseur à la demande de @Udi

Voici une tranche de la réponse de l'API (1 sur 236 objets) utilisant ST_SimplifyVW avec une tolérance élevée. Si je ne l'utilise pas, Firefox se bloque car il ne peut pas gérer 10 Mo de JSON je pense. Les données sur les frontières de ce pays particulier sont petites par rapport à d'autres pays. Le JSON renvoyé ici est compressé de 10 Mo à 750 Ko en raison de ST_SimplifyVW. Même avec seulement 750 Ko de JSON, cela a pris 4,5 secondes sur ma machine locale.


Sauvez la carte qui sauve des vies

Le 25 avril 2015, le Népal a été frappé par un séisme de magnitude 7,5, tuant plus de 8 000 personnes et en blessant plus de 21 000. Le tremblement de terre a secoué les infrastructures, laissant de nombreux sans-abri et endommageant quelques-uns des sites du patrimoine mondial les plus populaires au Népal. Les jours suivants, les répliques, la pluie et le mauvais temps ont rendu les opérations de sauvetage et les opérations de secours difficiles.

Je fais partie d'une communauté de cartographes et d'intervenants d'urgence cartographiques appelée Humanitarian OpenStreetMap Team (HOT). HOT et OpenStreetMap, une communauté mondiale de plus de 2,1 millions de membres, s'activent pour aider les agences humanitaires en temps de crise à fournir des informations géospatiales à jour sur les implantations et les infrastructures. HOT invite des cartographes bénévoles à cartographier les bâtiments, les agglomérations, les routes, les écoles, les hôpitaux, les sources d'eau, les héliports, les zones ouvertes et tout ce qui peut avoir une application pratique pour aider à la réponse aux catastrophes.

Le tremblement de terre au Népal a empêché certains d'entre nous de dormir pendant des semaines. Nous avons cartographié et travaillé avec l'unique agence locale de Katmandou, le Kathmandu Living Labs, pour comprendre les besoins des intervenants sur le terrain, identifier les zones de cartographie prioritaires, se procurer des images numériques aériennes et satellite, concevoir des cartes imprimées et communiquer les besoins à la communauté mondiale via Skype et Internet Relay Chat. Pendant le tremblement de terre, plus de 30 projets de cartographie ont été impliqués dans la cartographie de différentes parties du Népal.

Carte imprimée à contraste élevé du village de Dudhpati. Met en évidence les fonctionnalités humanitaires – OpenStreetMap

Carte de tremblement de terre de l'USGS au-dessus des caractéristiques physiques pour identifier l'intensité – Mapbox

Dans les 48 heures qui ont suivi le séisme, plus de 2 000 cartographes ont quadruplé le kilométrage des routes et ajouté 30 % de bâtiments et de données d'infrastructure supplémentaires. Cela s'ajoute aux données créées par la communauté locale bien avant le séisme. Ces cartes et données ont été utilisées par des organismes comme la Croix-Rouge américaine, les Forces armées canadiennes et l'Armée nationale népalaise pour soutenir les efforts visant à sauver des vies. Les cartes sauvent des vies.

Le capitaine James Borer, de l'Équipe d'intervention en cas de catastrophe des Forces armées canadiennes et le capitaine Animesh Adhikari, officier de liaison de l'Armée nationale népalaise, discutent avec les autorités locales de l'état des routes, dans le district de Sindhupalchok, au Népal. Photo du caporal Kevin McMillan.

Ces cartes et ces chiffres sont puissants, plus puissants que le projet de loi sur la réglementation des informations géospatiales du gouvernement indien. Le projet de loi propose de contrôler l'acquisition, la diffusion, la publication et la distribution de données géospatiales à la fois en Inde et par les citoyens indiens à l'extérieur du pays. Le projet de loi fera des années de travail de millions de géographes, cartographes et technologues du monde entier, y compris le mien, futile et potentiellement illégal ou criminel.

Les rues inondées ont aidé quiconque à trouver des rues sûres ou non à Chennai

En janvier 2016, lorsque Chennai a été touchée par une inondation massive qui a déplacé plus de 1,8 million de personnes, des collectifs et des organisations de citoyens se sont réunis pour venir en aide. Avec d'autres cartographes et bénévoles, nous avons créé une carte pour identifier les rues inondées afin de faciliter le transport de nourriture, d'eau et de consommables dans la ville depuis l'extérieur. Plus tôt, en octobre 2015, lorsque des parties de l'Afghanistan et du Pakistan ont été frappées par un tremblement de terre de magnitude 7,5, des volontaires se sont réunis pour créer des cartes, mais des lois de cartographie peu claires ont compliqué les choses.

Peu de temps après, j'ai écrit sur le changement que j'aimerais voir dans la communauté HOT - pouvoir travailler en étroite collaboration avec les agences traditionnelles.

Le projet de loi sur la réglementation des informations géospatiales a pour effet de fermer l'équipe humanitaire OpenStreetMap en Inde et de faire taire les cartographes, les cartographes et les analystes de données bénévoles qui soutiennent la réponse humanitaire, les empêchant essentiellement de sauver des vies. Le projet de loi interdit à toute personne ou organisation d'utiliser l'imagerie satellitaire ou aérienne, il bloque également l'utilisation d'équipements assistés pour collecter des informations géospatiales et la possession de ces données. Cela signifie qu'à partir de maintenant, personne ne pourra plus jamais dresser une carte de l'Inde, ou de certaines parties de ce pays, pour quoi que ce soit « humanitaire ou commercial » sans l'approbation des autorités.

Les conséquences potentielles du projet de loi sont dramatiques. Empêcher les gens de faire des cartes affecte la façon dont ils vivent dans notre pays, cela perturbe la réponse aux catastrophes et perturbe notre économie.


Utilisation des données de l'instrument Landsat 8 TIRS pour estimer la température de surface*

Le capteur infrarouge thermique (TIRS) est un nouvel instrument embarqué à bord du satellite Landsat 8 qui est dédié à la capture d'informations spécifiques à la température. En utilisant les informations de rayonnement des deux bandes spectrales électromagnétiques couvertes par ce capteur, il est possible d'estimer la température à la surface de la Terre (bien qu'à une résolution de 100 m, par rapport à la résolution de 30 m de l'autre instrument, l'Operational Land Imager).

J'ai utilisé les données du TIRS pour estimer la température de surface dans la ville-état de Delhi, en Inde, au 29 mai 2013. Le fichier tarball contenant les données a été téléchargé à l'aide de EarthExplorer de l'United States Geological Survey (USGS) outil, la zone d'intérêt était englobée par [identificateur de scène : chemin 146, ligne 040] dans le schéma WRS-2. Je pense que je vais laisser les explications spécifiques décrivant WRS-2, les valeurs de chemin/ligne et les autres petites opérations de gestion de données pour un article ultérieur. Pour l'instant, je laisse entendre que ce sont des choses importantes à savoir lors du processus d'obtention de ces données. Lorsque l'archive tar est complètement décompressée, les bandes de l'instrument TIRS sont les bandes 10 et 11, les fichiers .tif pertinents sont [« identifiant »_B10.tif] et [« identifiant »_B11.tif], et ceux-ci ont été coupés à la limite administrative de Delhi. Il y a aussi un fichier texte contenant des métadonnées : ["identifier"_MTL.txt] est essentiel pour le calcul que nous allons faire sur ces deux bandes.

Delhi vue par Landsat 8 Band 10 (TIRS)


Le Reader’s Digest Great World Atlas of 1961.

J'ai acquis la première édition du Reader’s Digest Great World Atlas of 1961, hier dans une très ancienne librairie de Bangalore.

C'est un ajout incroyable à ma collection de cartes. Fait intéressant, je n'ai trouvé aucune information sur l'atlas sur Internet.

L'atlas a été "planifié" sous la direction du célèbre géographe Frank Debenham. Avec la participation de la British and Foreign Bible Society, de la British Broadcasting Corporation, de la FOA, de l'OMS, du Service d'information de l'Inde et de nombreuses autres organisations et individus, l'atlas est réparti en quatre sections.

Le paradis est quelque part en Extrême-Orient. Jérusalem est le centre de toutes les nations et de tous les pays, et le monde lui-même est un disque plat entouré d'océans d'eau. Ainsi, les moines cartographes du Moyen Âge virent le monde dans lequel ils vivaient.

Jérusalem était considérée comme le centre du monde, tandis que le centre géographique a été calculé pour la première fois en 1864, révisé en 1973 et finalisé en 2003 par Andrew J. Woods. L'atlas tente de corriger ces fausses notions en recueillant la somme des connaissances des explorations et des découvertes scientifiques de l'époque.

La première section intitulée Face of the World présente des cartes en relief fascinantes comme celles ci-dessous. Ils sont structurellement et géographiquement dans les moindres détails que j'ai vus dans l'une des anciennes représentations.

L'atlas utilise diverses projections telles que la projection Conic, Azimuthal Equal Area de Lambert, Bonne et la projection de Van der Grinten. La carte suivante des océans est mieux représentée dans la projection de Van der Grinten.

Et l'illustration intéressante suivante sur la dérive des continents.

Il y a plus à l'atlas que ceux-ci. J'espère les publier quand j'aurai le temps de lire cet incroyable récit de l'histoire.


Conception d'un nouveau portail cartographique pour Karnataka Learning Partnership.

A écrit un article assez détaillé sur les nouvelles cartes pour Karnataka Learning Partnership sur le blog geohackers.in.

La carte est une partie importante de notre projet, de notre action et de notre processus car elle sert de pivot de navigation. Je parlerai rapidement des données et des outils avant d'aborder les aspects de conception.

Nous avons un ensemble de données assez important sur les écoles du Karnataka. Le nom de l'école, l'emplacement, le nombre de filles et de garçons, etc. dans une base de données. Heureusement, les données étaient propres et correctement stockées dans une base de données PostgreSQL avec des extensions PostGIS. La plupart de ma tâche consistait à modifier l'API pour lancer GeoJSON au client à l'aide de la fonction ST_AsGeoJSON et exporter les données.


Avec Arky, je créais une carte des équipes Mozilla l10n dans le monde et j'ai décidé d'utiliser Leaflet.js. Il a envoyé une feuille de données CSV qui contenait les équipes géolocalisées et d'autres informations. A écrit un script Python pour générer des marqueurs correspondant à Leaflet.js (Oui, mes compétences JS sont nulles). Lorsque nous avons posé les marqueurs sur la carte, nous avons constaté qu'il semblait tout encombré et difficile de localiser une équipe rapidement. J'ai emmené cette discussion sur le canal HasGeek et Nigel m'a montré la carte des événements mondiaux d'Ubuntu Loco. Les marqueurs de la carte ont changé en fonction du niveau de zoom de la carte. C'est une idée assez intéressante pour gérer les marqueurs de carte encombrés. Mais ils utilisaient Google Maps.

Je voulais que cela se produise avec les données Leaflet.js et OpenStreetMap. Leaflet.js est peut-être la meilleure bibliothèque de cartographie interactive, simple et intuitive que j'ai rencontrée. J'ai passé du temps dans la documentation et j'ai eu cette idée et le tour est joué ! – cela a fonctionné.

Leaflet.js fournit plusieurs événements, celui que nous utiliserons est zoomer. Cet événement est déclenché chaque fois que le niveau de zoom de la carte change. Exactement ce dont nous avons besoin. C'est ainsi que j'ai procédé.

Si vous avez un tas de marqueurs, utilisez LayerGroup, cela vous donnera un meilleur contrôle. Vous pouvez maintenant ajouter/supprimer l'ensemble du marqueur défini par le gestionnaire LayerGroup.


Inspec - Encore un autre framework de test Javascript BDD

Mise à jour : En raison d'un manque d'intérêt et de temps, Inspec n'est plus maintenu. jSpec est devenu plus complet depuis ce post, et je recommanderais plutôt d'utiliser jSpec.

La question &ldquoPourquoi&rdquo

Pourquoi? Vous demandez, nous avons déjà des frameworks BDD tels queScrew.Unit, jSpec et JSSepc, vous voulez faire un autre Cadre de test Javascript BDD ?

Pour répondre à votre question, vous pouvez jeter un œil à mon article précédent. Alors, s'il vous plaît, lisez-le si vous ne l'avez pas fait.

Maintenant, si vous êtes toujours avec moi après avoir lu mon article, permettez-moi de vous présenter un nouveau cadre de test BDD qui ne craint pas.


Dépendances

web2py est livré avec une couche d'abstraction de base de données (DAL), une API qui mappe des objets Python dans des objets de base de données tels que des requêtes, des tables et des enregistrements. Le DAL génère dynamiquement le SQL en temps réel en utilisant le dialecte spécifié pour le back-end de la base de données, de sorte que vous n'ayez pas à écrire de code SQL ou à apprendre différents dialectes SQL (le terme SQL est utilisé de manière générique), et l'application sera portable entre différents types de bases de données. Une liste partielle des bases de données prises en charge est présentée dans le tableau ci-dessous. Veuillez vérifier sur le site web et la liste de diffusion web2py les adaptateurs les plus récents. Google NoSQL est traité comme un cas particulier au chapitre 13.

La section Gotchas à la fin de ce chapitre contient des informations supplémentaires sur des bases de données spécifiques.

La distribution binaire Windows fonctionne immédiatement avec SQLite, MSSQL, Postgresql et MySQL. La distribution binaire Mac fonctionne immédiatement avec SQLite. Pour utiliser un autre back-end de base de données, exécutez-le à partir de la distribution source et installez le pilote approprié pour le back-end requis.

Une fois le bon pilote installé, démarrez web2py à partir de la source, et il trouvera le pilote. Voici une liste des pilotes que web2py peut utiliser :

base de donnéespilotes (source)
SQLitesqlite3 ou pysqlite2 ou zxJDBC [zxjdbc] (sur Jython)
PostgreSQLpsycopg2 [psycopg2] ou pg8000 [pg8000] ou zxJDBC [zxjdbc] (sur Jython)
MySQLpymysql [pymysql] ou MySQLdb [mysqldb]
Oraclecx_Oracle [cxoracle]
MSSQLpyodbc [pyodbc] ou pypyodbc [pypyodbc]
Oiseau de feukinterbasdb [kinterbasdb] ou fdb ou pyodbc
DB2pyodbc [pyodbc]
Informixinformixdb [informixdb]
Ingresingresdbi [ingresdbi]
Cubridecubriddb [cubridb] [cubridb]
SybaseSybase [Sybase]
Teradatapyodbc [Teradata]
SAPDBsapdb [SAPDB]
MongoDBpymongo [pymongo]
IMAPimalib [IMAP]

sqlite3 , pymysql , pg8000 et imaplib sont livrés avec web2py. La prise en charge de MongoDB est expérimentale. L'option IMAP permet d'utiliser DAL pour accéder à IMAP.

Le DAL : un petit tour

web2py définit les classes suivantes qui composent le DAL :

Le DAL L'objet représente une connexion à une base de données. Par exemple:

Tableau représente une table de base de données. Vous n'instancierez pas directement Table à la place, DAL.define_table l'instancie.

Les méthodes les plus importantes d'un tableau sont :

.insert , .truncate , .drop et .import_from_csv_file .

Domaine représente un champ de base de données. Il peut être instancié et passé en argument à DAL.define_table .

Ligne contient des valeurs de champ.

Mettre en doute est un objet qui représente une clause SQL "where":

Régler est un objet qui représente un ensemble d'enregistrements. Ses méthodes les plus importantes sont count , select , update et delete . Par exemple:

Expression est quelque chose comme une expression orderby ou groupby. La classe Field est dérivée de l'expression. Voici un exemple.

Utilisation du DAL "autonome"

Le DAL web2py peut être utilisé dans un environnement non web2py via

Constructeur DAL

La base de données est maintenant connectée et la connexion est stockée dans la variable globale db .

A tout moment vous pouvez récupérer la chaîne de connexion.

La chaîne de connexion est appelée _uri car il s'agit d'une instance d'un Uniform Resource Identifier.

Le DAL permet plusieurs connexions avec la même base de données ou avec des bases de données différentes, voire des bases de données de types différents. Pour l'instant, nous supposerons la présence d'une seule base de données car c'est la situation la plus courante.

Signature DAL

Chaînes de connexion (le paramètre uri)

Une connexion avec la base de données est établie en créant une instance de l'objet DAL :

db n'est pas un mot-clé, c'est une variable locale qui stocke l'objet de connexion DAL. Vous êtes libre de lui donner un nom différent. Le constructeur de DAL requiert un seul argument, la chaîne de connexion. La chaîne de connexion est le seul code web2py qui dépend d'une base de données back-end spécifique. Voici des exemples de chaînes de connexion pour des types spécifiques de bases de données principales prises en charge (dans tous les cas, nous supposons que la base de données s'exécute à partir de localhost sur son port par défaut et est nommée "test") :

sdb

SQLite sqlite://storage.db
MySQL mysql://username:[email protected]/test
PostgreSQL postgres://username:[email protected]/test
MSSQL (héritage) mssql://username:[email protected]/test
MSSQL (>=2005) mssql3://username:[email protected]/test
MSSQL (>=2012) mssql4://username:[email protected]/test
Oiseau de feu firebird://username:[email protected]/test
Oracle oracle://nom d'utilisateur/mot de [email protected]
DB2 db2://nom d'utilisateur:mot de [email protected]
Ingres ingres://username:[email protected]/test
Sybase sybase://nom d'utilisateur:mot de [email protected]/test
Informix informix://username:[email protected]
Teradata teradata://DSN=dsnUID=userPWD=passDATABASE=test
Cubride cubrid://username:[email protected]/test
SAPDB sapdb://username:[email protected]/test
IMAP imap://utilisateur:mot de [email protected]:port
MongoDB mongodb://username:[email protected]/test
Google/SQL google:sql://project:instance/database
Google/NoSQL google: banque de données
Google/NoSQL/NDB google:datastore+ndb

Notez que dans SQLite, la base de données se compose d'un seul fichier. S'il n'existe pas, il est créé. Ce fichier est verrouillé à chaque accès. Dans le cas de MySQL, PostgreSQL, MSSQL, FireBird, Oracle, DB2, Ingres et Informix la base de données "test" doit être créée en dehors de web2py. Une fois la connexion établie, web2py créera, modifiera et supprimera les tables de manière appropriée.

Dans le cas de Google/NoSQL, l'option +ndb active NDB. NDB utilise un tampon Memcache pour lire les données auxquelles on accède souvent. C'est complètement automatique et fait au niveau du magasin de données, pas au niveau de web2py.

Il est également possible de définir la chaîne de connexion sur None . Dans ce cas, DAL ne se connectera à aucune base de données principale, mais l'API est toujours accessible pour les tests. Des exemples de ceci seront discutés au chapitre 7.

Parfois, vous devrez peut-être générer du SQL comme si vous aviez une connexion mais sans vous connecter réellement à la base de données. Cela peut être fait avec

Dans ce cas, vous pourrez appeler _select , _insert , _update et _delete pour générer du SQL mais pas appeler select , insert , update et delete . Dans la plupart des cas, vous pouvez utiliser do_connect=False même sans avoir les pilotes de base de données requis.

Notez que par défaut, web2py utilise l'encodage de caractères utf8 pour les bases de données. Si vous travaillez avec des bases de données existantes qui se comportent différemment, vous devez le modifier avec le paramètre facultatif db_codec comme

Sinon, vous obtiendrez des tickets UnicodeDecodeError.

Mise en commun des connexions

Un argument commun du constructeur DAL est le pool_size qu'il met par défaut à zéro.

Comme il est assez lent d'établir une nouvelle connexion à la base de données pour chaque requête, web2py implémente un mécanisme de pooling de connexion. Une fois qu'une connexion est établie et que la page a été servie et la transaction terminée, la connexion n'est pas fermée mais va dans un pool. Lorsque la prochaine requête http arrive, web2py essaie de recycler une connexion du pool et de l'utiliser pour la nouvelle transaction. S'il n'y a aucune connexion disponible dans le pool, une nouvelle connexion est établie.

Lorsque web2py démarre, le pool est toujours vide. Le pool s'agrandit jusqu'au minimum entre la valeur de pool_size et le nombre maximum de requêtes simultanées. Cela signifie que si pool_size=10 mais que notre serveur ne reçoit jamais plus de 5 requêtes simultanées, la taille réelle du pool ne passera qu'à 5. Si pool_size=0, le pool de connexions n'est pas utilisé.

Les connexions dans les pools sont partagées séquentiellement entre les threads, dans le sens où elles peuvent être utilisées par deux threads différents mais pas simultanés. Il n'y a qu'un seul pool pour chaque processus web2py.

Le paramètre pool_size est ignoré par SQLite et Google App Engine. Le regroupement de connexions est ignoré pour SQLite, car il n'apporterait aucun avantage.

Échecs de connexion (paramètre de tentatives)

Si web2py ne parvient pas à se connecter à la base de données, il attend 1 seconde et par défaut réessaye jusqu'à 5 fois avant de déclarer un échec. En cas de regroupement de connexions, il est possible qu'une connexion regroupée qui reste ouverte mais inutilisée pendant un certain temps soit fermée à la fin de la base de données. Grâce à la fonctionnalité de nouvelle tentative, web2py essaie de rétablir ces connexions interrompues. Le nombre de tentatives est défini via le paramètre tentatives.

Tables paresseuses

le réglage lazy_tables = True fournit une amélioration majeure des performances. Voir ci-dessous : tables paresseuses

Bases de données répliquées

Le premier argument de DAL(. ) peut être une liste d'URI. Dans ce cas, web2py essaie de se connecter à chacun d'eux. L'objectif principal est de gérer plusieurs serveurs de bases de données et de répartir la charge de travail entre eux). Voici un cas d'utilisation typique :

Dans ce cas, le DAL essaie de se connecter au premier et, en cas d'échec, il essaiera le deuxième et le troisième. Cela peut également être utilisé pour répartir la charge dans une configuration maître-esclave de base de données. Nous en parlerons plus en détail au chapitre 13 dans le contexte de l'évolutivité.

Mots-clés réservés

check_reserved indique au constructeur de vérifier les noms de table et de colonne par rapport aux mots-clés SQL réservés dans les bases de données principales cibles. check_reserved par défaut à None.

Il s'agit d'une liste de chaînes contenant les noms des adaptateurs principaux de la base de données.

Le nom de l'adaptateur est le même que celui utilisé dans la chaîne de connexion DAL. Donc, si vous souhaitez vérifier par rapport à PostgreSQL et MSSQL, votre chaîne de connexion ressemblera à ceci :

Le DAL analysera les mots-clés dans le même ordre que dans la liste.

Il y a deux options supplémentaires "tous" et "commun". Si vous spécifiez tout, il vérifiera par rapport à tous les mots-clés SQL connus. Si vous spécifiez common, il ne vérifiera que les mots-clés SQL courants tels que SELECT , INSERT , UPDATE , etc.

Pour les back-ends pris en charge, vous pouvez également spécifier si vous souhaitez également vérifier les mots-clés SQL non réservés. Dans ce cas, vous ajouteriez _nonreserved au nom. Par exemple:

Les backends de base de données suivants prennent en charge la vérification des mots réservés.

PostgreSQL postgres(_non réservé)
MySQL mysql
Oiseau de feu oiseau de feu(_non réservé)
MSSQL mssql
Oracle oracle

Cotation de la base de données et paramètres de cas (entity_quoting, ignore_field)

Vous pouvez également utiliser des guillemets explicites d'entités SQL au niveau DAL. Cela fonctionne de manière transparente, vous pouvez donc utiliser les mêmes noms en python et dans le schéma de base de données.

ignore_field_case = Vrai entity_quoting = Vrai

Autres paramètres du constructeur DAL

Emplacement du dossier de la base de données

dossier - où les fichiers .table seront créés. Défini automatiquement dans web2py. Utiliser un chemin explicite lors de l'utilisation de DAL en dehors de web2py

Paramètres de migration par défaut

La migration est détaillée ci-dessous dans les tableaux des migrations. Les paramètres de migration du constructeur DAL sont des booléens affectant les valeurs par défaut et le comportement global.

migrate = True définit le comportement de migration par défaut pour toutes les tables

fake_migrate = False définit le comportement par défaut de fake_migrate pour toutes les tables

migrate_enabled = True Si défini sur False désactive TOUTES les migrations

fake_migrate_all = False Si défini sur True, fake migre TOUTES les tables

Expérimentez avec le shell web2py

Vous pouvez expérimenter avec l'API DAL en utilisant le shell web2py (option de ligne de commande -S).

Commencez par créer une connexion. À titre d'exemple, vous pouvez utiliser SQLite. Rien dans cette discussion ne change lorsque vous modifiez le moteur principal.

Constructeur de table

Define_table signature

La signature pour define_table :

Les tables sont définies dans le DAL via define_table :

Il définit, stocke et retourne un objet Table appelé "personne" contenant un champ (colonne) "nom". Cet objet est également accessible via db.person , vous n'avez donc pas besoin de récupérer la valeur de retour.

Id : Notes sur la clé primaire

Ne déclarez pas un champ appelé "id", car il est de toute façon créé par web2py. Chaque table a un champ appelé "id" par défaut. Il s'agit d'un champ entier à incrémentation automatique (commençant à 1) utilisé pour les références croisées et pour rendre chaque enregistrement unique, donc "id" est une clé primaire. (Remarque : le compteur d'identifiants commençant à 1 est spécifique au back-end. Par exemple, cela ne s'applique pas à Google App Engine NoSQL.)

En option, vous pouvez définir un champ de type='id' et web2py utilisera ce champ comme champ d'identification à incrémentation automatique. Ceci n'est pas recommandé, sauf lors de l'accès aux tables de base de données héritées qui ont une clé primaire sous un nom différent. Avec certaines limitations, vous pouvez également utiliser différentes clés primaires à l'aide du paramètre primarykey. primarykey est expliqué ci-dessous.

Pluriel et singulier

Les objets Smartgrid peuvent avoir besoin de connaître le nom singulier et pluriel de la table. Les valeurs par défaut sont intelligentes mais ces paramètres vous permettent d'être précis. Voir smartgrid pour plus d'informations.

Redéfinir

Les tables ne peuvent être définies qu'une seule fois mais vous pouvez forcer web2py à redéfinir une table existante :

La redéfinition peut déclencher une migration si le contenu du champ est différent.

Format : Représentation d'enregistrement

Il est facultatif mais recommandé de spécifier une représentation de format pour les enregistrements avec le paramètre format.

ou encore plus complexes à l'aide d'une fonction :

L'attribut format sera utilisé à deux fins :

  • Pour représenter les enregistrements référencés dans les listes déroulantes de sélection/option.
  • Pour définir l'attribut db.othertable.person.represent pour tous les champs référençant cette table. Cela signifie que SQLTABLE n'affichera pas les références par identifiant mais utilisera à la place la représentation préférée du format.

Rname : Représentation d'enregistrement

rname définit un nom de base de données pour la table. Cela fait du nom de la table web2py un alias et rname est le vrai nom utilisé lors de la construction de la requête pour le backend. Pour illustrer une seule utilisation, rname peut être utilisé pour fournir des noms de table MSSQL complets accédant aux tables appartenant à d'autres bases de données sur le serveur : rname = 'db1.dbo.table1'

Primarykey : prise en charge des anciennes tables

primarykey aide à prendre en charge les tables héritées avec des clés primaires existantes, même en plusieurs parties. Voir les bases de données héritées ci-dessous.

Migrer , fake_migrer

migrate définit les options de migration pour la table. Voir le tableau Migrations ci-dessous

Classe_table

Si vous définissez votre propre classe Table en tant que sous-classe de gluon.dal.Table, vous pouvez la fournir ici, cela vous permet d'étendre et de remplacer les méthodes. Exemple : classe_table=MaTable

Nom_séquence

(Facultatif) Le nom d'une séquence de table personnalisée (si pris en charge par la base de données). Peut créer une SEQUENCE (commençant à 1 et incrémentant de 1) ou l'utiliser pour les tables héritées avec des séquences personnalisées. Notez que lorsque cela est nécessaire, web2py créera des séquences automatiquement par défaut (à partir de 1).

Nom_déclencheur

(facultatif) Se rapporte à nom_séquence . Pertinent pour certains backends qui ne prennent pas en charge les champs numériques à incrémentation automatique.

Polymodèle

On_define

on_define est un rappel déclenché lorsqu'une lazy_table est instanciée, bien qu'elle soit appelée de toute façon si la table n'est pas paresseuse. Cela permet des modifications dynamiques de la table sans perdre les avantages de l'instanciation retardée.

Notez que cet exemple montre comment utiliser on_define mais ce n'est pas réellement nécessaire. Les valeurs simples d'exigences pourraient être ajoutées aux définitions de champ et la table serait toujours paresseuse. Cependant, exige qui prend un objet Set comme premier argument, tel que IS_IN_DB, fera une requête comme db.sometable.somefield == some_value qui entraînerait la définition précoce de sometable. C'est la situation sauvegardée par on_define .

Lazy Tables, un gain de performances majeur

Les modèles web2py sont exécutés avant les contrôleurs, donc toutes les tables sont définies à chaque requête. Toutes les tables ne sont pas nécessaires pour gérer chaque demande, il est donc possible qu'une partie du temps passé à définir les tables soit perdue. Les modèles conditionnels (modèles conditionnels, chapitre 4) peuvent aider, mais web2py offre un gros gain de performances via lazy_tables. Cette fonctionnalité signifie que la création de la table est différée jusqu'à ce que la table soit réellement référencée. L'activation des tables paresseuses est effectuée lors de l'initialisation d'une base de données via le constructeur DAL. Cela nécessite de définir le paramètre DAL(.lazy_tables=True). C'est l'une des améliorations les plus significatives des performances de temps de réponse dans web2py.

Ajout d'attributs aux champs et aux tables

Si vous devez ajouter des attributs personnalisés aux champs, vous pouvez simplement le faire :

"extra" n'est pas un mot-clé, c'est un attribut personnalisé maintenant attaché à l'objet de champ. Vous pouvez également le faire avec des tables, mais elles doivent être précédées d'un trait de soulignement pour éviter les conflits de nom avec les champs :

Constructeur de terrain

Voici les valeurs par défaut d'un constructeur Field :

Tous ne sont pas pertinents pour tous les domaines. "length" n'est pertinent que pour les champs de type "string". "uploadfield" et "authorize" ne sont pertinents que pour les champs de type "upload". "ondelete" n'est pertinent que pour les champs de type "référence" et "upload".

  • length définit la longueur maximale d'un champ "string", "password" ou "upload". Si la longueur n'est pas spécifiée, une valeur par défaut est utilisée, mais il n'est pas garanti que la valeur par défaut soit rétrocompatible. Pour éviter les migrations indésirables lors des mises à niveau, nous vous recommandons de toujours spécifier la longueur des champs de chaîne, de mot de passe et de téléchargement.
  • default définit la valeur par défaut du champ. La valeur par défaut est utilisée lors de l'exécution d'une insertion si une valeur n'est pas explicitement spécifiée. Il est également utilisé pour pré-remplir les formulaires créés à partir de la table à l'aide de SQLFORM. Notez qu'au lieu d'être une valeur fixe, la valeur par défaut peut être une fonction (y compris une fonction lambda) qui renvoie une valeur du type approprié pour le champ. Dans ce cas, la fonction est appelée une fois pour chaque enregistrement inséré, même lorsque plusieurs enregistrements sont insérés dans une seule transaction.
  • requis indique au DAL qu'aucune insertion ne doit être autorisée sur cette table si une valeur pour ce champ n'est pas explicitement spécifiée.
  • requiert est un validateur ou une liste de validateurs. Ce n'est pas utilisé par le DAL, mais il est utilisé par SQLFORM. Les validateurs par défaut pour les types donnés sont indiqués dans la section suivante.
  • uploadfolder alors que la valeur par défaut est None , la plupart des adaptateurs de base de données téléchargeront par défaut des fichiers dans os.path.join(request.folder, 'uploads'). MongoAdapter ne semble pas le faire pour le moment.
  • rname fournit que le champ était un "vrai nom", un nom pour le champ connu de l'adaptateur de base de données lorsque le champ est utilisé, c'est la valeur rname qui est envoyée à la base de données. Le nom web2py du champ est alors effectivement un alias.
  • ondelete se traduit par l'instruction SQL "ON DELETE". Par défaut, il est défini sur "CASCADE". Cela indique à la base de données que lorsqu'elle supprime un enregistrement, elle doit également supprimer tous les enregistrements qui y font référence. Pour désactiver cette fonction, réglez la suppression sur "NO ACTION" ou "SET NULL".
  • notnull=True se traduit par l'instruction SQL "NOT NULL". Il empêche la base de données d'insérer des valeurs nulles pour le champ.
  • unique=True se traduit par l'instruction SQL "UNIQUE" et garantit que les valeurs de ce champ sont uniques dans la table. Il est appliqué au niveau de la base de données.
  • uploadfield s'applique uniquement aux champs de type "upload". Un champ de type "upload" stocke le nom d'un fichier enregistré ailleurs, par défaut sur le système de fichiers sous le dossier "uploads/" de l'application. Si uploadfield est défini sur True, le fichier est stocké dans un champ blob au sein de la même table et la valeur de uploadfield est le nom du champ blob. Ceci sera discuté plus en détail plus tard dans le contexte de SQLFORM.
  • uploadfolder est par défaut le dossier "uploads/" de l'application. S'il est défini sur un chemin différent, les fichiers seront téléchargés dans un dossier différent.
  • uploadseparate si défini sur True téléchargera les fichiers sous différents sous-dossiers du dossier de téléchargement dossier. Ceci est optimisé pour éviter trop de fichiers dans le même dossier/sous-dossier. ATTENTION : Vous ne pouvez pas modifier la valeur de uploadseparate de True à False sans rompre les liens vers les téléchargements existants. web2py utilise les sous-dossiers séparés ou non. Changer le comportement après le téléchargement des fichiers empêchera web2py de pouvoir récupérer ces fichiers. Si cela se produit, il est possible de déplacer des fichiers et de résoudre le problème, mais cela n'est pas décrit ici.
  • uploadfs vous permet de spécifier un système de fichiers différent où télécharger des fichiers, y compris un stockage Amazon S3 ou un stockage SFTP distant. Cette option nécessite l'installation de PyFileSystem. uploadfs doit pointer vers PyFileSystem .

Types de champs

type de champvalidateurs de champ par défaut
chaîne de caractères IS_LENGTH (longueur) la longueur par défaut est 512
texte IS_LENGTH(65536)
goutte Rien
booléen Rien
entier IS_INT_IN_RANGE(-1e100, 1e100)
double IS_FLOAT_IN_RANGE(-1e100, 1e100)
décimal(n,m) IS_DECIMAL_IN_RANGE(-1e100, 1e100)
Date IS_DATE()
temps IS_TIME()
dateheure IS_DATETIME()
le mot de passe Rien
télécharger Rien
référence <table> IS_IN_DB(db,table.champ,format)
liste:chaîne Rien
liste:entier Rien
liste:référence <table> IS_IN_DB(db,table.field,format,multiple=True)
json IS_JSON()
bigint Rien
grand-id Rien
grande référence Rien

Decimal requiert et renvoie des valeurs sous forme d'objets Decimal, comme défini dans le module décimal Python. SQLite ne gère pas le type décimal, nous le traitons donc en interne comme un double . Les (n,m) sont respectivement le nombre de chiffres au total et le nombre de chiffres après la virgule.

Le big-id et big-reference ne sont pris en charge que par certains moteurs de base de données et sont expérimentaux.Ils ne sont normalement pas utilisés comme types de champ, sauf pour les tables héritées, cependant, le constructeur DAL a un argument bigint_id qui, lorsqu'il est défini sur True, rend les champs id et les champs de référence respectivement big-id et big-reference.

Les champs list:<type> sont spéciaux car ils sont conçus pour tirer parti de certaines fonctionnalités de dénormalisation sur NoSQL (dans le cas de Google App Engine NoSQL, les types de champs ListProperty et StringListProperty ) et les rétroporter sur toutes les autres bases de données relationnelles prises en charge. Sur les bases de données relationnelles, les listes sont stockées sous forme de champ de texte. Les éléments sont séparés par un | et chaque | dans l'élément de chaîne est échappé en tant que || . Ils sont discutés dans leur propre section.

Le type de champ json est à peu près explicatif. Il peut stocker n'importe quel objet sérialisable json. Il est conçu pour fonctionner spécifiquement pour MongoDB et rétroporté sur les autres adaptateurs de base de données pour la portabilité.

les champs blob sont également spéciaux. Par défaut, les données binaires sont codées en base64 avant d'être stockées dans le champ réel de la base de données, et elles sont décodées lors de l'extraction. Cela a pour effet négatif d'utiliser 25 % d'espace de stockage en plus que nécessaire dans les champs blob, mais présente deux avantages. En moyenne, cela réduit la quantité de données communiquées entre web2py et le serveur de base de données, et rend la communication indépendante des conventions d'échappement spécifiques au back-end.

Champ d'exécution et modification de la table

La plupart des attributs des champs et des tables peuvent être modifiés après leur définition :

(notez que les attributs des tables sont généralement précédés d'un trait de soulignement pour éviter tout conflit avec d'éventuels noms de champs).

Vous pouvez lister les tables qui ont été définies pour une connexion à une base de données donnée :

Vous pouvez également lister les champs qui ont été définis pour une table donnée :

Vous pouvez rechercher le type d'une table :

et vous pouvez accéder à une table à partir de la connexion DAL en utilisant :

De même, vous pouvez accéder aux champs à partir de leur nom de plusieurs manières équivalentes :

Étant donné un champ, vous pouvez accéder aux attributs définis dans sa définition :

y compris sa table parent, son nom de table et sa connexion parent :

Un champ a aussi des méthodes. Certains d'entre eux sont utilisés pour construire des requêtes et nous les verrons plus tard. Une méthode spéciale de l'objet champ est valider et elle appelle les validateurs pour le champ.

qui renvoie un tuple (valeur, erreur) . error est None si l'entrée passe la validation.

Migrations

define_table vérifie si la table correspondante existe ou non. Si ce n'est pas le cas, il génère le SQL pour le créer et exécute le SQL. Si la table existe mais diffère de celle définie, il génère le code SQL pour modifier la table et l'exécute. Si un champ a changé de type mais pas de nom, il essaiera de convertir les données (Si vous ne le souhaitez pas, vous devez redéfinir la table deux fois, la première fois, en laissant web2py supprimer le champ en le supprimant, et la deuxième fois en ajoutant le champ nouvellement défini pour que web2py puisse le créer.). Si la table existe et correspond à la définition actuelle, elle la laissera seule. Dans tous les cas, il créera l'objet db.person qui représente la table.

Nous appelons ce comportement une « migration ». web2py enregistre toutes les migrations et tentatives de migration dans le fichier "databases/sql.log".

Le premier argument de define_table est toujours le nom de la table. Les autres arguments sans nom sont les champs (Champ). La fonction prend également un argument de mot-clé facultatif appelé « migrer » :

La valeur de migrate est le nom du fichier (dans le dossier "databases" de l'application) où web2py stocke les informations de migration interne pour cette table. Ces fichiers sont très importants et ne doivent jamais être supprimés tant que les tables correspondantes existent. Dans les cas où une table a été supprimée et que le fichier correspondant existe toujours, il peut être supprimé manuellement. Par défaut, migrate est défini sur True. Cela amène web2py à générer le nom de fichier à partir d'un hachage de la chaîne de connexion. Si migrate est défini sur False, la migration n'est pas effectuée et web2py suppose que la table existe dans le magasin de données et qu'elle contient (au moins) les champs répertoriés dans define_table . La meilleure pratique consiste à donner un nom explicite à la table de migration.

Il ne peut pas y avoir deux tables dans la même application avec le même nom de fichier de migration.

La classe DAL prend également un argument "migrate", qui détermine la valeur par défaut de migrate pour les appels à define_table . Par exemple,

définira la valeur par défaut de migrate sur False chaque fois que db.define_table est appelé sans argument migrate.

Les migrations peuvent être désactivées pour toutes les tables à la fois :

C'est le comportement recommandé lorsque deux applications partagent la même base de données. Une seule des deux applications doit effectuer les migrations, l'autre doit les désactiver.

Correction des migrations interrompues

Il y a deux problèmes courants avec les migrations et il existe des moyens de s'en remettre.

Un problème est spécifique à SQLite. SQLite n'applique pas les types de colonnes et ne peut pas supprimer de colonnes. Cela signifie que si vous avez une colonne de type chaîne et que vous la supprimez, elle n'est pas vraiment supprimée. Si vous ajoutez à nouveau la colonne avec un type différent (par exemple datetime), vous vous retrouvez avec une colonne datetime qui contient des chaînes (indésirables à des fins pratiques). web2py ne se plaint pas de cela car il ne sait pas ce qu'il y a dans la base de données, jusqu'à ce qu'il essaie de récupérer les enregistrements et échoue.

Si web2py renvoie une erreur dans la fonction gluon.sql.parse lors de la sélection des enregistrements, c'est le problème : données corrompues dans une colonne à cause du problème ci-dessus.

La solution consiste à mettre à jour tous les enregistrements de la table et à mettre à jour les valeurs de la colonne en question avec Aucun.

L'autre problème est plus générique mais typique avec MySQL. MySQL n'autorise pas plus d'un ALTER TABLE dans une transaction. Cela signifie que web2py doit diviser les transactions complexes en plus petites (une ALTER TABLE à la fois) et valider une pièce à la fois. Il est donc possible qu'une partie d'une transaction complexe soit validée et qu'une partie échoue, laissant web2py dans un état corrompu. Pourquoi une partie d'une transaction échouerait-elle ? Parce que, par exemple, cela implique de modifier une table et de convertir une colonne de chaîne en une colonne datetime, web2py essaie de convertir les données, mais les données ne peuvent pas être converties. Que devient web2py ? Il est confus quant à la structure exacte de la table réellement stockée dans la base de données.

La solution consiste à désactiver les migrations pour toutes les tables et à activer les fausses migrations :

Cela reconstruira les métadonnées web2py sur la table en fonction de la définition de la table. Essayez plusieurs définitions de table pour voir laquelle fonctionne (celle avant l'échec de la migration et celle après l'échec de la migration). Une fois réussi, supprimez le paramètre fake_migrate=True.

Avant de tenter de résoudre les problèmes de migration, il est prudent de faire une copie des fichiers "applications/yourapp/databases/*.table".

Les problèmes de migration peuvent également être résolus pour toutes les tables à la fois :

Cela échoue également si le modèle décrit des tables qui n'existent pas dans la base de données, mais cela peut aider à réduire le problème.

Résumé du contrôle de migration

La logique des différents arguments de migration est résumée dans ce pseudo-code :

Insérer

Étant donné une table, vous pouvez insérer des enregistrements

Insert renvoie la valeur "id" unique de chaque enregistrement inséré.

Vous pouvez tronquer la table, c'est-à-dire supprimer tous les enregistrements et réinitialiser le compteur de l'identifiant.

Maintenant, si vous insérez à nouveau un enregistrement, le compteur recommence à 1 (ceci est spécifique au back-end et ne s'applique pas à Google NoSQL) :

Notez que vous pouvez passer des paramètres à truncate , par exemple, vous pouvez dire à SQLITE de redémarrer le compteur d'identifiants.

L'argument est en SQL brut et donc spécifique au moteur.

web2py fournit également une méthode bulk_insert

Il prend une liste de dictionnaires de champs à insérer et effectue plusieurs insertions à la fois. Il renvoie les ID des enregistrements insérés. Sur les bases de données relationnelles prises en charge, il n'y a aucun avantage à utiliser cette fonction par opposition à la mise en boucle et à l'exécution d'insertions individuelles, mais sur Google App Engine NoSQL, il y a un avantage majeur en termes de vitesse.

Commit et rollback

Aucune opération de création, de suppression, d'insertion, de troncature, de suppression ou de mise à jour n'est réellement validée jusqu'à ce que web2py émette la commande de validation. Dans les modèles, les vues et les contrôleurs, web2py le fait pour vous, mais dans les modules, vous devez effectuer le commit.

Pour le vérifier insérons un nouvel enregistrement :

et annuler, c'est-à-dire ignorer toutes les opérations depuis le dernier commit :

Si vous insérez maintenant à nouveau, le compteur sera à nouveau mis à 2, puisque l'insertion précédente a été annulée.

Le code dans les modèles, les vues et les contrôleurs est inclus dans le code web2py qui ressemble à ceci :

Ainsi, dans les modèles, les vues et les contrôleurs, il n'est jamais nécessaire d'appeler un commit ou un rollback explicitement dans web2py, à moins que vous n'ayez besoin d'un contrôle plus granulaire. Cependant, dans les modules, vous devrez utiliser commit() .

SQL brut

Requêtes de temps

Toutes les requêtes sont automatiquement chronométrées par web2py. La variable db._timings est une liste de tuples. Chaque tuple contient la requête SQL brute transmise au pilote de base de données et le temps d'exécution en secondes. Cette variable peut être affichée dans les vues à l'aide de la barre d'outils :

Exécutersql

Le DAL vous permet d'émettre explicitement des instructions SQL.

Dans ce cas, les valeurs de retour ne sont pas analysées ou transformées par le DAL et le format dépend du pilote de base de données spécifique. Cette utilisation avec selects n'est normalement pas nécessaire, mais elle est plus courante avec les index. executesql prend quatre arguments facultatifs : placeholders , as_dict , fields et colnames . les espaces réservés sont une séquence facultative de valeurs à substituer ou, si pris en charge par le pilote de base de données, un dictionnaire avec des clés correspondant aux espaces réservés nommés dans votre SQL.

Si as_dict est défini sur True, le curseur de résultats renvoyé par le pilote de base de données sera converti en une séquence de dictionnaires codés avec les noms de champ de base de données. Les résultats renvoyés avec as_dict = True sont les mêmes que ceux renvoyés lors de l'application .as_list() à une sélection normale.

L'argument fields est une liste d'objets DAL Field qui correspondent aux champs renvoyés par la base de données. Les objets Champ doivent faire partie d'un ou plusieurs objets Table définis sur l'objet DAL. La liste des champs peut inclure un ou plusieurs objets Table DAL en plus ou au lieu d'inclure des objets Champ, ou il peut s'agir d'une seule table (pas dans une liste). Dans ce cas, les objets Field seront extraits de la (des) table(s).

Au lieu de spécifier l'argument champs, l'argument colnames peut être spécifié sous la forme d'une liste de noms de champs au format tablename.fieldname. Encore une fois, ceux-ci doivent représenter des tables et des champs définis sur l'objet DAL.

Il est également possible de spécifier les deux champs et les colnames associés. Dans ce cas, les champs peuvent également inclure des objets Expression DAL en plus des objets Champ. Pour les objets Field dans "fields", les noms de colonne associés doivent toujours être au format tablename.fieldname. Pour les objets Expression dans les champs , les noms de colonne associés peuvent être des étiquettes arbitraires.

Notez que les objets Table DAL référencés par des champs ou des noms de colonnes peuvent être des tables factices et n'ont pas à représenter de vraies tables dans la base de données. Notez également que les champs et les noms de colonne doivent être dans le même ordre que les champs du curseur de résultats renvoyés par la base de données.

_lastsql

Que SQL ait été exécuté manuellement à l'aide de executesql ou qu'il ait été généré par le DAL, vous pouvez toujours trouver le code SQL dans db._lastsql . Ceci est utile à des fins de débogage :

Enfin, vous pouvez supprimer des tables et toutes les données seront perdues :

Remarque pour sqlite : web2py ne recréera pas la table supprimée tant que vous n'aurez pas navigué dans le système de fichiers vers le répertoire des bases de données de votre application et supprimé le fichier associé à la table supprimée.

Index

Actuellement, l'API DAL ne fournit pas de commande pour créer des index sur les tables, mais cela peut être fait à l'aide de la commande executesql. En effet, l'existence d'index peut rendre les migrations complexes, et il vaut mieux les traiter explicitement. Des index peuvent être nécessaires pour les champs utilisés dans les requêtes récurrentes.

D'autres dialectes de base de données ont des syntaxes très similaires mais peuvent ne pas prendre en charge la directive facultative "IF NOT EXISTS".

Bases de données héritées et tables à clé

web2py peut se connecter à des bases de données héritées sous certaines conditions.

Le moyen le plus simple est lorsque ces conditions sont remplies :

  • Chaque table doit avoir un champ entier d'auto-incrémentation unique appelé "id"
  • Les enregistrements doivent être référencés exclusivement à l'aide du champ « id ».

Lorsque vous accédez à une table existante, c'est-à-dire une table non créée par web2py dans l'application en cours, définissez toujours migrate=False .

Si la table héritée a un champ entier à incrémentation automatique mais qu'il ne s'appelle pas "id", web2py peut toujours y accéder mais la définition de la table doit contenir explicitement Field('. ','id') où . est le nom du champ entier à incrémentation automatique.

Enfin si la table héritée utilise une clé primaire qui n'est pas un champ d'identifiant auto-incrémenté il est possible d'utiliser une "table à clé", par exemple :

  • primarykey est une liste des noms de champs qui composent la clé primaire.
  • Tous les champs de clé primaire ont une valeur NOT NULL même s'ils ne sont pas spécifiés.
  • Les tables à clé ne peuvent référencer que d'autres tables à clé.
  • Les champs de référence doivent utiliser le format de référence tablename.fieldname.
  • La fonction update_record n'est pas disponible pour les lignes de tables à clés.

Au moment de la rédaction, nous ne pouvons pas garantir que l'attribut primarykey fonctionne avec chaque table héritée existante et chaque backend de base de données pris en charge. Pour plus de simplicité, nous vous recommandons, si possible, de créer une vue de base de données avec un champ d'identification à incrémentation automatique.

Transaction distribuée

En supposant que vous ayez deux (ou plus) connexions à des bases de données PostgreSQL distinctes, par exemple :

Dans vos modèles ou contrôleurs, vous pouvez les commiter simultanément avec :

En cas d'échec, cette fonction est annulée et lève une Exception .

Dans les contrôleurs, lorsqu'une action revient, si vous avez deux connexions distinctes et que vous n'appelez pas la fonction ci-dessus, web2py les valide séparément. Cela signifie qu'il est possible que l'un des commits réussisse et que l'autre échoue. La transaction distribuée empêche que cela se produise.

En savoir plus sur les téléchargements

Considérez le modèle suivant :

Dans le cas d'un champ 'upload', la valeur par défaut peut éventuellement être définie sur un chemin (un chemin absolu ou un chemin relatif au dossier de l'application actuel) et l'image par défaut sera définie sur une copie du fichier sur le chemin . Une nouvelle copie est faite pour chaque nouvel enregistrement qui ne spécifie pas d'image.

Normalement, une insertion est gérée automatiquement via un SQLFORM ou un formulaire brut (qui est un SQLFORM), mais parfois vous avez déjà le fichier sur le système de fichiers et souhaitez le télécharger par programme. Cela peut être fait de cette manière :

Il est également possible d'insérer un fichier de manière plus simple et de stocker automatiquement l'appel de la méthode insert :

Dans ce cas, le nom de fichier est obtenu à partir de l'objet stream s'il est disponible.

La méthode de stockage de l'objet de champ de téléchargement prend un flux de fichier et un nom de fichier. Il utilise le nom de fichier pour déterminer l'extension (type) du fichier, crée un nouveau nom temporaire pour le fichier (selon le mécanisme de téléchargement web2py) et charge le contenu du fichier dans ce nouveau fichier temporaire (sous le dossier uploads, sauf indication contraire). Il renvoie le nouveau nom temporaire, qui est ensuite stocké dans le champ image de la table db.myfile.

Notez que si le fichier doit être stocké dans un champ blob associé plutôt que dans le système de fichiers, la méthode store() n'insérera pas le fichier dans le champ blob (car store() est appelé avant l'insertion), donc le fichier doit être explicitement inséré dans le champ blob :

Le contraire de .store est .retrieve :

Requête , Ensemble , Lignes

Considérons à nouveau la table définie (et supprimée) précédemment et insérons trois enregistrements :

Vous pouvez stocker la table dans une variable. Par exemple, avec la variable personne , vous pourriez faire :

Vous pouvez également stocker un champ dans une variable telle que name . Par exemple, vous pouvez également faire :

Vous pouvez même créer une requête (en utilisant des opérateurs tels que ==, !=, <, >, <=, >=, like, Classifieds) et stocker la requête dans une variable q telle que dans :

Lorsque vous appelez db avec une requête, vous définissez un ensemble d'enregistrements. Vous pouvez le stocker dans une variable s et écrire :

Notez qu'aucune requête de base de données n'a été effectuée jusqu'à présent. DAL + Query définissent simplement un ensemble d'enregistrements dans cette base de données qui correspondent à la requête. web2py détermine à partir de la requête quelle table (ou tables) est impliquée et, en fait, il n'est pas nécessaire de le spécifier.

Sélectionner

Étant donné un Set, s , vous pouvez récupérer les enregistrements avec la commande select :

Elle retourne un objet itérable de classe gluon.sql.Rows dont les éléments sont des objets Row. Les objets gluon.sql.Row agissent comme des dictionnaires, mais leurs éléments sont également accessibles en tant qu'attributs, comme gluon.storage.Storage . Le premier diffère du second car ses valeurs sont en lecture seule.

L'objet Rows permet de boucler sur le résultat de la sélection et d'imprimer les valeurs de champ sélectionnées pour chaque ligne :

Vous pouvez effectuer toutes les étapes dans une seule instruction :

La commande select peut prendre des arguments. Tous les arguments sans nom sont interprétés comme les noms des champs que vous souhaitez récupérer. Par exemple, vous pouvez être explicite sur la récupération du champ "id" et du champ "name":

L'attribut de table ALL vous permet de spécifier tous les champs :

Notez qu'aucune chaîne de requête n'est transmise à db. web2py comprend que si vous voulez tous les champs de la personne de la table sans informations supplémentaires, alors vous voulez tous les enregistrements de la personne de la table.

Une syntaxe alternative équivalente est la suivante :

et web2py comprend que si vous demandez tous les enregistrements de la personne de la table (id > 0) sans informations supplémentaires, alors vous voulez tous les champs de la personne de la table.

vous pouvez extraire ses valeurs à l'aide de plusieurs expressions équivalentes :

Cette dernière syntaxe est particulièrement pratique lors de la sélection d'une expression au lieu d'une colonne. Nous montrerons cela plus tard.

et activez, à la place, la notation moins compacte :

Oui, c'est inhabituel et rarement nécessaire.

Rendu des lignes à l'aide de represent

Vous souhaiterez peut-être réécrire les lignes renvoyées par select pour tirer parti des informations de formatage contenues dans le paramètre represent des champs.

Si vous ne spécifiez pas d'index, vous obtenez un générateur pour itérer sur toutes les lignes :

Peut également être appliqué aux tranches :

Si vous souhaitez uniquement transformer les champs sélectionnés via leur attribut "représenter", vous pouvez les lister dans l'argument "champs":

Notez qu'il renvoie une copie transformée de la ligne d'origine, il n'y a donc pas de update_record (ce que vous ne voudriez pas de toute façon) ou delete_record.

Raccourcis

Le DAL prend en charge divers raccourcis simplifiant le code. En particulier:

renvoie l'enregistrement avec l'identifiant donné s'il existe. Si l'identifiant n'existe pas, il renvoie None . L'énoncé ci-dessus équivaut à

Vous pouvez supprimer des enregistrements par identifiant :

et cela équivaut à

et supprime l'enregistrement avec l'identifiant donné, s'il existe.

Remarque : Cette syntaxe de raccourci de suppression ne fonctionne pas actuellement si la gestion des versions est activée

et il crée un nouvel enregistrement avec les valeurs de champ spécifiées par le dictionnaire sur le côté droit.

et il met à jour un enregistrement existant avec les valeurs de champ spécifiées par le dictionnaire sur le côté droit.

Récupérer une ligne

Une autre syntaxe pratique est la suivante :

Apparemment similaire à db.mytable[id], la syntaxe ci-dessus est plus flexible et plus sûre.Tout d'abord, il vérifie si id est un int (ou str(id) est un int) et renvoie None sinon (il ne lève jamais d'exception). Il permet également de spécifier plusieurs conditions que l'enregistrement doit remplir. Si elles ne sont pas remplies, elle renvoie également None .

Sélections récursives

Considérez la table précédente personne et une nouvelle table "chose" faisant référence à une "personne":

et une simple sélection dans ce tableau :

où ._id est une référence à la clé primaire de la table. Normalement, db.thing._id est le même que db.thing.id et nous supposerons que dans la plupart de ce livre.

Pour chaque ligne d'objets, il est possible de récupérer non seulement les champs de la table sélectionnée (objet) mais également des tables liées (récursivement) :

Ici, thing.owner_id.name nécessite une sélection de base de données pour chaque chose dans les choses et est donc inefficace. Nous suggérons d'utiliser des jointures chaque fois que cela est possible au lieu de sélections récursives, néanmoins cela est pratique et pratique lors de l'accès à des enregistrements individuels.

Vous pouvez également le faire à l'envers, en sélectionnant les éléments référencés par une personne :

Dans cette dernière expression person.thing est un raccourci pour

c'est-à-dire l'ensemble des choses référencées par la personne actuelle. Cette syntaxe se décompose si la table de référencement a plusieurs références à la table référencée. Dans ce cas, il faut être plus explicite et utiliser une requête complète.

Sérialisation des lignes dans les vues

Étant donné l'action suivante contenant une requête

Le résultat d'une sélection peut être affiché dans une vue avec la syntaxe suivante :

SQLTABLE convertit les lignes en un tableau HTML avec un en-tête contenant les noms de colonnes et une ligne par enregistrement. Les lignes sont marquées en alternance classe "pair" et classe "impair". Sous le capot, Rows est d'abord converti en un objet SQLTABLE (à ne pas confondre avec Table) puis sérialisé. Les valeurs extraites de la base de données sont également formatées par les validateurs associés au champ puis échappées.

Pourtant, il est possible et parfois pratique d'appeler explicitement SQLTABLE.

Le constructeur SQLTABLE accepte les arguments facultatifs suivants :

Si vous lui attribuez une chaîne avec le nom d'une action, il générera un lien vers cette fonction en lui passant, en tant qu'arguments, le nom de la table et l'identifiant de chaque enregistrement (dans cet ordre). Exemple:

Si vous souhaitez qu'un lien différent soit généré, vous pouvez spécifier un lambda, qui recevra en paramètres, la valeur de l'id, le type de l'objet (ex. table), et le nom de l'objet. Par exemple, si vous souhaitez recevoir les arguments dans l'ordre inverse :

  • télécharger l'URL ou l'action de téléchargement pour permettre le téléchargement des fichiers téléchargés (par défaut, aucun)
  • headers un dictionnaire mappant les noms de champs à leurs étiquettes à utiliser comme en-têtes (par défaut <> ). Il peut aussi s'agir d'une instruction. Actuellement, nous prenons en charge headers='fieldname:capitalize' .
  • tronquer le nombre de caractères pour tronquer les valeurs longues dans le tableau (la valeur par défaut est 16)
  • colonnes la liste des noms de champs à afficher sous forme de colonnes (au format tablename.fieldname). Ceux qui ne sont pas répertoriés ne sont pas affichés (tous par défaut).
  • **attributs attributs d'aide génériques à transmettre à l'objet TABLE le plus externe.

Voici un exemple d'utilisation de SQLFORM.grid :

et la vue correspondante :

Pour travailler avec plusieurs lignes, SQLFORM.grid et SQLFORM.smartgrid sont préférés à SQLTABLE car ils sont plus puissants. Veuillez consulter le chapitre 7.

Orderby , groupby , limitby , distinct , ayant , orderby_on_limitby , gauche , cache

La commande select prend un certain nombre d'arguments facultatifs.

Commandé par

Vous pouvez récupérer les enregistrements triés par nom :

Vous pouvez récupérer les enregistrements triés par nom dans l'ordre inverse (notez le tilde) :

Vous pouvez faire apparaître les enregistrements récupérés dans un ordre aléatoire :

Vous pouvez trier les enregistrements selon plusieurs champs en les concaténant avec un "|":

Groupby, avoir

En utilisant groupby avec orderby , vous pouvez regrouper des enregistrements avec la même valeur pour le champ spécifié (ceci est spécifique au back-end et n'est pas sur Google NoSQL) :

Vous pouvez utiliser avoir en conjonction avec groupby pour grouper de manière conditionnelle (seuls ceux ayant la condition sont regroupés.

Notez que query1 filtre les enregistrements à afficher, query2 filtre les enregistrements à regrouper.

Distinct

Avec l'argument distinct=True , vous pouvez spécifier que vous souhaitez uniquement sélectionner des enregistrements distincts. Cela a le même effet que le regroupement à l'aide de tous les champs spécifiés, sauf qu'il ne nécessite pas de tri. Lors de l'utilisation de distinct, il est important de ne pas sélectionner TOUS les champs, et en particulier de ne pas sélectionner le champ "id", sinon tous les enregistrements seront toujours distincts.

Notez que distinct peut aussi être une expression par exemple :

Limiter par

Avec limitby=(min, max), vous pouvez sélectionner un sous-ensemble des enregistrements de offset=min à mais sans inclure offset=max (dans ce cas, les deux premiers commençant à zéro) :

Orderby_on_limitby

Notez que le DAL ajoute implicitement un orderby lors de l'utilisation d'un limitby. Cela garantit que la même requête renvoie les mêmes résultats à chaque fois, ce qui est important pour la pagination. Mais cela peut causer des problèmes de performances. utilisez orderby_on_limitby = False pour changer cela (cette valeur par défaut est True).

Discuté ci-dessous dans la section sur les jointures

Cache, pouvant être mis en cache

Un exemple d'utilisation qui donne des sélections beaucoup plus rapides est :

Voir la discussion sur « la mise en cache des sélections », ci-dessous, pour comprendre quels sont les compromis.

Opérateurs logiques

Les requêtes peuvent être combinées à l'aide de l'opérateur binaire AND " & " :

et l'opérateur OU binaire " | " :

Vous pouvez annuler une requête (ou sous-requête) avec l'opérateur binaire " != " :

ou par négation explicite avec le "

" a une priorité plus élevée que les opérateurs de comparaison, donc

Les comparaisons négatives doivent également être mises entre parenthèses.

Il est également possible de créer des requêtes à l'aide d'opérateurs logiques sur place :

Compter , est vide , supprimer , mettre à jour

Vous pouvez compter les enregistrements dans un ensemble :

Notez que count prend un argument distinct facultatif qui est par défaut False, et cela fonctionne très bien comme le même argument pour select . count a également un argument de cache qui fonctionne de manière très similaire à l'argument équivalent de la méthode select.

Parfois, vous devrez peut-être vérifier si une table est vide. Un moyen plus efficace que de compter consiste à utiliser la méthode isempty :

Vous pouvez supprimer des enregistrements dans un ensemble :

Et vous pouvez mettre à jour tous les enregistrements d'un ensemble en passant des arguments nommés correspondant aux champs qui doivent être mis à jour :

Expressions

La valeur affectée à une instruction de mise à jour peut être une expression. Par exemple, considérons ce modèle

Les valeurs utilisées dans les requêtes peuvent également être des expressions

Une expression peut contenir une clause case par exemple :

Update_record

web2py permet également de mettre à jour un seul enregistrement déjà en mémoire à l'aide de update_record

update_record ne doit pas être confondu avec

car pour une seule ligne, la méthode update met à jour l'objet de ligne mais pas l'enregistrement de la base de données, comme dans le cas de update_record .

Il est également possible de modifier les attributs d'une ligne (un à la fois) puis d'appeler update_record() sans arguments pour enregistrer les modifications :

La méthode update_record n'est disponible que si le champ id de la table est inclus dans la sélection et que cacheable n'est pas défini sur True .

Insertion et mise à jour à partir d'un dictionnaire

Un problème courant consiste à devoir insérer ou mettre à jour des enregistrements dans une table où le nom de la table, le champ à mettre à jour et la valeur du champ sont tous stockés dans des variables. Par exemple : tablename , fieldname et value .

L'insertion peut se faire en utilisant la syntaxe suivante :

La mise à jour de l'enregistrement avec l'identifiant donné peut être effectuée avec :

Notez que nous avons utilisé table._id au lieu de table.id . De cette façon, la requête fonctionne même pour les tables avec un champ de type "id" qui a un nom autre que "id".

Premier et dernier

Étant donné un objet Rows contenant des enregistrements :

As_dict et as_list

Un objet Row peut être sérialisé dans un dictionnaire normal à l'aide de la méthode as_dict() et un objet Rows peut être sérialisé dans une liste de dictionnaires à l'aide de la méthode as_list(). Voici quelques exemples:

Ces méthodes sont pratiques pour transmettre des lignes à des vues génériques et/ou pour stocker des lignes dans des sessions (puisque les objets Rows eux-mêmes ne peuvent pas être sérialisés car ils contiennent une référence à une connexion DB ouverte) :

Combiner des rangées

Les objets de ligne peuvent être combinés au niveau Python. Ici, nous supposons :

Vous pouvez faire une intersection des enregistrements dans deux ensembles de lignes :

Vous pouvez faire une union des enregistrements en supprimant les doublons :

Rechercher , exclure , trier

Parfois, vous devez effectuer deux sélections et l'une contient un sous-ensemble d'une sélection précédente. Dans ce cas, il est inutile d'accéder à nouveau à la base de données. Les objets find , exclude et sort vous permettent de manipuler un objet Rows et d'en générer un autre sans accéder à la base de données. Plus précisement:

  • find renvoie un nouvel ensemble de lignes filtrées par une condition et laisse l'original inchangé.
  • exclure renvoie un nouvel ensemble de lignes filtrées par une condition et les supprime des lignes d'origine.
  • sort renvoie un nouvel ensemble de lignes triées par une condition et laisse l'original inchangé.

Toutes ces méthodes prennent un seul argument, une fonction qui agit sur chaque ligne individuelle.

Voici un exemple d'utilisation :

Sort prend un argument facultatif reverse=True avec le sens évident.

La méthode find a un argument limitby facultatif avec la même syntaxe et les mêmes fonctionnalités que la méthode Set select .

Autres méthodes

Update_or_insert

Parfois, vous devez effectuer une insertion uniquement s'il n'y a pas d'enregistrement avec les mêmes valeurs que celles insérées. Cela peut être fait avec

L'enregistrement ne sera inséré que s'il n'y a pas d'autre utilisateur appelé John né à Chicago.

Vous pouvez spécifier les valeurs à utiliser comme clé pour déterminer si l'enregistrement existe. Par exemple:

et s'il y a John, son lieu de naissance sera mis à jour, sinon un nouvel enregistrement sera créé.

Le critère de sélection dans l'exemple ci-dessus est un champ unique. Il peut également s'agir d'une requête, telle que

Valider_et_insérer , valider_et_mettre à jour

sauf qu'il appelle les validateurs pour les champs avant d'effectuer l'insertion et saute si la validation ne passe pas. Si la validation échoue, les erreurs peuvent être trouvées dans ret.errors . ret.errors contient un mappage clé-valeur où chaque clé est le nom du champ dont la validation a échoué, et la valeur de la clé est le résultat de l'erreur de validation (un peu comme form.errors ). S'il réussit, l'id du nouvel enregistrement est dans ret.id . N'oubliez pas que la validation est normalement effectuée par la logique de traitement du formulaire, cette fonction est donc rarement nécessaire.

fonctionne à peu près de la même manière que

sauf qu'il appelle les validateurs pour les champs avant d'effectuer la mise à jour. Notez que cela ne fonctionne que si la requête implique une seule table. Le nombre d'enregistrements mis à jour se trouve dans res.updated et les erreurs seront ret.errors .

Smart_query (expérimental)

Il y a des moments où vous devez analyser une requête en utilisant un langage naturel tel que

Le DAL fournit une méthode pour analyser ce type de requêtes :

Le premier argument doit être une liste de tables ou de champs qui doivent être autorisés dans la recherche. Il lève une RuntimeError si la chaîne de recherche n'est pas valide. Cette fonctionnalité peut être utilisée pour construire des interfaces RESTful (voir chapitre 10) et elle est utilisée en interne par SQLFORM.grid et SQLFORM.smartgrid .

Dans la chaîne de recherche smartquery, un champ peut être identifié par fieldname uniquement et/ou par tablename.fieldname. Les chaînes peuvent être délimitées par des guillemets doubles si elles contiennent des espaces.

Champs calculés

Les champs DAL peuvent avoir un attribut de calcul. Il doit s'agir d'une fonction (ou lambda) qui prend un objet Row et renvoie une valeur pour le champ. Lorsqu'un nouvel enregistrement est modifié, y compris les insertions et les mises à jour, si une valeur pour le champ n'est pas fournie, web2py essaie de calculer à partir des autres valeurs de champ en utilisant la fonction de calcul. Voici un exemple:

Notez que la valeur calculée est stockée dans la base de données et qu'elle n'est pas calculée lors de la récupération, comme dans le cas des champs virtuels, décrits plus loin. Deux applications typiques des champs calculés sont :

  • dans les applications wiki, pour stocker le texte wiki d'entrée traité au format HTML, pour éviter le retraitement à chaque demande
  • pour la recherche, pour calculer les valeurs normalisées d'un champ, à utiliser pour la recherche.

Les champs calculés sont évalués dans l'ordre dans lequel ils sont définis dans la définition de la table. Un champ calculé peut faire référence à des champs calculés précédemment définis (nouveau après la v 2.5.1)

Champs virtuels

Les champs virtuels sont aussi des champs calculés (comme dans la sous-section précédente) mais ils diffèrent de ceux-ci parce qu'ils sont virtuel en ce sens qu'ils ne sont pas stockés dans la base de données et qu'ils sont calculés à chaque fois que des enregistrements sont extraits de la base de données. Ils peuvent être utilisés pour simplifier le code de l'utilisateur sans utiliser de stockage supplémentaire, mais ils ne peuvent pas être utilisés pour la recherche.

Champs virtuels de nouveau style

web2py fournit un moyen nouveau et plus simple de définir des champs virtuels et des champs virtuels paresseux. Cette section est marquée expérimentale car les API peuvent encore changer un peu par rapport à ce qui est décrit ici.

Nous allons considérer ici le même exemple que dans la sous-section précédente. On considère en particulier le modèle suivant :

On peut définir un champ virtuel total_price comme

c'est-à-dire en définissant simplement un nouveau champ total_price comme étant un Field.Virtual . Le seul argument du constructeur est une fonction qui prend une ligne et renvoie les valeurs calculées.

Un champ virtuel défini comme celui ci-dessus est automatiquement calculé pour tous les enregistrements lorsque les enregistrements sont sélectionnés :

Il est également possible de définir des champs de méthode qui sont calculés à la demande, lors de l'appel. Par exemple:

Dans ce cas, row.discounted_total n'est pas une valeur mais une fonction. La fonction prend les mêmes arguments que la fonction passée au constructeur de méthode, à l'exception de la ligne qui est implicite (considérez-la comme self pour les objets rows).

Le champ paresseux de l'exemple ci-dessus permet de calculer le prix total de chaque article :

Et cela permet aussi de passer un pourcentage de remise optionnel (15%) :

Les champs Virtuel et Méthode peuvent également être définis en place lorsqu'une table est définie :

Champs virtuels à l'ancienne

Afin de définir un ou plusieurs champs virtuels, vous pouvez également définir une classe conteneur, l'instancier et la lier à une table ou à un select. Par exemple, considérons le tableau suivant :

On peut définir un champ virtuel total_price comme

Notez que chaque méthode de la classe qui prend un seul argument (self) est un nouveau champ virtuel. self fait référence à chaque ligne du select. Les valeurs de champ sont référencées par chemin complet comme dans self.item.unit_price . La table est liée aux champs virtuels en ajoutant une instance de la classe à l'attribut virtualfields de la table.

Les champs virtuels peuvent également accéder aux champs récursifs comme dans

Notez l'accès au champ récursif self.order_item.item.unit_price où self est l'enregistrement en boucle.

Ils peuvent également agir sur le résultat d'un JOIN

Remarquez comment, dans ce cas, la syntaxe est différente. Le champ virtuel accède à la fois à self.item.unit_price et self.order_item.quantity qui appartiennent à la sélection de jointure. Le champ virtuel est attaché aux lignes de la table à l'aide de la méthode setvirtualfields de l'objet rows. Cette méthode prend un nombre arbitraire d'arguments nommés et peut être utilisée pour définir plusieurs champs virtuels, définis dans plusieurs classes, et les attacher à plusieurs tables :

Les champs virtuels peuvent être fainéant tout ce qu'ils ont à faire est de retourner une fonction et d'y accéder en appelant la fonction :

ou plus court à l'aide d'une fonction lambda :

Une relation à plusieurs

Pour illustrer comment implémenter une à plusieurs relations avec le DAL web2py, définissez une autre table "chose" qui fait référence à la table "personne" que nous redéfinissons ici :

La table "chose" a deux champs, le nom de la chose et le propriétaire de la chose. Le champ "owner_id" identifie un champ de référence. Un type de référence peut être spécifié de deux manières équivalentes :

Ce dernier est toujours converti au premier. Ils sont équivalents sauf dans le cas de tables paresseuses, de références à soi ou d'autres types de références cycliques où l'ancienne notation est la seule notation autorisée.

Lorsqu'un type de champ est une autre table, il est prévu que le champ référence l'autre table par son identifiant. En fait, vous pouvez imprimer la valeur de type réelle et obtenir :

Maintenant, insérez trois choses, deux appartenant à Alex et une à Bob :

Vous pouvez sélectionner comme vous l'avez fait pour n'importe quelle autre table :

Parce qu'une chose a une référence à une personne, une personne peut avoir beaucoup de choses, donc un enregistrement de la table personne acquiert maintenant un nouvel attribut chose, qui est un ensemble, qui définit les choses de cette personne. Cela permet de boucler sur toutes les personnes et de récupérer leurs affaires facilement :

Jointures internes

Une autre façon d'obtenir un résultat similaire consiste à utiliser une jointure, en particulier une INNER JOIN. web2py effectue des jointures automatiquement et de manière transparente lorsque la requête lie deux ou plusieurs tables comme dans l'exemple suivant :

Observez que web2py a fait une jointure, donc les lignes contiennent maintenant deux enregistrements, un de chaque table, liés ensemble. Étant donné que les deux enregistrements peuvent avoir des champs avec des noms en conflit, vous devez spécifier la table lors de l'extraction d'une valeur de champ à partir d'une ligne. Cela signifie qu'avant vous pouviez faire :

et il était évident que ce soit le nom d'une personne ou d'une chose, dans le résultat d'une jointure il faut être plus explicite et dire :

Il existe une syntaxe alternative pour INNER JOINS :

Bien que la sortie soit la même, le SQL généré dans les deux cas peut être différent. Cette dernière syntaxe supprime les ambiguïtés possibles lorsque la même table est jointe deux fois et aliasée :

La valeur de join peut être la liste de db.table.on(. ) à joindre.

Jointure externe gauche

Notez que Carl n'apparaît pas dans la liste ci-dessus car il n'a rien. Si vous avez l'intention de sélectionner des personnes (qu'elles aient des objets ou non) et leurs objets (s'ils en ont), alors vous devez effectuer un LEFT OUTER JOIN. Cela se fait en utilisant l'argument "left" de la commande select. Voici un exemple:

fait la requête de jointure gauche. Ici, l'argument de db.thing.on est la condition requise pour la jointure (la même que celle utilisée ci-dessus pour la jointure interne). Dans le cas d'une jointure gauche, il est nécessaire d'être explicite sur les champs à sélectionner.

Plusieurs jointures gauches peuvent être combinées en passant une liste ou un tuple de db.mytable.on(. ) à l'attribut left.

Regrouper et compter

Lorsque vous effectuez des jointures, vous souhaitez parfois regrouper des lignes selon certains critères et les compter. Par exemple, comptez le nombre de choses possédées par chaque personne. web2py le permet également. Tout d'abord, vous avez besoin d'un opérateur de comptage. Deuxièmement, vous souhaitez joindre la table des personnes à la table des objets par propriétaire. Troisièmement, vous voulez sélectionner toutes les lignes (personne + chose), les regrouper par personne et les compter lors du regroupement :

Notez que l'opérateur de comptage (qui est intégré) est utilisé comme champ. Le seul problème ici est de savoir comment récupérer les informations. Chaque ligne contient clairement une personne et le nombre, mais le nombre n'est pas un champ d'une personne ni un tableau. Alors, où faut-il aller? Il va dans l'objet de stockage représentant l'enregistrement avec une clé égale à l'expression de la requête elle-même. La méthode count de l'objet Field a un argument distinct facultatif. Lorsqu'il est défini sur True, il spécifie que seules les valeurs distinctes du champ en question doivent être comptées.

Plusieurs à plusieurs

Dans les exemples précédents, nous autorisions une chose à avoir un seul propriétaire, mais une personne pouvait avoir plusieurs choses.Et si Boat appartenait à Alex et Curt ? Cela nécessite une relation plusieurs-à-plusieurs, et elle est réalisée via une table intermédiaire qui relie une personne à une chose via une relation de propriété.

la relation de propriété existante peut maintenant être réécrite comme :

Vous pouvez maintenant ajouter la nouvelle relation que Curt est copropriétaire de Boat :

Comme vous avez maintenant une relation à trois voies entre les tables, il peut être pratique de définir un nouvel ensemble sur lequel effectuer des opérations :

Maintenant, il est facile de sélectionner toutes les personnes et leurs affaires dans le nouvel ensemble :

De même, vous pouvez rechercher toutes les choses appartenant à Alex :

Une alternative plus légère aux relations Many 2 Many est le marquage. Le balisage est abordé dans le contexte du validateur IS_IN_DB. Le balisage fonctionne même sur les backends de base de données qui ne prennent pas en charge les JOIN comme Google App Engine NoSQL.

List:<type> et contient

web2py fournit les types de champs spéciaux suivants :

Ils peuvent contenir respectivement des listes de chaînes, d'entiers et de références.

Sur Google App Engine NoSQL list:string est mappé dans StringListProperty , les deux autres sont mappés dans ListProperty(int) . Sur les bases de données relationnelles, ils sont mappés dans des champs de texte qui contiennent la liste des éléments séparés par | . Par exemple [1,2,3] est mappé dans |1|2|3| .

Pour les listes de chaînes, les éléments sont échappés de sorte que tout | dans l'élément est remplacé par un || . Quoi qu'il en soit, il s'agit d'une représentation interne et elle est transparente pour l'utilisateur.

Vous pouvez utiliser list:string , par exemple, de la manière suivante :

list:integer fonctionne de la même manière mais les éléments doivent être des entiers.

Comme d'habitude, les exigences sont appliquées au niveau des formulaires, pas au niveau de l'insertion .

L'opérateur list:reference et l'opérateur contains(value) sont particulièrement utiles pour dénormaliser les relations plusieurs-à-plusieurs. Voici un exemple:

Notez qu'un champ list:reference tag obtient une contrainte par défaut

qui produit une boîte de dépôt multiple SELECT/OPTION dans les formulaires.

Notez également que ce champ obtient un attribut represent par défaut qui représente la liste des références sous la forme d'une liste de références formatées séparées par des virgules. Ceci est utilisé dans les formulaires de lecture et les SQLTABLE .

Autres opérateurs

web2py a d'autres opérateurs qui fournissent une API pour accéder aux opérateurs SQL équivalents. Définissons une autre table "log" pour stocker les événements de sécurité, leur event_time et leur gravité, où la gravité est un nombre entier.

Comme précédemment, insérez quelques événements, un "port scan", une "xss injection" et un "unauthorized login". Pour les besoins de l'exemple, vous pouvez enregistrer des événements avec le même event_time mais avec des gravités différentes (1, 2 et 3 respectivement).

Like , ilike , regexp , commence par , se termine par , contient , supérieur , inférieur

Les champs ont un opérateur similaire que vous pouvez utiliser pour faire correspondre des chaînes :

Ici, "port%" indique une chaîne commençant par "port". Le signe de pourcentage, "%", est un caractère générique qui signifie "toute séquence de caractères".

L'opérateur like correspond au mot LIKE dans ANSI-SQL. LIKE est sensible à la casse dans la plupart des bases de données et dépend du classement de la base de données elle-même. La méthode like est donc sensible à la casse mais elle peut être rendue insensible à la casse avec

web2py fournit également quelques raccourcis :

qui sont à peu près équivalents respectivement à

Notez que contains a une signification particulière pour les champs list:<type> et cela a été discuté dans une section précédente.

La méthode contains peut également recevoir une liste de valeurs et un argument booléen facultatif all pour rechercher des enregistrements contenant toutes les valeurs :

ou toute valeur de la liste

Il existe également une méthode regexp qui fonctionne comme la méthode like mais permet une syntaxe d'expression régulière pour l'expression de recherche. Il n'est pris en charge que par PostgreSQL, MySQL, Oracle et SQLite (avec différents degrés de prise en charge).

Les méthodes majuscules et minuscules vous permettent de convertir la valeur du champ en majuscules ou minuscules, et vous pouvez également les combiner avec l'opérateur similaire :

Année , mois , jour , heure , minutes , secondes

Les champs date et datetime ont des méthodes jour, mois et année. Les champs datetime et time ont des méthodes heure, minutes et secondes. Voici un exemple:

Fait parti

L'opérateur SQL IN est réalisé via la méthode uses qui retourne true lorsque la valeur du champ appartient à l'ensemble spécifié (liste ou tuples) :

Le DAL permet également une sélection imbriquée comme argument de l'opérateur d'appartenance. Le seul inconvénient est que la sélection imbriquée doit être un _select , pas un select , et qu'un seul champ doit être sélectionné explicitement, celui qui définit l'ensemble.

Dans les cas où une sélection imbriquée est requise et le champ de recherche est une référence, nous pouvons également utiliser une requête comme argument. Par exemple:

Dans ce cas, il est évident que la sélection suivante n'a besoin que du champ référencé par le champ db.thing.owner_id, nous n'avons donc pas besoin de la notation _select plus détaillée.

Une sélection imbriquée peut également être utilisée comme valeur d'insertion/mise à jour mais dans ce cas la syntaxe est différente :

Dans ce cas, lazy est une expression imbriquée qui calcule l'identifiant de la personne "Jonathan". Les deux lignes génèrent une seule requête SQL.

Somme , moy , min , max et longueur

Vous pouvez également utiliser avg , min et max pour la valeur moyenne, minimale et maximale respectivement pour les enregistrements sélectionnés. Par exemple:

.len() calcule la longueur d'une chaîne, d'un texte ou d'un champ booléen.

Les expressions peuvent être combinées pour former des expressions plus complexes. Par exemple ici, nous calculons la somme de la longueur de toutes les chaînes de gravité dans les journaux, augmentée de un :

Sous-chaînes

On peut construire une expression pour faire référence à une sous-chaîne. Par exemple, nous pouvons regrouper des éléments dont le nom commence par les trois mêmes caractères et n'en sélectionner qu'un dans chaque groupe :

Valeurs par défaut avec coalesce et coalesce_zero

Il arrive parfois que vous ayez besoin d'extraire une valeur de la base de données, mais que vous ayez également besoin de valeurs par défaut si la valeur d'un enregistrement est définie sur NULL. En SQL, il existe un mot-clé, COALESCE , pour cela. web2py a une méthode de coalescence équivalente :

D'autres fois, vous devez calculer une expression mathématique, mais certains champs ont une valeur définie sur Aucun alors qu'elle devrait être nulle. coalesce_zero vient à la rescousse en mettant par défaut None à zéro dans la requête :

Générer du SQL brut

Parfois, vous devez générer le SQL mais pas l'exécuter. C'est facile à faire avec web2py puisque chaque commande qui effectue des E/S de base de données a une commande équivalente qui ne le fait pas, et renvoie simplement le SQL qui aurait été exécuté. Ces commandes ont les mêmes noms et la même syntaxe que les commandes fonctionnelles, mais elles commencent par un trait de soulignement :


Voir la vidéo: Spatial Queries - Working with Geometries #PostGIS. #PostgreSQL. #QGIS. UrduHindi. #13 (Octobre 2021).