Category: python Data Access

Python PostgreSQL – Create Database ”; Previous Next You can create a database in PostgreSQL using the CREATE DATABASE statement. You can execute this statement in PostgreSQL shell prompt by specifying the name of the database to be created after the command. Syntax Following is the syntax of the CREATE DATABASE statement. CREATE DATABASE dbname; Example Following statement creates a database named testdb in PostgreSQL. postgres=# CREATE DATABASE testdb; CREATE DATABASE You can list out the database in PostgreSQL using the l command. If you verify the list of databases, you can find the newly created database as follows − postgres=# l List of databases Name | Owner | Encoding | Collate | Ctype | ———–+———-+———-+—————————-+————-+ mydb | postgres | UTF8 | English_United States.1252 | ……….. | postgres | postgres | UTF8 | English_United States.1252 | ……….. | template0 | postgres | UTF8 | English_United States.1252 | ……….. | template1 | postgres | UTF8 | English_United States.1252 | ……….. | testdb | postgres | UTF8 | English_United States.1252 | ……….. | (5 rows) You can also create a database in PostgreSQL from command prompt using the command createdb, a wrapper around the SQL statement CREATE DATABASE. C:Program FilesPostgreSQL11bin> createdb -h localhost -p 5432 -U postgres sampledb Password: Creating a database using python The cursor class of psycopg2 provides various methods execute various PostgreSQL commands, fetch records and copy data. You can create a cursor object using the cursor() method of the Connection class. The execute() method of this class accepts a PostgreSQL query as a parameter and executes it. Therefore, to create a database in PostgreSQL, execute the CREATE DATABASE query using this method. Example Following python example creates a database named mydb in PostgreSQL database. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”postgres”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Preparing query to create a database sql = ”””CREATE database mydb”””; #Creating a database cursor.execute(sql) print(“Database created successfully……..”) #Closing the connection conn.close() Output Database created successfully…….. Print Page Previous Next Advertisements ”;

Python MySQL – Update Table ”; Previous Next UPDATE Operation on any database updates one or more records, which are already available in the database. You can update the values of existing records in MySQL using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it. Syntax Following is the syntax of the UPDATE statement in MySQL − UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition]; You can combine N number of conditions using the AND or the OR operators. Example Assume we have created a table in MySQL with name EMPLOYEES as − mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT ); Query OK, 0 rows affected (0.36 sec) And if we have inserted 4 records in to it using INSERT statements as − mysql> INSERT INTO EMPLOYEE VALUES (”Krishna”, ”Sharma”, 19, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 25, ”F”, 5000), (”Mac”, ”Mohan”, 26, ”M”, 2000); Following MySQL statement increases the age of all male employees by one year − mysql> UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ”M”; Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0 If you retrieve the contents of the table, you can see the updated values as − mysql> select * from EMPLOYEE; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Krishna | Sharma | 20 | M | 2000 | | Raj | Kandukuri | 21 | M | 7000 | | Ramya | Ramapriya | 25 | F | 5000 | | Mac | Mohan | 27 | M | 2000 | +————+———–+——+——+——–+ 4 rows in set (0.00 sec) Updating the contents of a table using Python To update the records in a table in MySQL using python − import mysql.connector package. Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Create a cursor object by invoking the cursor() method on the connection object created above. Then, execute the UPDATE statement by passing it as a parameter to the execute() method. Example The following example increases age of all the males by one year. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Preparing the query to update the records sql = ”””UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ”M” ””” try: # Execute the SQL command cursor.execute(sql) # Commit your changes in the database conn.commit() except: # Rollback in case there is any error conn.rollback() #Retrieving data sql = ”””SELECT * from EMPLOYEE””” #Executing the query cursor.execute(sql) #Displaying the result print(cursor.fetchall()) #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 22, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 23, ”M”, 7000.0), (”Ramya”, ”Ramapriya”, 26, ”F”, 5000.0) ] Print Page Previous Next Advertisements ”;

