Tech Info Talks

To read records from a mysql table in python we can use the below code. Please refer to my previous post for the table structure and records inserted into the table in sql-inserting-records-into-mysql-table post. To connect to mysql database from python MySQLdb library is used. You can refer the post on how to install MySQLdb here . #!/usr/bin/python import MySQLdb dbconnection = MySQLdb.connect(host = "localhost" , user = "username" , passwd = "password" , db = "pydatabase" ) cursor = dbconnection.cursor() sql = "select * from pytable" cursor.execute(sql) for record in cursor.fetchall(): print record on running the program it fetches all the records from the table pytable and loads into a cursor. With a for loop the records are printed. The output of the above program is displayed here. (1L, 'Snikers' , 'CH' , datetime.date(2016, 8, 5), 100.0) (2L, 'Galaxy'

To insert records into mysql tables use the insert into sql statement.The below insert statement add new records into the table pytable. Please refer to my previous post for table creation and table structure -  sql-creating-tables-in-mysql insert into pytable values (1, 'Snikers' , 'CH' ,NOW(),100.00); insert into pytable values (2, 'Galaxy' , 'CO' , '2015-01-01' ,200.00); insert into pytable values (3, 'Butterfinger' , 'CN' ,NOW(),150.00); If the record addition is valid it will return the message mysql> insert into pytable values (1, 'Snikers' , 'CH' ,NOW(),100.00); Query OK, 1 row affected, 1 warning (0.04 sec) mysql> insert into pytable values (2, 'Galaxy' , 'CO' , '2015-01-01' ,200.00); Query OK, 1 row affected (0.03 sec) mysql> insert into pytable values (3, 'Butterfinger' , 'CN' ,NOW(),150.00); Query OK, 1 row affected, 1 warning

We can create table in mysql with create table command. A simple SQL for creating a table named pytable is shown below. First select the database in which the table has to be created. Here an already existing database pydatabase base is used. mysql > use pydatabase; After that use the following SQL mysql> create table pytable ( pyid int not null , pyname varchar(100) not null , pycode char(2), pydate date, pynum float(8,2), primary key (pyid) ); So once the table is created we will get the message Query OK, 0 rows affected (0.08 sec) To view the structure of the table created use the describe command. mysql> describe pytable; + --------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | + --------+--------------+------+-----+---------+-------+ | pyid | int(11) | NO | PRI | NULL | | | pyname | varchar(100) | NO | | NULL | | | pycode | char(2) |

To create a database in mysql do the following steps. mysql> create database pydatabase; This will create a database named pydatabase. To list all the databases available use the below command mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | pydatabase         | +--------------------+ 4 rows in set (0.02 sec) To use a particular database instance use the command mysql> use pydatabase; Database changed This will change to database pydatabase. To display all the tables present in a database use the command mysql > show tables;