Python
Python Database connection
Retrieve data from a table:
import pyodbc
conn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=SQLPythonBasics;Trusted_Connection=yes;’)
cursor = conn.cursor()
cursor.execute(‘Select * from Cities’)
rows=cursor.fetchall()
print(rows)
#print(*rows,sep=”\n”)
#Retrieve data from a table if rows exist
#if rows: print(rows)
cursor.close()
conn.close()
Retrieve data from a table using for loop:
import pyodbc
conn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=SQLPythonBasics;Trusted_Connection=yes;’)
cursor = conn.cursor()
cursor.execute(‘Select * from Cities’)
for row in cursor.fetchall():
print(row)
cursor.close()
conn.close()
Retrieve limited rows from a table:
import pyodbc
conn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=SQLPythonBasics;Trusted_Connection=yes;’)
cursor = conn.cursor()
cursor.execute(‘Select * from Marks’)
for row in cursor.fetchmany(2):
print (row)
cursor.close()
conn.close()
Insert data to a table:
import pyodbc
conn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=SQLPythonBasics;Trusted_Connection=yes;’)
cursor = conn.cursor()
city = “Washington”
str = ”’Insert into Cities Values(?)”’
cursor.execute(str,city)
conn.commit()
cursor.close()
conn.close()
Delete data from a table:
import pyodbc
conn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=SQLPythonBasics;Trusted_Connection=yes;’)
cursor = conn.cursor()
city = “Washington”
str = ”’Delete FROM Cities Where Cityname=?”’
cursor.execute(str,city)
conn.commit()
cursor.close()
conn.close()
Retrieve multiple columns from a table:
import pyodbc
conn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=SQLPythonBasics;Trusted_Connection=yes;’)
cursor = conn.cursor()
cursor.execute(‘Select * from Marks’)
rows=cursor.fetchall()
for row in rows:
print(“Student: “,row[0])
print(“Subject: “,row[1])
print(“Marks: “,row[2])
print(“\n”)
#print(len(rows))
cursor.close()
conn.close()