Problems related to Mysql operations and Chinese characters using Python

Source: Internet
Author: User
Using Python to operate Mysql and Chinese www. iteye. the main programming language of comtopic573092 is Java, and Mysql is also used during development. For testing and debugging purposes, you need to operate the database, such as backup, insert test data, and modify test data, in some cases, you cannot simply use SQL to complete tasks or perform tasks well.

Using Python to operate Mysql and Chinese http://www.iteye.com/topic/573092 at ordinary times the main programming language is Java, the development is also mainly used Mysql, often for testing, debugging purposes need to operate the database, such as backup, insert test data and modify test data. In some cases, you cannot simply use SQL to complete tasks or perform tasks well.

Using Python for Mysql and Chinese problems
Http://www.iteye.com/topic/573092
The main programming language is Java, and Mysql is also used during development. for testing purposes, you often need to operate databases, such as backup, insert test data, and modify test data, in some cases, you cannot simply use SQL to complete the task or complete the task well. It is a little too troublesome to write in Java, so you can think of Python. The Python syntax is concise and does not need to be compiled. You can complete the task well. Today, I checked Python's operations on Mysql and made a record.

First of all, the environment required for installation is not mentioned in Mysql and Python.
The main installation of MySQLdb, you can go to sf.net download, the specific address is the http://sourceforge.net/projects/mysql-python/
If Ubuntu is used

Ubuntu: sudo apt-get install python-mysqldb
Fedora19: sudo yum-y install MySQL-python

After the installation is complete, you can
Import MySQLdb # case sensitive !!

If no error is reported, the installation is successful and may continue.

MySQLdb is equivalent to the JDBC Driver of MySQL in JAVA in Python. Python also has similar data interface specifications for Python db api. MySQLdb is the implementation of Mysql. The operation is also relatively simple, just like operating databases on other platforms or languages, that is, establishing a connection with the database system, then inputting SQL to the database, and then obtaining results from the database.
First, write a simple,
Create a database:
#! /Usr/bin/env python # coding = UTF-8 ############################# #######@ author migle # @ date 2010-01-17 ########################## ######### MySQLdb example ############################# ###### import MySQLdb # establish a connection with the database system conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ') # obtain the operation cursor = conn. cursor () # Execute SQL to create a database. cursor.exe cute ("" create database python ") # Close the connection and release the resource cursor. close ();



Create databases, create tables, insert data, and insert multiple data records
#! /Usr/bin/env python # coding = UTF-8 ############################# #######@ author migle # @ date 2010-01-17 ########################## ######### MySQLdb example ############################# ###### import MySQLdb # establish a connection with the database system conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ') # obtain the operation cursor = conn. cursor () # Execute SQL to create a database. cursor.exe cute ("" create database if not exists python """) # Select database conn. select_db ('python'); # Execute SQL to create a data table. cursor.exe cute ("" create table test (id int, info varchar (100) ") value = [1," inserted? "]; # Insert a record cursor.exe cute (" insert into test values (% s, % s) ", value ); values = [] # generate the insert parameter value for I in range (20): values. append (I, 'Hello mysqldb, I am recoder '+ str (I) # insert multiple records cursor.exe cute.pdf ("" insert into test values (% s, % s) ", values); # Close the connection and release the resource cursor. close ();



The query process is similar to the insert process, but there is only one more step to get the query result.
#! /Usr/bin/env python # coding = UTF-8 ############################# ###########@ author migle # @ date 2010-01-17 ###################### ################### MySQLdb query ################### #################### import MySQLdb conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'longforfreedom ', db = 'python') cursor = conn. cursor () count = cursor.exe cute ('select * from test') print 'Total % s records', count # Get a record, print is returned for each record as a tuples. "Only one record is obtained:" result = cursor. fetchone (); print result # print 'id: % s info: % s' % (result [0], result [1]) print 'id: % s info: % s '% result # Get 5 Records. Note that the cursor has pointed to the second record because fetchone () has been executed, that is, print all records starting from the second record "gets only five records:" results = cursor. fetchiterator (5) for r in results: print r print "get all results:" # reset the cursor position. 0 indicates the offset. mode = absolute | relative. The default value is relative and cursor. scroll (0, mode = 'absolute ') # obtain all results = cursor. fetchall () for r in results: print r conn. close ()



Chinese questions:
#! /Usr/bin/python # coding = gbk # to set this parameter, if any # Note: the database is in the utf-8_bin format import sysimport requestsimport reimport MySQLdbfrom BeautifulSoup import BeautifulSoupurl = "http://sh.house.163.com/13/0929/09/99U877FR00073SDJ.html" req = requests. get (url) # print req. contentbp = BeautifulSoup (req. content) title = bp. findAll (id = re. compile ("h1title") endText = bp. findAll (id = re. compile ("endText"); # Read it in UTF-8 format # charset = "utf8" here is GBK And conn = MySQLdb. connect (host = "192.168.0.196", user = "root", passwd = ", db =" python ", charset =" utf8 ") cursor = conn. cursor () # encode the iso-8895-1 from the page into GBKvalues = [title [0]. text. encode ("iso-8859-1 "). decode ("GBK"), endText [0]. text. encode ("iso-8859-1 "). decode ("GBK")]; # values = [" ". decode ("gbk "). encode ("UTF-8"), "good guy ". decode ("gbk "). encode ("UTF-8" cannot parse cursor.exe cute ("insert into test (title, text) value (% s, % s)", values) cursor. close () conn. close ()
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.