How do SQL and Python work together

Python course

Python and SQL



introduction

The history of SQL goes back to the 1970s. SQL is an abbreviation that stands for "Structured Query Language". SQL is a database language for defining data structures in relational databases as well as for processing - i.e. inserting, changing, deleting - and querying data stocks. It is a relational model based on an article by Edgar F. Codds's from 1970. SQL became the standard of the American National Standards Institute (ANSI) in 1986 and the International Organization for Standardization (ISO) in 1987.
Since most of the people who have found or searched for this article are already more or less familiar with mSQL, PostgresSQL, MySQL or other variants of SQL, we do not want to go into further detail.

A database is an organized collection of data. The data is typically organized to model aspects of reality to support processes that need that information.
The term "database" can refer to both the data itself and the database management system (DBMS). The DBMS is used for the interaction between the users and the actual database. Users do not necessarily have to be human users. Programs can also act as users of a DBMS. In this article we will learn how Python, or rather a Python program, can interact with a SQL database.

In this context, we will go into Python and the interface to SQLite and MySQL. The Python standard for database interfaces is the DB API, which is used by the Python interfaces for databases. The DB-API is designed as a general interface that can be used to address relational databases. In other words: The code used in Python to communicate with a database should be the same no matter which database or which database module is used.

SQLite

SQLite is a simple relational database system that stores its data in regular files or even in the RAM of a computer. It was designed for embedded systems such as Mozilla Firefox (bookmarks), Symbian OS or Android. SQLITE is "pretty" fast, even though it only uses a simple file for storage. SQLite can also be used for large databases.
To use SQLite, you have to import the sqlite3 module. First you have to create a connection object in order to use a database. The connection object represents the database. The connection argument - "companys.db" in the following example - functions both as the name of the file in which the data is saved and as the name of the database. If a file with this name already exists, it will be opened. Of course, this has to be a SQLite file. In the following example we open the database "company". The file does not have to exist:
>>> import sqlite3 >>> connection = sqlite3.connect ("company.db")
We have now created a database with the name "company". It behaves as if you had the command "CREATE DATABASE company;" sent to an SQL server. If you call up "sqlite3.connect ('company.db')" again, the previously written database is opened again.

After we have created a database, we naturally want to insert one or more tables. In the following we create a table "employee" for our database "company": CREATE TABLE employee (staff_number INT NOT NULL AUTO_INCREMENT, fname VARCHAR (20), lname VARCHAR (30), gender CHAR (1), joining DATE, birth_date DATE, PRIMARY KEY (staff_number)); However, this is how you would do it on the SQL command line. We want to do it directly from Python, of course. In order to be able to send a command to SQL or SQLite, we need a cursor object. Typically in SQL, a cursor is used to scroll through the records. So it is used to get the results across.
It is used more generally in SQLite (and other DB interfaces). It is used to execute all SQL commands.

We get a cursor object by calling the cursor method of connection. We can create any number of cursors. The cursor is also used to scroll through the records of the result. We have listed a complete Python program for creating the database "company" and for creating a table "employee": sql_command = "" "CREATE TABLE employee (staff_number INTEGER PRIMARY KEY, fname VARCHAR (20), lname VARCHAR (30 ), gender CHAR (1), joining DATE, birth_date DATE); "" "A note on SQL syntax: Some will have noticed that the AUTOINCREMENT field is missing in the SQLite code we use in the Python program. We defined the staff_number field as "INTEGER PRIMARY KEY". A field marked in this way automatically becomes an "autoincrement" field in SQLite3.
In other words: If a column of a table is declared as "INTEGER PRIMARY KEY", then every time someone uses NULL as input for this field, an integer number is inserted. This number is larger than the largest number previously used in this column. If the largest number so far corresponds to the value 9223372036854775807, i.e. the largest possible INT in SQLite, an unused value is selected at random.

Now we have a database with a table but there is still no data in it. To fill the table, we have to send the "INSERT" command to SQLite. For this we use the execute command again.

The following example is a fully functional program. However, you must either delete the company.db file or comment out the "DROP TABLE" command:

import sqlite3 connection = sqlite3.connect ("company.db") cursor = connection.cursor () # delete # cursor.execute ("" "DROP TABLE employee;" "") sql_command = "" "CREATE TABLE employee (staff_number INTEGER PRIMARY KEY, fname VARCHAR (20), lname VARCHAR (30), gender CHAR (1), joining DATE, birth_date DATE); "" "cursor.execute (sql_command) sql_command =" "" INSERT INTO employee (staff_number, fname, lname, gender, birth_date) VALUES (NULL, "William", "Shakespeare", "m", "1961-10-25"); "" "cursor.execute (sql_command) sql_command =" "" INSERT INTO employee (staff_number , fname, lname, gender, birth_date) VALUES (NULL, "Frank", "Schiller", "m", "1955-08-17"); "" "cursor.execute (sql_command) # never forget this, if you want the changes to be saved: connection.commit () connection.close () Of course, in most cases you will not "literally" insert the data into the SQL table. Most of the time you will have a lot of data in a data structure, e.g. a list or a dictionary, and you want to use this data as input for insert.

In the following example we assume that the database company.db and an "employee" table already exist. We use the data from the staff_data list in our INSERT statements: import sqlite3 connection = sqlite3.connect ("company.db") cursor = connection.cursor () staff_data = [("William", "Shakespeare", "m", "1961-10-25"), ("Frank", "Schiller", "m", "1955-08-17"), ("Jane", "Wall", "f", "1989-03-14 ")] for p in staff_data: format_str =" "" INSERT INTO employee (staff_number, fname, lname, gender, birth_date) VALUES (NULL, "{first}", "{last}", "{gender}", " {birthdate} ");" "" sql_command = format_str.format (first = p [0], last = p [1], gender = p [2], birthdate = p [3]) cursor.execute (sql_command)
Now we want to query our employee table:

