Comment créer une table de base de données avec pyodbc en Python

Dans cet article, vous obtiendrez les informations nécessaires sur la façon de créer une table dans SQLite en utilisant le package pyodbc en Python. Un exemple de code est inclus et un lien vers un dépôt Github sera fourni dans la section des références.

Prérequis

Pour utiliser pyodbc dans cet article, certaines conditions préalables doivent être remplies.

1. Pilote ODBC – vous devez télécharger et installer le pilote ODBC pour le fournisseur de base de données que vous utilisez. Dans cet article, j'utilise SQLite. Téléchargé un pilote ODCB pour sqlite3 depuis http://www.ch-werner.de/sqliteodbc/,
2. SQLite3 – cet article suppose que vous avez SQLite3 dans votre système et que vous avez créé une base de données appelée testDB.db .
3. LDD de base de données – différents fournisseurs de bases de données utilisent différents langages de définition de données. DDL est le langage nécessaire pour créer des tables dans une base de données. Vous devez être familiarisé avec cette syntaxe pour réussir à créer votre table. La création de tables dans SQLite peut être trouvée ici :https://sqlite.org/lang_createtable.html
4. Concevez votre tableau – C'est le tableau qui sera implémenté dans l'exemple ci-dessous :

Voici un exemple de la raison pour laquelle vous devez connaître le langage de définition de données de votre base de données. Les informations suivantes proviennent de https://www.sqlite.org/datatype3.html

"SQLite n'a pas de classe de stockage réservée pour stocker les dates et/ou les heures. Au lieu de cela, les fonctions de date et d'heure intégrées de SQLite sont capables de stocker des dates et des heures sous forme de valeurs TEXT, REAL ou INTEGER :

• TEXTE sous forme de chaînes ISO8601 ("YYYY-MM-DD HH:MM:SS.SSS" ).
• RÉEL comme nombres de jours juliens, le nombre de jours depuis midi à Greenwich le 24 novembre 4714 av. selon le calendrier grégorien proleptique.
• ENTIER comme heure Unix, le nombre de secondes depuis 1970-01-01 00:00:00 UTC.

Les applications peuvent choisir de stocker les dates et les heures dans l'un de ces formats et de convertir librement entre les formats à l'aide des fonctions de date et d'heure intégrées. »

SQLite Créer un langage de définition de données de table pour la table PeopleInfo :

CREATE TABLE PeopleInfo (
        PersonId INTEGER PRIMARY KEY,
        FirstName TEXT NOT NULL,
        LastName  TEXT NOT NULL,
        Age INTEGER NULL,
        CreatedAt TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL
 
);

Plongeons-nous dans les étapes pour créer une table dans SQLite en utilisant pyodbc en Python.

Étape 1 :Installez le package pyodbc

Installez le package Pyodbc à l'aide de la commande suivante :

pip install pyodbc

Pour Anaconda, utilisez la commande suivante :

conda install -c anaconda pyodbc

Étape 2 :Connectez votre script Python à SQLite

Ensuite, vous devrez connecter votre script à SQLite.

Vous pouvez utiliser ce modèle pour effectuer la connexion :

N'oubliez pas d'importer le pyodbc paquet d'abord. Connectez-vous à la base de données en utilisant la méthode connect avec la chaîne de connexion ODBC pour SQLite.

import pyodbc
conn = pyodbc.connect('Driver={SQLite3 ODBC Driver};'
                      'Server=server_name;'
                      'Database=database_name;'
                      'Trusted_Connection=yes;')

Étape 3 :Créer un curseur à partir de votre connexion

L'objet curseur créé par cursor() vous permet d'exécuter des requêtes.

cursor = conn.cursor()

Étape 4 :Créer la table dans SQLite

Vous allez maintenant pouvoir créer votre table en SQLite

Pour notre exemple, voici le code que j'ai utilisé pour créer la table dans SQL Server à l'aide de Python : 

cursor.execute('''
 CREATE TABLE PeopleInfo (
        PersonId INTEGER PRIMARY KEY,
        FirstName TEXT NOT NULL,
        LastName  TEXT NOT NULL,
        Age INTEGER NULL,
        CreatedAt TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL
 
);

               ''')

Étape 5 :valider la transaction

conn.commit()

La validation de la transaction est un sujet important à aborder. L'instruction ci-dessus valide explicitement la transaction. Si vous ne validez pas la transaction, la base de données ne créera pas la table. La transaction sera annulée.

Les autres options à considérer sont :

1 – Ajoutez le paramètre autocommit pour vous connecter. Cela ne nécessitera pas de validation manuelle. Par exemple :

con = pyodbc.connect(your_connection_string, autocommit = True)

2 – Utilisez un with bloc et tout sera validé avant que la connexion ne soit terminée à la fin du with bloquer. Par exemple :

with pyodbc.connect(your_connection_string) as con:
    CREATE_TABLE_CODE

Étape 6 :Insérez des enregistrements pour vérifier que votre table est correctement configurée.

cursor.execute('''
                INSERT INTO PeopleInfo (PersonId, FirstName, LastName, Age)
                VALUES
                (1,'Bob','Smith', 55),
                (2, 'Jenny','Smith', 66)
                ''')
conn.commit()

Étape 7 :Exécutez une requête SELECT pour récupérer les enregistrements.

cursor.execute('SELECT * FROM PeopleInfo')
for row in cursor:
    print(row)

Étape 8 :Fermez votre connexion si elle n'est pas nécessaire.

conn.close()

Les connexions à la base de données sont une ressource coûteuse et les connexions disponibles pour votre base de données peuvent être limitées. N'oubliez pas de fermer explicitement votre connexion si vous n'utilisez pas de bloc "avec", comme expliqué à l'étape 5.

Code complet :

import pyodbc
conn = pyodbc.connect('Driver={SQLite3 ODBC Driver};'
                      'Server=localhost;'
                      'Database=testDB.db;'
                      'Trusted_Connection=yes;')
cursor = conn.cursor()
cursor.execute('''
 CREATE TABLE PeopleInfo (
        PersonId INTEGER PRIMARY KEY,
        FirstName TEXT NOT NULL,
        LastName  TEXT NOT NULL,
        Age INTEGER NULL,
        CreatedAt TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL
 
);

               ''')
conn.commit()

cursor.execute('''
                INSERT INTO PeopleInfo (PersonId, FirstName, LastName, Age)
                VALUES
                (1,'Bob','Smith', 55),
                (2, 'Jenny','Smith', 66)
                ''')
conn.commit()

cursor.execute('SELECT * FROM PeopleInfo ')
for row in cursor:
    print(row)

conn.close()

Sortie :

(1, 'Bob', 'Smith', 55, '2020-08-01 20:37:04')
(2, 'Jenny', 'Smith', 66, '2020-08-01 20:37:04')

Références

1 – Dépôt Github Pyodbc :https://github.com/mkleehammer/pyodbc

2 – Premiers pas avec Pyodbc :https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki

3 – Créer des tables avec SQLite :https://www.sqlitetutorial.net/sqlite-create-table/