Python MySQL – Drop Table ”; Previous Next You can remove an entire table using the DROP TABLE statement. You just need to specify the name of the table you need to delete. Syntax Following is the syntax of the DROP TABLE statement in MySQL − DROP TABLE table_name; Example Before deleting a table get the list of tables using the SHOW TABLES statement as follows − mysql> SHOW TABLES; +—————–+ | Tables_in_mydb | +—————–+ | contact | | cricketers_data | | employee | | sample | | tutorials | +—————–+ 5 rows in set (0.00 sec) Following statement removes the table named sample from the database completely − mysql> DROP TABLE sample; Query OK, 0 rows affected (0.29 sec) Since we have deleted the table named sample from MySQL, if you get the list of tables again you will not find the table name sample in it. mysql> SHOW TABLES; +—————–+ | Tables_in_mydb | +—————–+ | contact | | cricketers_data | | employee | | tutorials | +—————–+ 4 rows in set (0.00 sec) Removing a table using python You can drop a table whenever you need to, using the DROP statement of MYSQL, but you need to be very careful while deleting any existing table because the data lost will not be recovered after deleting a table. To drop a table from a MYSQL database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it. Example Following table drops a table named EMPLOYEE from the database. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving the list of tables print(“List of tables in the database: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Doping EMPLOYEE table if already exists cursor.execute (“DROP TABLE EMPLOYEE”) print(“Table dropped… “) #Retrieving the list of tables print( “List of tables after dropping the EMPLOYEE table: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Closing the connection conn.close() Output List of tables in the database: [(”employee”,), (”employeedata”,), (”sample”,), (”tutorials”,)] Table dropped… List of tables after dropping the EMPLOYEE table: [(”employeedata”,), (”sample”,), (”tutorials”,)] Drop table only if exists If you try to drop a table which does not exist in the database, an error occurs as − mysql.connector.errors.ProgrammingError: 1051 (42S02): Unknown table ”mydb.employee” You can prevent this error by verifying whether the table exists before deleting, by adding the IF EXISTS to the DELETE statement. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving the list of tables print(“List of tables in the database: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Doping EMPLOYEE table if already exists cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) print(“Table dropped… “) #Retrieving the list of tables print(“List of tables after dropping the EMPLOYEE table: “) cursor.execute(“SHOW Tables”) print(cursor.fetchall()) #Closing the connection conn.close() Output List of tables in the database: [(”employeedata”,), (”sample”,), (”tutorials”,)] Table dropped… List of tables after dropping the EMPLOYEE table: [(”employeedata”,), (”sample”,), (”tutorials”,)] Print Page Previous Next Advertisements ”;

Python MySQL – Introduction ”; Previous Next The Python standard for database interfaces is the Python DB-API. Most Python database interfaces adhere to this standard. You can choose the right database for your application. Python Database API supports a wide range of database servers such as − GadFly mSQL MySQL PostgreSQL Microsoft SQL Server 2000 Informix Interbase Oracle Sybase Here is the list of available Python database interfaces: Python Database Interfaces and APIs. You must download a separate DB API module for each database you need to access. For example, if you need to access an Oracle database as well as a MySQL database, you must download both the Oracle and the MySQL database modules. What is mysql-connector-python? MySQL Python/Connector is an interface for connecting to a MySQL database server from Python. It implements the Python Database API and is built on top of the MySQL. How do I Install mysql-connector-python? First of all, you need to make sure you have already installed python in your machine. To do so, open command prompt and type python in it and press Enter. If python is already installed in your system, this command will display its version as shown below − C:UsersTutorialspoint>python Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 64 bit (AMD64)] on win32 Type “help”, “copyright”, “credits” or “license” for more information. >>> Now press ctrl+z and then Enter to get out of the python shell and create a folder (in which you intended to install Python-MySQL connector) named Python_MySQL as − >>> ^Z C:UsersTutorialspoint>d: D:>mkdir Python_MySQL Verify PIP PIP is a package manager in python using which you can install various modules/packages in Python. Therefore, to install Mysql-python mysql-connector-python you need to make sure that you have PIP installed in your computer and have its location added to path. You can do so, by executing the pip command. If you didn’t have PIP in your system or, if you haven’t added its location in the Path environment variable, you will get an error message as − D:Python_MySQL>pip ”pip” is not recognized as an internal or external command, operable program or batch file. To install PIP, download the get-pip.py to the above created folder and, from command navigate it and install pip as follows − D:>cd Python_MySQL D:Python_MySQL>python get-pip.py Collecting pip Downloading https://files.pythonhosted.org/packages/8d/07/f7d7ced2f97ca3098c16565efbe6b15fafcba53e8d9bdb431e09140514b0/pip-19.2.2-py2.py3-none-any.whl (1.4MB) |████████████████████████████████| 1.4MB 1.3MB/s Collecting wheel Downloading https://files.pythonhosted.org/packages/00/83/b4a77d044e78ad1a45610eb88f745be2fd2c6d658f9798a15e384b7d57c9/wheel-0.33.6-py2.py3-none-any.whl Installing collected packages: pip, wheel Consider adding this directory to PATH or, if you prefer to suppress this warning, use –no-warn-script-location. Successfully installed pip-19.2.2 wheel-0.33.6 Installing mysql-connector-python Once you have Python and PIP installed, open command prompt and upgrade pip (optional) as shown below − C:UsersTutorialspoint>python -m pip install –upgrade pip Collecting pip Using cached https://files.pythonhosted.org/packages/8d/07/f7d7ced2f97ca3098c16565efbe6b15fafcba53e8d9bdb431e09140514b0/pip-19.2.2-py2.py3-none-any.whl Python Data Access 4 Installing collected packages: pip Found existing installation: pip 19.0.3 Uninstalling pip-19.0.3: Successfully uninstalled pip-19.0.3 Successfully installed pip-19.2.2 Then open command prompt in admin mode and install python MySQL connect as − C:WINDOWSsystem32>pip install mysql-connector-python Collecting mysql-connector-python Using cached https://files.pythonhosted.org/packages/99/74/f41182e6b7aadc62b038b6939dce784b7f9ec4f89e2ae14f9ba8190dc9ab/mysql_connector_python-8.0.17-py2.py3-none-any.whl Collecting protobuf>=3.0.0 (from mysql-connector-python) Using cached https://files.pythonhosted.org/packages/09/0e/614766ea191e649216b87d331a4179338c623e08c0cca291bcf8638730ce/protobuf-3.9.1-cp37-cp37m-win32.whl Collecting six>=1.9 (from protobuf>=3.0.0->mysql-connector-python) Using cached https://files.pythonhosted.org/packages/73/fb/00a976f728d0d1fecfe898238ce23f502a721c0ac0ecfedb80e0d88c64e9/six-1.12.0-py2.py3-none-any.whl Requirement already satisfied: setuptools in c:program files (x86)python37-32libsite-packages (from protobuf>=3.0.0->mysql-connector-python) (40.8.0) Installing collected packages: six, protobuf, mysql-connector-python Successfully installed mysql-connector-python-8.0.17 protobuf-3.9.1 six-1.12.0 Verification To verify the installation of the create a sample python script with the following line in it. import mysql.connector If the installation is successful, when you execute it, you should not get any errors − D:Python_MySQL>python test.py D:Python_MySQL> Installing python from scratch Simply, if you need to install Python from scratch. Visit the Python Home Page. Click on the Downloads button, you will be redirected to the downloads page which provides links for latest version of python for various platforms choose one and download it. For instance, we have downloaded python-3.7.4.exe (for windows). Start the installation process by double-clicking the downloaded .exe file. Check the Add Python 3.7 to Path option and proceed with the installation. After completion of this process, python will be installed in your system. Print Page Previous Next Advertisements ”;