import sqlite3 connection = sqlite3.connect ("company.db") cursor = connection.cursor () cursor.execute ("SELECT * FROM employee") print ("fetchall:") result = cursor.fetchall () for r in result : print (r) cursor.execute ("SELECT * FROM employee") print ("\ nfetch one:") res = cursor.fetchone () print (res) If we start this program, which we call "sql_company_query.py" saved, we get the following result depending on the data actually contained:

$ python3 sql_company_query.py fetchall: (1, 'William', 'Shakespeare', 'm', None, '1961-10-25') (2, 'Frank', 'Schiller', 'm', None, ' 1955-08-17 ') (3,' Bill ',' Windows ',' m ', None,' 1963-11-29 ') (4,' Esther ',' Wall ',' m ', None,' 1991-05-11 ') (5,' Jane ',' Thunder ',' f ', None,' 1989-03-14 ') fetch one: (1,' William ',' Shakespeare ',' m ', None, '1961-10-25')

MySQL

If you are working with Python2.x, the MySQLdb module can be installed, which is quite easy on Debian and Ubuntu: sudo apt-get install python-MySQLdb, however, if you are using Python3.x, install the python-mysql-connector : sudo apt-get install python3-mysql.connector Except for the import command and the parameters in the connect method, everything else remains as before, i.e. as in our SQLite example:
  • Import MySQLdb module (Python2.x) or the python-mysql-connector (Python3.x)
  • Open a connection to the SQL server
  • Send commands and receive results
  • Close the connection to the SQL server
import and connect look like this: import MySQLdb connection = MySQLdb.connect (host = "localhost", user = "testuser", passwd = "testpass", db = "company") The variant for Python3.x looks like this: import mysql.connector as mc connection = mc.connect (host = "localhost", user = "pytester", passwd = "monty", db = "company") For the following example we assume that it is the database user "pytester" there. You can create this via the command line as follows:

First open a MySQL session: mysql -u root -p On the MySQL shell we continue with: mysql> CREATE USER 'pytester' @ 'localhost' IDENTIFIED BY 'monty'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *. * TO 'pytester' @ 'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> We check the version of the MySQL server with the connection created below: import mysql.connector as mc connection = mc.connect (host = "localhost", user = "pytester" , passwd = "monty", db = "company") cursor = connection.cursor () cursor.execute ("SELECT VERSION ()") row = cursor.fetchone () print ("server version:", row [0] ) cursor.close () connection.close () The output looks something like this: server version: 5.5.52-0ubuntu0.14.04.1 As in our SQLite3 example shown at the beginning, we create a table "employee" and fill it with some Data. The program only works under Python3: import sys import mysql.connector as mc try: connection = mc.connect (host = "localhost", user = "pytester", passwd = "monty", db = "company") except mc. Error as e: print ("Error% d:% s"% (e.args [0], e.args [1])) sys.exit (1) cursor = connection.cursor () cursor.execute ("DROP TABLE IF EXISTS employee ") # delete # cursor.execute (" "" DROP TABLE employee; "" ") sql_command =" "" CREATE TABLE employee (staff_number INTEGER PRIMARY KEY, fname VARCHAR (20), lname VARCHAR (30), gender CHAR (1), joining DATE, birth_date DATE); "" "cursor.execute (sql_command) staff_data = [(" William "," Shakespeare "," m "," 1961-10-25 "), (" Frank "," Schiller "," m "," 1955-08-17 "), (" Jane "," Wall "," f "," 1989-03-14 "),] for staff, p in enumerate (staff_data ): format_str = "" "INSERT INTO employee (staff_num ber, fname, lname, gender, birth_date) VALUES ({staff_no}, '{first}', '{last}', '{gender}', '{birthdate}'); "" "sql_command = format_str.format ( staff_no = staff, first = p [0], last = p [1], gender = p [2], birthdate = p [3]) print (sql_command) cursor.execute (sql_command) connection.commit () cursor.close () connection.close () The output matches the data that were inserted into the "employee" table: INSERT INTO employee (staff_number, fname, lname, gender, birth_date) VALUES (0, 'William', 'Shakespeare' , 'm', '1961-10-25'); INSERT INTO employee (staff_number, fname, lname, gender, birth_date) VALUES (1, 'Frank', 'Schiller', 'm', '1955-08-17'); INSERT INTO employee (staff_number, fname, lname, gender, birth_date) VALUES (2, 'Jane', 'Wall', 'f', '1989-03-14'); Then we would like to query the database again: import sys import mysql.connector as mc try: connection = mc.connect (host = "localhost", user = "pytester", passwd = "monty", db = "company") except mc .Error as e: print ("Error% d:% s"% (e.args [0], e.args [1])) sys.exit (1) cursor = connection.cursor () cursor.execute (" SELECT * FROM employee ") print ('' 'Result of" SELECT * FROM employee ":' '') result = cursor.fetchall () for r in result: print (r) cursor.close () connection.close () We get the following output: Result of "SELECT * FROM employee": (0, 'William', 'Shakespeare', 'm', None, datetime.date (1961, 10, 25)) (1, 'Frank', ' Schiller ',' m ', None, datetime.date (1955, 8, 17)) (2,' Jane ',' Wall ',' f ', None, datetime.date (1989, 3, 14))