getting Started
Connect to the database
CallconnectMethod and pass inODBCConnect a string, which returns aconnectObject. passconnectObject, callcursor()method,You can get a cursorcursor. The following code example:
import pyodbc
#Connection example: Windowssystem, NoDSNWay, Using Microsoft SQL Server Database Driver
cnxn =('DRIVER={SQL Server};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass')
#Connection example: Linuxsystem, NoDSNWay, useFreeTDSdrive
cnxn =('DRIVER={FreeTDS};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass;TDS_Version=7.0')
#Connection example:useDSNWay
cnxn = ('DSN=test;PWD=password')
# Open the cursor
cursor =()
The above examples are just standard examples, specificODBCThe connection string is subject to the driver you use.
Query some data
allSQLAll statements are used()Method execution. for exampleselectThe statement will return some result rows, you can use the cursor (Cursor) related function functions (fetchone，fetchall，fetchmany) Search the results.
Used to return a single line ( Row) object:
("selectuser_id, user_name from users")
row =()
if row:
print(row)
Row The object is similar to onepythonTuple(tuples), but can also be accessed by column names, e.g.:
("selectuser_id, user_name from users")
row =()
print('name:',row[1]) # Use column index numbers to access data
print('name:',row.user_name) # Or use column names to access data directly
When all rows have been retrieved, fetchoneReturn to None.
while 1:
row = ()
if not row:
break
print('id:', row.user_id)
The method returns all remaining rows and stores them in a list. If there are no rows, an empty list is returned.(Note: If there are many rows, it will cause a lot of memory usage.FetchallAll data will be queried to the local area at one time and then traversed again)
("selectuser_id, user_name from users")
rows = ()
for row in rows:
print(row.user_id, row.user_name)
If you don't care about the data processing time, you can use the cursor itself as an iterator and iterate line by line. This can save a lot of memory overhead, but the speed will be relatively slow due to communicating back and forth with data:
("selectuser_id, user_name from users"):
for row in cursor:
print(row.user_id, row.user_name)
becauseAlways return the cursor (cursor）, So it can also be abbreviated:
for row ("select user_id, user_name from users"):
print(row.user_id, row.user_name)
We canexecutUsed in”””Triple quotes to apply multiple linesSQLString. sosqlThe readability is greatly enhanced. This ispythonFeatures:
(
"""
select user_id, user_name
from users
where last_logon < '2001-01-01'
and bill_overdue = 1
""")
SQLparameter
ODBCSupport the use of question marks asSQLquery parameter placeholder. Can beexecuteMethodSQLAfter the parameters, provideSQLValue of parameter placeholder:
(
"""
select user_id, user_name
from users
where last_logon < ?
and bill_overdue = ?
""", '2001-01-01', 1)
Doing so can preventSQLInject attacks to improve security. If you use different parameters to execute the sameSQLIt will be more efficient, in which caseSQLWill only preinstall (prepared )once.（pyodbcOnly the last written statement is kept, so if the program switches between statements, it will be preinstalled each time, resulting in multiple preinstallations. )
PythonofDB APISpecify parameters should be sequenced (sequence) object passing, sopyodbcThis method is also supported:
(
"""
select user_id, user_name
from users
where last_logon < ?
and bill_overdue = ?
""", ['2001-01-01', 1])
Insert data
Insert data using the same function - By passinginsert SQLExecute insertion data with related placeholder parameters:
("insertinto products(id, name) values ('pyodbc', 'awesome library')")
()
("insertinto products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
()
Note: Call(). If it is sent as an error, it can be rolled back. The specific needs to depend on the database feature support status. If the data changes, it is best to do itcommit. If not committed, all data will be rolled back when the connection is interrupted.
Update and delete data
Updates and deletes work in the same way: through specificSQLto execute. Usually we want to know how many records are affected when updating and deleting, and we can use itTo get the value:
("deletefrom products where id <> ?", 'pyodbc')
print('Deleted {}inferior products'.format())
()
becauseexecute Always return cursors (allowing you to call chains or iterators to use), sometimes we simply abbreviate them like this:
deleted =("delete from products where id <> 'pyodbc'").rowcount
()
Be sure to call itcommit. Otherwise, the change will be rolled back when the connection is interrupted.
Tips and tips
quotation marks
In single quotesSQLIt is valid. When the value requires single quotes, use a double quote.SQL:
("deletefrom products where id <> 'pyodbc'")
If using triple quotation marks, We can use single quotes like this:
(
"""
delete
from products
where id <> 'pyodbc'
""")
Column name
Microsoft SQLServerSome databases like this do not produce column names for computed columns, in which case the column needs to be accessed through the index. We can also usesqlThe column alias method, specify a reference name for the calculated column:
row =("select count(*) as user_count fromusers").fetchone()
print('{}users'.format(row.user_count)
Of course, you can also directly use column index to access column values:
count =("select count(*) from users").fetchone()[0]
print('{}users'.format(count)
Note that the first column in the above example cannot beNull. otherwisefetchoneThe method will returnNoneAnd will reportNoneTypeAn error that does not support indexing. If there is a default value, it can often beISNULLOr merge:
maxid =("select coalesce(max(id), 0) fromusers").fetchone()[0]
Automatic cleaning
Connect (default) in a transaction. If a connection is not committed before closing, the current transaction rollback will occur. Very rarely neededfinallyorexcept Statements to perform artificial cleaning operations, and the program will automatically clean up.
For example, if any of the following executions are performedSQLAn exception occurs in a statement, which will cause the execution of these two cursors to be invalid. This ensures atomicity, either all data is inserted or all data is not inserted. No need to write clean code manually.
cnxn =(...)
cursor = ()
("insertinto t(col) values (1)")
("insertinto t(col) values (2)")
()