Python MySQL – Insert Data ”; Previous Next You can add new rows to an existing table of MySQL using the INSERT INTO statement. In this, you need to specify the name of the table, column names, and values (in the same order as column names). Syntax Following is the syntax of the INSERT INTO statement of MySQL. INSERT INTO TABLE_NAME (column1, column2,column3,…columnN) VALUES (value1, value2, value3,…valueN); Example Following query inserts a record into the table named EMPLOYEE. INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (” Mac”, ”Mohan”, 20, ”M”, 2000 ); You can verify the records of the table after insert operation using the SELECT statement as − mysql> select * from Employee; +————+———–+——+——+——–+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | +————+———–+——+——+——–+ | Mac | Mohan | 20 | M | 2000 | +————+———–+——+——+——–+ 1 row in set (0.00 sec) It is not mandatory to specify the names of the columns always, if you pass values of a record in the same order of the columns of the table you can execute the SELECT statement without the column names as follows − INSERT INTO EMPLOYEE VALUES (”Mac”, ”Mohan”, 20, ”M”, 2000); Inserting data in MySQL table using python The execute() method (invoked on the cursor object) accepts a query as parameter and executes the given query. To insert data, you need to pass the MySQL INSERT statement as a parameter to it. cursor.execute(“””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Mac”, ”Mohan”, 20, ”M”, 2000)”””) To insert data into a table in MySQL using python − import mysql.connector package. Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Create a cursor object by invoking the cursor() method on the connection object created above Then, execute the INSERT statement by passing it as a parameter to the execute() method. Example The following example executes SQL INSERT statement to insert a record into the EMPLOYEE table − import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() # Preparing SQL query to INSERT a record into the database. sql = “””INSERT INTO EMPLOYEE( FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Mac”, ”Mohan”, 20, ”M”, 2000)””” try: # Executing the SQL command cursor.execute(sql) # Commit your changes in the database conn.commit() except: # Rolling back in case of error conn.rollback() # Closing the connection conn.close() Inserting values dynamically You can also use “%s” instead of values in the INSERT query of MySQL and pass values to them as lists as shown below − cursor.execute(“””INSERT INTO EMPLOYEE VALUES (”Mac”, ”Mohan”, 20, ”M”, 2000)”””, (”Ramya”, ”Ramapriya”, 25, ”F”, 5000)) Example Following example inserts a record into the Employee table dynamically. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() # Preparing SQL query to INSERT a record into the database. insert_stmt = ( “INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)” “VALUES (%s, %s, %s, %s, %s)” ) data = (”Ramya”, ”Ramapriya”, 25, ”F”, 5000) try: # Executing the SQL command cursor.execute(insert_stmt, data) # Commit your changes in the database conn.commit() except: # Rolling back in case of error conn.rollback() print(“Data inserted”) # Closing the connection conn.close() Output Data inserted Print Page Previous Next Advertisements ”;

