Connecting to MySQL using Python
- Install mysql server & mysql client
sudo apt-get install mysql-server
sudo apt-get install mysql-client
- Install MySQL-python
To enable python to operate mysql requires MySQL-python package, it is python operation mysql essential modules.Download: https://pypi.python.org/pypi/MySQL-python/
Download the MySQL-python-1.2.5.zip file and extract it directly. Go to MySQL-python-1.2.5 directory:
python setup.py install
check whether the MySQLdb module can be imported normally.
- If you are not similar mysql, please read this post.
- Interaction with mysql using python
>>> conn = MySQLdb.connect(host=’localhost’,port = 3306,user=’root’, passwd=’ddos′,db =’classmate’,) # connect() method used to create a database connection, which can specify the parameters: user name, password, host and other information. This is just a connection to the database, in order to operate the database need to create a cursor.
cur = conn.cursor() #the cursor() method to create a cursor.
>>> cur.execute(“create table student(id int ,name varchar(20),class varchar(30),age varchar(10))”) #Through the cursor cur operation execute () method can write pure sql statement. Through the execute () method to write such as sql statement to operate on the data.
>>>cur.close() #Close the cursor
>>>conn.commit() #Conn.commit () method in the submission of things, in the database to insert a data must have this method, otherwise the data will not be really inserted.
>>>conn.close() #closes the database connection
- Insert data to table
>>>cur.execute(“insert into student values(‘2′,’DDOS’,’4 year 4 class’,’11′)”)To insert the new data, you must change the value of this statement. You can make the following modifications:
The executemany() method can insert multiple values at once, execute the singular sql statement, but repeat the parameters in the parameter list. The return value is the number of rows affected.
- Query data
>>>aa=cur.execute(“select * from student”)print aa
The fetchone() method can help us get the data in the table, but every time we execute cur.fetchone(), we do not have the same data. In other words, I did not execute the cursor once from the first data in the table The location of a data, so I re-executed when the second is the data.
acroll(0, ‘absolute’) method You can navigate the cursor to the first data in the table