This article builds on parts of a 2012 article, Abstracting the database engine with the SQLAlchemy ORM and Python, which explains how to use sqlalchemy and sqlite3 with Python.

The full faker documentation is available at this link. This library generates fake data such as names, phone numbers, jobs and countries, among others.

The idea for this article comes from a Towards Data Science article, Generating Random Data into a Database Using Python.

Installing the required packages and importing the libraries.

Faker and sqlalchemy need to be installed.

pip install Faker sqlalchemy

The code explained

  1. Import the Faker and sqlalchemy libraries
from sqlalchemy import *
from faker import Faker
  1. Create the sqlite database contactos.db and its table with the required fields

The table is created with these fields:

  • id: Id of the inserted record, integer, primary key.
  • nombre: Full name, string.
  • telefono: Phone number, string.
  • pais: Country, string.
  • fecha_nacimiento: Date of birth, string.
  • trabajo: Job, string.
  • correo: Email, string.

db = create_engine('sqlite:///contactos.db')
#Se deshabilita el verbose de las ejecuciones. 
db.echo = False
# Se define la metadata
metadata = MetaData(db)
# Se crea la tabla contactos con sus campos.
contactos = Table('contactos',metadata,
    Column('id',Integer,primary_key=True),
    Column('nombre', Unicode(100)),
    Column('telefono',String(11)),
    Column('pais',String(50)),
    Column('fecha_nacimiento',String(20)),
    Column('trabajo',String(50)),
    Column('correo',String(50)))

# Se crea la tabla en la base de datos. 
metadata.create_all()
  1. Generate the random data and insert it into the database.
# A continuación se define la instancia de faker con datos localizados para España, Mexico, Italia y Estados Unidos.
fake = Faker(['it_IT', 'en_US', 'es_ES','es_MX'])

# Se crea la instancia para insertar los datos en la tabla contactos.
u = contactos.insert()

# Se generan 300 datos aleatorios falsos. 
# Nombre, telefono, id, país, fecha de nacimiento, trabajo y correo.
for i in range(300):
    u.execute(nombre=fake.name(),
              telefono=fake.phone_number(),
              id=i,
             pais=fake.country(),
             fecha_nacimiento=fake.date_of_birth(),
             trabajo=fake.job(),
             correo=fake.ascii_free_email())
  1. Query the database.

Once the data is inserted, a select lists it:

# Se crea el select para contactos y se ejecuta.
s = contactos.select()
rs = s.execute()
# Se recorre las filas
for fila in rs:
    print (fila)

The output looks something like this:

(0, 'Ing. Luz Cantú', '(592)035-0771', 'Irlanda', '1941-07-16', 'Cartographer', 'eloy53@yahoo.com')
(1, 'Alexander Rogers', '+39 2741 04213055', 'China', '1981-01-07', 'Lobbyist', 'avilesdelia@gmail.com')
(2, 'William Mata', '+98(4)6812081105', 'Nueva Zelandia', '2014-07-27', 'Administrator, charities/voluntary organisations', 'bustoszeferino@yahoo.com')
(3, 'Victor Manuel de Riquelme', '+34868799533', 'United States of America', '1920-08-07', 'Therapist, speech and language', 'alarconvictor@hotmail.com')
(4, 'Desiderio Tudela Hurtado', '+39 826 2913339', 'República Democrática Popular Lao', '2003-02-25', 'Dance movement psychotherapist', 'teodorosantacruz@gmail.com')
(5, 'Héctor Figueroa', '+39 430 26536170', 'Saint Helena', '1949-09-30', 'Historic buildings inspector/conservation officer', 'gabrielabenavides@hotmail.com')
(6, 'Alison Fernandez', '060.424.1361x63279', 'Nicaragua', '1923-06-10', 'Operations geologist', 'sergiomesa@yahoo.com')
(7, 'Kike Alberdi Solé', '1-944-411-9964x56873', 'México', '2011-10-23', 'Automotive engineer', 'nmontanari@hotmail.it')
(8, 'Giuliano Proietti', '(221)607-8605', 'Vietman', '2002-10-31', 'Horticulturist, amenity', 'barbarasotelo@gmail.com')
(9, 'Jessica Lynch', '(499)715-7365x432', 'Kiribati', '1954-04-25', 'Further education lecturer', 'ponsrolando@gmail.com')
(10, 'Mtro. Inés Santillán', '1-940-806-7100x5576', 'Mongolia', '1989-09-11', 'Occupational psychologist', 'stephanie03@gmail.com')
(11, 'Melania Granatelli', '+34 680 838 256', 'India', '1991-05-03', 'Advertising account planner', 'eserrano@hotmail.com')
(12, 'Amanda Ariasso', '+34744 273 170', 'Isole Vergini Britanniche', '1926-06-12', 'Clinical scientist, histocompatibility and immunogenetics', 'thompsongeorge@hotmail.com')
...
(299, 'Verónica Palomo Pavón', '+39 3517 3948391', 'Bahamas', '1949-04-13', 'Product designer', 'felipeolivo@yahoo.com')

It can also be viewed with sqlitebrowser.

sqlitebrowser

Make a donation! If you liked the article, you can make a Bitcoin (BTC) donation from the digital wallet of your choice to the following address: 17MtNybhdkA9GV3UNS6BTwPcuhjXoPrSzV

Or scan the QR code from your wallet:

17MtNybhdkA9GV3UNS6BTwPcuhjXoPrSzV