Python MySQL – Database Connection ”; Previous Next To connect with MySQL, (one way is to) open the MySQL command prompt in your system as shown below − It asks for password here; you need to type the password you have set to the default user (root) at the time of installation. Then a connection is established with MySQL displaying the following message − Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.12-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ”help;” or ”h” for help. Type ”c” to clear the current input statement. You can disconnect from the MySQL database any time using the exit command at mysql> prompt. mysql> exit Bye Establishing connection with MySQL using python Before establishing connection to MySQL database using python, assume − That we have created a database with name mydb. We have created a table EMPLOYEE with columns FIRST_NAME, LAST_NAME, AGE, SEX and INCOME. The credentials we are using to connect with MySQL are username: root, password: password. You can establish a connection using the connect() constructor. This accepts username, password, host and, name of the database you need to connect with (optional) and, returns an object of the MySQLConnection class. Example Following is the example of connecting with MySQL database “mydb”. import mysql.connector #establishing the connection conn = mysql.connector.connect(user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Executing an MYSQL function using the execute() method cursor.execute(“SELECT DATABASE()”) # Fetch a single row using fetchone() method. data = cursor.fetchone() print(“Connection established to: “,data) #Closing the connection conn.close() Output On executing, this script produces the following output − D:Python_MySQL>python EstablishCon.py Connection established to: (”mydb”,) You can also establish connection to MySQL by passing credentials (user name, password, hostname, and database name) to connection.MySQLConnection() as shown below − from mysql.connector import (connection) #establishing the connection conn = connection.MySQLConnection(user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb”) #Closing the connection conn.close() Print Page Previous Next Advertisements ”;