Python database programming,

Source: Internet
Author: User

Python database programming,
Outline

Basic Database Operations

Function Attributes & object Methods

Connect to database

 

 

Basic Database Operations

Create a database:

Create database test;

Grant all on test. * to user (s );

The first line creates a database named "test". Assume that you are the database administrator. The second line of statements can be used to grant permissions to specified users (or all users ).

Use Database

USE test;

If you have logged on to a database system but have not selected the database you want to use, this simple statement allows you to specify a database to perform database operations.

Delete Database

Drop database test;

This simple statement can remove all tables and data from the database and delete it from the system.

Create a table

Create table users (login VARCHAR (8), userid INT, projid INT );

This statement creates a new table that contains the string column login and two integer columns: userid and projid.

Delete table

Drop table users;

This simple statement can delete a table in the database and clear all the data.

Insert row

Insert into users VALUES ('leanna ', 2111,1 );

You can use the INSERT statement to INSERT a new row into the database. You must specify the table name and the values of each column. In this example, the string "leanna" corresponds to the login parameter, and 2111 and 1 correspond to the userid and projid respectively.

Update row

UPDATE users SET projid = 4 WHERE projid = 2;

UPDATE users SET projid = 1 WHERE userid = 311;

To modify the existing rows in the table, use the UPDATE statement. Use SET to determine the columns to be modified and provide conditions to determine the modified rows. In the first example, all users whose "projid" is 2 must be changed to 4. In the second example, the specified user (here the user with userid 311) is moved to the project team numbered #1.

Delete row

Delete from users WHERE projid = % d;

Delete from users;

To DELETE rows in a table, use the delete from command to specify the table name and optional conditions of the row to be deleted. Without this condition, all rows will be deleted, as in the second example.

The above are some basic operations of the database, as shown in the following figure:

Create and drop databases are the same for creating and deleting tables.

Modifying an existing row in a table is called update and adding a new row to the database is called insert)

The existing row in the removal table is called delete ).

These actions are generally called database commands or operations. The optional condition is used to request rows in the database ).

 

Function Attributes & object Methods

Connect () function attributes

User Username password host name database name MySQLdb database uses the dbdsn data source name connect () function to access the database through the Connection object. The compatible module must implement the connect () function, which is created and returns a Connection object.

Database connection is required for communication between applications and databases. It is the most basic mechanism. commands can be passed to the server and the returned results can be obtained only through database connection. After a connection (or connection pool) is established, you can create a cursor, send a request to the database, and then receive a response from the database.

 

Connection object Method
The Connection object does not need to contain any data attributes.

Close () close database connection commit () commit current transaction rollback () Cancel current transaction cursor () use this connection to create (and return) a cursor or class cursor object errorhandler (cxn, cur, errcls, errval) as the processing program for the cursor of the given connection

 

When close () is used, this connection cannot be used any more, otherwise it will enter the exception handling process.

If the database does not support transaction processing or the automatic commit function is enabled, the commit () method cannot be used. If you want to, you can use a separate method to enable or disable the automatic submission function.

For the database to be processed, you only need to implement "pass" in the method.

Similar to commit (), the rollback () method is only useful in databases that support transaction processing. When an exception occurs, rollback () restores the database to the beginning of transaction processing.

If RDBMS does not support cursor (), cursor () returns an object that imitates the actual cursor as much as possible. This is the most basic requirement. Each adapter developer can add special attributes for his interface or database.

When you complete the database connection and close the cursor, You need to execute commit () for all operations and close () for your connection ().

 

Cursor object

 

After the connection is established, you can communicate with the database. The cursor allows you to submit database commands and obtain query results.

After the cursor is created, You can execute a query or command (or multiple queries and commands) and retrieve one or more rows of results from the result set.

The most important attributes of a cursor object are the execute * () and fetch * () methods. All service requests to the database are executed through them. The arraysize data attribute is useful when the default size is set for fetchmany. Of course, it is a good idea to close the cursor when you don't need it. If your database supports stored procedures, callproc () may be used ().

 

Connect to database

 

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.