Connection implementation method and precautions of the Python database.

Connection implementation method and precautions of the Python database.

To connect to a database in Python, first install several important things:

(1) Python-dev package
(2)setuptools-0.6c11.tar.gz
(3366mysql-python-1.2.3.tar.gz

The following describes how to install these three major packages:

(1) Python-dev package
Direct yum install python-devel

(2)setuptools-0.6c11.tar.gz
Download on the internet, first decompress: tar zxf setuptools-0.6c11.tar.gz, after the cd to the decompressed folder, execute:
Python setup. py build
Python setup. py install

(3366mysql-python-1.2.3.tar.gz
As with the step of installing setuptools-0.6c11.tar.gz, unzip the tar zxfMySQL-python-1.2.3.tar.gz first,
Cd to the decompressed folder and run the following command:

Python setup. py build
Python setup. py install

Before executing the preceding command, you 'd better modify a file first. First, find the location of mysql_config and run the following command:
Find/-name mysql_config
Then we get the path to it, my is:/usr/bin/mysql_config, and then modify the MySQL-python-1.2.3 directory
To remove the # Before mysql_config = XXX, and change it:
Mysql_config =/usr/bin/mysql_config

After the above steps, we can basically install MySQLdb.

You can test it. In the Python interactive command line, enter import MySQLdb. If no error is reported, the installation is complete.

The following Python code shows how to connect to a database and perform database operations:

import MySQLdbtry:  conn = MySQLdb.connect(host='localhost',user='root',passwd='root',port=3306)  cur = conn.cursor()  cur.execute('create database if not exists PythonDB')  conn.select_db('PythonDB')  cur.execute('create table Test(id int,name varchar(20),info varchar(20))')  value = [1,'ACdreamer','student']  cur.execute('insert into Test values(%s,%s,%s)',value)  values = []  for i in range(20):    values.append((i,'Hello World!','My number is '+str(i)))  cur.executemany('insert into Test values(%s,%s,%s)',values)  cur.execute('update Test set name="ACdreamer" where id=3')  conn.commit()  cur.close()  conn.close()except MySQLdb.Error,msg:  print "MySQL Error %d: %s" %(msg.args[0],msg.args[1])

You can see that the steps for connecting to a database are roughly as follows:

(1) Establish a connection with the database system
(2) Get operation cursor
(3) Execute SQL statements and create a database (of course this step is not required because we can use an existing database)
(4) Select a database
(5) Perform Various database operations
(6) commit the transaction after the operation is completed (this step is very important because data can be written into the database only after the transaction is committed)
(7) Close Operation cursor
(8) Close the database connection

Of course, if we use an existing database, we can define it when getting the connection, for example:
Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'root', db = 'pythondb ')

If the database contains Chinese characters, we add the attribute charset = 'uft-8' or 'gb2312 'to prevent garbled characters. The charset must be consistent with the database encoding.
Conn = MySQLdb. connect (host = 'localhost', user = 'root ',
Passwd = 'root', db = 'pythondb', charset = 'utf8 ')

The following describes common functions:

Database Connection Method for transaction operations: commit () commit rollback () rollback

Cursor is used to execute commands:

Callproc (self, procname, args)
Used to execute a stored procedure. The received parameters are the stored procedure name and parameter list. The returned values are the number of affected rows.

Execute (self, query, args)
Execute a single SQL statement. The received parameters are the SQL statement itself and the list of parameters used. The returned values are the number of affected rows.

Executetasks (self, query, args)
Execute a single-pick SQL statement, but repeat the parameters in the parameter list, the returned value is the number of affected rows

Nextset (self)
Move to the next result set

Cursor is used to receive the returned value:

Fetchall (self)
Receive all returned results

Fetchmany (self, size = None)
Returns the number of returned results rows. If the value of size is greater than the number of returned results rows, the cursor. arraysize data is returned.

Fetchone (self)
Returns a result line.

Scroll (self, value, mode = 'relative ')
Move the pointer to a row. If mode = 'relative ', the value bar is moved from the current row. If mode = 'absolute ', it indicates moving the value entry from the first row of the result set.

Articles you may be interested in:

• Connect python mysqldb to the database
• Connect python Django to the MySQL database for addition, deletion, modification, and query
• How to connect python to databases such as MySQL, MongoDB, Redis, and memcache
• Example of how python uses mysqldb to connect to a database
• Example of connecting python to mysql database (add, delete, and modify operations)
• Example of using python to connect to mongodb for Data Operations (mongodb database configuration class)
• Common Database Operation modules and connection instances in python
• How to connect to the SQLite database using Python3
• Connect python to an oracle database instance
• Connect to the database through Python
• Correct posture for connecting Python to mysql database