Before we get started on this month’s actual python subject, let me toot my own horn for just a minute. In late December and early January, my first book on Python was published by Apress. It is named “The Python Quick Syntax Reference”, and is available from a number of places. You can find it on the Apress site (http://www.apress.com/9781430264781), Springer.com (http://www.springer.com/computer/book/978-1-4302-6478-1) and Amazon (http://www.amazon.com/The-Python-Quick-Syntax-Reference/dp/1430264780) as well as others. It is, as the title suggests, a syntax reference that will help those of us who program in other languages as well as Python, to remember how a certain command works and the requirements for that command. Please help a poor old programmer make a living by buying the book, if you can. Now on to bigger and better things.

Avant de commencer l'article python de ce mois-ci, permettez-moi une courte page d'auto-publicité. Fin décembre et début janvier, mon premier livre sur Python a été publié aux éditions Apress. Intitulé The Python Quick Syntax Reference, il est disponible dans un certain nombre d'endroits. Vous pouvez le trouver sur les sites Apress (http://www.apress.com/9781430264781), Springer.com (http://www.springer.com/computer/book/978-1-4302-6478-1) et Amazon (http://www.amazon.com/The-Python-Quick-Syntax-Reference/dp/1430264780), ainsi que d'autres. Il s'agit, comme le titre l'indique, d'un ouvrage de référence sur la syntaxe qui aidera ceux d'entre nous qui programment aussi dans d'autres langages que Python à se rappeler le fonctionnement d'une certaine commande et les prérequis pour cette commande. S'il vous plaît, aidez un ancien programmeur à gagner sa vie en achetant le livre, si vous le pouvez.

Passons maintenant à des choses plus importantes et passionnantes.

While I was working on my latest book for Apress, I rediscovered a SQL command that I didn't discuss when we were working with SQL databases a long time ago, so I thought I'd share the information with you. It is the CREATE TABLE AS SELECT command, which allows us to pull a query from one table (or joined tables) and create another table on the fly. The general syntax is: CREATE TABLE [IF NOT EXISTS] {New Table Name} AS SELECT {query} The part in square brackets (IF NOT EXISTS) is totally optional, which will create the table only if it doesn’t exist already. The part in curly brackets, however, is not. The first is the new table name and the second is the query that you want to use to pull data and create the new table.

Alors que je travaillais sur mon dernier livre pour Apress, j'ai redécouvert une commande SQL dont je n'avais pas parlé lorsque nous travaillions avec des bases de données SQL, il y a longtemps, et j'ai pensé que je partagerais l'information avec vous. Il s'agit de la commande CREATE TABLE AS SELECT, qui nous permet d'exécuter une requête sur une table (ou sur des tables jointes) et de créer une autre table à la volée. La syntaxe générale est :

CREATE TABLE [IF NOT EXISTS] {Nouveau nom de la table} AS SELECT {requête}

La partie entre crochets (IF NOT EXISTS) est totalement optionnelle et créera la table seulement si elle n'existe pas déjà. Les parties entre accolades, par contre, ne le sont pas. La première est le nouveau nom de la table et la seconde est la requête que vous souhaitez utiliser pour extraire des données et créer la nouvelle table.

Assume we have a database that has multiple tables in it. One of the tables is named “study” that holds data from a receiving operation. There are six fields which are shown below. One of the datasets that we will need to produce from this raw data is a grouping of package count and the number of days within the study that quantity of packages came in on, assuming that the days are weekdays (Monday thru Friday) and that the day is not a holiday, since holidays have less than normal number of packages. Our query is shown above. This then provides us with data that would look something like this: pkgs CountOfDow 31 1 32 2 33 1 … 48 3

Supposons que nous ayons une base de données contenant plusieurs tables. L'une des tables est nommée « étude » et contient les données de réception de colis. Il y a six champs, présentés ci-dessous.

L'un des ensembles de données que nous devrons produire à partir de ces données brutes est un regroupement du nombre de colis et le nombre de jours au cours de l'étude pendant lesquels ces nombres de colis sont arrivés, en supposant que les jours sont les jours de semaine (lundi au vendredi) et que le jour n'est pas un jour férié, car pendant les vacances il y a moins de colis que d'habitude. Notre requête est indiquée ci-dessus.

Elle nous fournit des données qui ressemblent à ceci :

colis NbDeJoursDeSemaine 31 1 32 2 33 1 … 48 3

So the data is showing that during the study of 65 days, only one weekday had 31 packages but 3 weekdays had 48 packages and so on. Similar queries could be created that would cover holidays and weekends. While having the data simply as a returned dataset from the query, we might want to do further analysis on the data, so we want to put the resulting data from the query into a table. That's why we would create a table from the query. So in the following example, shown above right, we create a table named “weekdays” using the same query we just showed above. Now anytime we need the data for that weekday result set, we can just run a query on the weekdays table.

Ainsi, les données montrent que lors de l'étude de 65 jours, un seul jour de la semaine avait 31 paquets, mais 3 jours de la semaine avaient 48 paquets et ainsi de suite. Des requêtes similaires pourraient être créées pour couvrir les vacances et les week-ends.

On reçoit simplement un ensemble de données retourné par la requête, mais nous pourrions avoir envie de faire une analyse plus approfondie des données, donc nous voulons mettre les données résultant de la requête dans une table. C'est pourquoi nous aimerions créer une table à partir de la requête. Ainsi, dans l'exemple suivant, ci-dessus à droite, nous créons une table nommée « JoursDeSemaine » en utilisant la même requête que nous avons montrée ci-dessus.

Maintenant, quand nous aurons besoin de cet ensemble de données des jours de semaine, nous pourrons simplement exécuter une requête sur la table « JoursDeSemaine ».

Once we know what we need, and have tested the query, then we can begin our code. Assuming we already have the study table created and populated, we can use Python to then create our new table in the main database. Just as an FYI, I am using the APSW SQLite library to do the database work. We, of course, have to open a connection (right) and create a cursor to the SQLite database. We have covered this in a number of past articles. Now we need to create the routine that will actually create the table with the returned dataset from the query, shown below, then alter it and run some calculations.

Une fois que nous savons ce que nous voulons, et que nous avons testé la requête, nous pouvons commencer notre code. En supposant que nous avons déjà la table « étude » créée et remplie, nous pouvons utiliser Python pour créer notre nouvelle table dans la base de données principale. Pour votre information, j'utilise la bibliothèque APSW SQLite pour faire le travail de base de données.

Nous devons, bien sûr, ouvrir une connexion (à droite) et créer un curseur pour la base de données SQLite. Nous avons vu ceci dans de nombreux articles précédents.

Maintenant, nous devons créer la routine qui crée la table avec l'ensemble de données retourné par la requête, ci-dessous, puis la modifier et exécuter quelques calculs.

As you can see, we want to create a second cursor, so that we don’t run any risk of the first cursor having data we need to maintain. We will be using it in the final part of the code. We then drop the table if it exists and run our query on the “study” table. Now we create three more columns (shown below) within the weekdays table named “probability”, “lower” and “upper”. We do this by using the “ALTER TABLE” SQL command. The next step (top right) will be to sum the data in the CountOfDOW field.

Comme vous pouvez le voir, nous créons un deuxième curseur pour ne pas risquer que le premier curseur contienne des données que nous devons conserver. Nous l'utiliserons dans la dernière partie du code. Nous supprimons la table si elle existe et exécutons notre requête sur la table « étude ».

Maintenant, nous créons trois colonnes de plus (ci-dessous) dans la table des jours de semaine, nommées « probabilité », « inférieur » et « supérieur ». Nous faisons cela en utilisant la commande SQL « ALTER TABLE ».

L'étape suivante (en haut à droite) sera d'additionner les données dans le champ NombreDeJoursDeSemaine.

There is only one record returned, but we do the for loop thing anyway. Remember from the above discussion that the “CountOfDow” field holds the number of days during the study that a particular number of packages came in. This gives us a value that contains the sum of all of the “CountOfDow” entries. Just so you have a reference as we go forward, the number I got from all my dummy data is 44. upquery = “SELECT * FROM weekdays” c1 = cursor.execute(upquery)

Il n'y a qu'un seul enregistrement retourné, mais nous faisons quand même une boucle for. Rappelez-vous de ce qui précède que le champ « NombreDeJoursDeSemaine » contient le nombre de jours au cours de l'étude où un nombre déterminé de paquets est arrivé. Cela nous donne une valeur qui contient la somme de toutes les entrées de « NombreDeJoursDeSemaine ». Juste pour que vous ayez une référence pendant que nous progressons : le nombre que j'ai obtenu avec mes données factices est 44.

upquery = “SELECT * FROM JoursDeSemaine”

c1 = cursor.execute(upquery)

Here we have done a ‘SELECT all’ query so every record in the datatable is in the ‘c1’ cursor. We’ll walk through each row of the dataset, pulling the pkgs (row[0]) and CountOfDow (row[1]) data into variables. LastUpper = .0 for row in c1: cod = row[1] pkg = row[0] Now we will create a probability of each daily package count in the database and calculate an upper and lower value that will be used in another process later on. Notice that we check to see if the LastUpper variable contains ‘.0’. If it does, we set it to the probability value, otherwise we set it to the lower plus the probability value.

Ici, nous avons fait une requête « SELECT all », aussi chaque enregistrement de la table de données est dans le curseur « c1 ». Nous allons parcourir chaque ligne de l'ensemble de données, en extrayant les données pkgs (row[0]) et NombreDeJoursDeSemaine (row[1]) dans des variables.

LastUpper = .0 for row in c1:

cod = row[1]
pkg = row[0]

Maintenant, nous allons créer une probabilité pour chaque compte quotidien de paquets dans la base de données et calculer une valeur supérieure et inférieure qui seront utilisées dans un autre processus plus tard. Notez que nous vérifions pour voir si la variable LastUpper contient « .0 ». Si c'est le cas, nous la réglons à la valeur de probabilité, sinon nous la réglons à la plus faible valeur plus la valeur de probabilité.

Finally we use an update SQL statement to put the new computed values into the database. What we end up with is a package count (pkgs), a count of the number of days that package count came in, a probability of that occurring within the whole of the study (31 packages on 1 day out of a total of 44 (weekdays in that 60+ day study), will have a probability of 0.02.). If we add up all the probability values in the table it should add up to 1.0 .

Enfin, nous utilisons l'instruction SQL « UPDATE » pour mettre les nouvelles valeurs calculées dans la base de données.

Nous nous retrouvons avec un nombre de paquet (pkgs), le décompte du nombre de jours où ce nombre de paquets est arrivé, une probabilité que cela se produise dans l'ensemble de l'étude (31 colis sur une journée sur un total de 44 (jours de semaine dans cette étude de plus de 60 jours), auront une probabilité de 0,02).

Si l'on additionne toutes les valeurs de probabilité de la table, on devrait trouver 1.

The upper and lower values then reflect a number between floating point number 0 and 1 that will mirror the possibility of any random number within that range that will give us a randomized number of packages. This number can then be used for a statistics analysis of this data. A “normal real-world” example would be to predict the number of cars that arrive at a carwash based on observational data done in the field. If you want to understand more, you could look at http://www.algebra.com/algebra/homework/Probability-and-statistics/Probability-and-statistics.faq.question.309110.html to see an example of this. All we did is generate (the hard part) easily with Python. The code for the two routines that we presented this time is at: http://pastebin.com/kMc9EXes Until next time.

Les valeurs supérieures et inférieures reflètent alors un nombre compris entre 0 et 1 qui mime la possibilité qu'un nombre aléatoire soit dans cette plage et qui va nous donner un nombre aléatoire de paquets. Ce nombre peut alors être utilisé pour une analyse statistique de ces données. Un exemple du « monde réel normal » serait de prévoir le nombre de voitures qui arrivent à un centre de lavage auto sur la base d'observations effectuées sur le terrain. Si vous voulez comprendre mieux, vous pouvez consulter http://www.algebra.com/algebra/homework/Probability-and-statistics/Probability-and-statistics.faq.question.309110.html pour voir un exemple de cela. Tout ce que nous avons fait est de le générer (le plus dur) facilement avec Python.

Le code pour les deux routines que nous avons présentées cette fois-ci est ici : http://pastebin.com/7EF7epVG

À la prochaine fois.

SELECT pkgs, Count(DOW) as NombreDeJoursDeSemaine FROM etude

 WHERE (Holiday <> 1)
    AND DayName in ("lundi","mardi","mercredi","jeudi","vendredi")
 GROUP BY pkgs

pkID - Integer, Primary Key, AutoIncrement DOM - Integer - Jour du mois (1-31) DOW - Integer - Jour de la semaine (1-7 (dimanche = 1, lundi = 2, etc.)) pkgs - Integer - Nombre de colis reçus ce jour DayName - TEXT - “dimanche”,“lundi”, etc Holiday - Integer 0 ou 1 (Est-ce que ce jour est considéré comme congé ou pas) 1 pour oui

CREATE TABLE IF NOT EXISTS JoursDeSemaine AS

 SELECT pkgs, Count(DOW) as NbDeJoursDeSemaine FROM etude
 WHERE (Holiday <> 1)
    AND DayName in ("lundi","mardi","mercredi","jeudi","vendredi")
 GROUP BY pkgs

def OpenDB():

  global connection
  global curseur
  connection = apsw.Connection("labpackagestudy.db3")
  curseur = connection.cursor()

addcolquery = 'ALTER TABLE JoursDeSemaine ADD COLUMN probability REAL' cursor.execute(addcolquery) addcolquery = 'ALTER TABLE JoursDeSemaine ADD COLUMN lower REAL' cursor.execute(addcolquery) addcolquery = 'ALTER TABLE JoursDeSemaine ADD COLUMN upper REAL' cursor.execute(addcolquery)

def TraiterJoursDeSemaine():

  # on cree un second curseur pour mettre a jour la nouvelle table
   curseur2 = connection.cursor()
   q1 = "DROP TABLE IF EXISTS JoursDeSemaine"
   curseur.execute(q1)
   query = '''CREATE TABLE IF NOT EXISTS JoursDeSemaine AS SELECT pkgs, 
              Count(DOW) as NombreDeJoursDeSemaine FROM etude WHERE (Holiday <> 1) 
              AND DayName in 
              ("lundi","mardi","mercredi","jeudi","vendredi") 
              GROUP BY pkgs'''
   curseur.execute(query)

sumquery = “SELECT Sum(NombreDeJoursDeSemaine) as Sm FROM JoursDeSemaine” tmp = curseur.execute(sumquery) for t in tmp:

 DaySum = t[0]

prob = cod / float(DaySum) if LastUpper != .0:

 lower = LastUpper
 LastUpper = (lower + prob)

else:

 lower = .0
 LastUpper = prob

nquery = 'UPDATE weekdays SET probability = %f, \

      lower = %f, upper = %f WHERE pkgs = %d' \
      % (prob,lower,LastUpper,pkg)

u = cursor2.execute(nquery) #

# End of TraiterJoursDeSemaine #