◀ Blog
Sample code of pyodbc to connect with Microsoft SQL Server Database.
SQL Server Management:
https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15
SQL
# conda env py36db
# !pip install pyodbc
import pyodbc
import pandas as pd
# help(pyodbc)
# Check drivers
for driver in pyodbc.drivers():
print(driver)
# SQL Server Authentication
server = 'DESKTOP-1GHH8VU\JSQL'
database = 'testdb'
username = 'sa'
password = 'mssqlP@ssw0rd'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
'SERVER='+server+';'
'DATABASE='+database+';'
'UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
# Window Authentication
server = 'DESKTOP-1GHH8VU\JSQL'
database = 'testdb'
cnxn = pyodbc.connect('Driver={SQL Server};'
'SERVER='+server+';'
'DATABASE='+database+';'
'Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
print(row[0])
row = cursor.fetchone()
# SQL: 'EXEC sp_databases'
cursor = cnxn.cursor()
cursor.execute("SELECT name from sys.databases")
rows = cursor.fetchall()
for row in rows:
sql = ("select type_desc, physical_name from %s.sys.database_files" % row.name)
cursor.execute(sql)
l1rows = cursor.fetchall()
print("Database: " + row.name)
for l1row in l1rows:
print(l1row.type_desc + " " + l1row.physical_name)
cursor.execute('''
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='customer' and xtype='U')
CREATE TABLE customer
(
Name nvarchar(50),
Age int,
City nvarchar(50),
Date date
)
''')
cnxn.commit()
# cursor.execute("DROP TABLE customer")
columns:
Schema name
# cursor = cnxn.cursor()
# for row in cursor.tables():
# print(row)
table_info = cursor.tables()
table_info = table_info.fetchall()
df = pd.DataFrame(table_info, columns=['table_info'])
# Split column
df = df['table_info'].astype(str).str.split(expand=True)
# All column
df.columns =['table_cat','table_schem','table_name','table_type','memo']
# Replace symbol
df = df.replace("'", "", regex=True)
df = df.replace("\(", "", regex=True)
df = df.replace("\)", "", regex=True)
df = df.replace(",", "", regex=True)
# Filter only dbo
df = df[df['table_schem'].astype(str).str.contains('dbo', na=False)]
df
Columns:
meta = cursor.columns(table='customer')
for row in meta:
print(row)
meta = cursor.columns(table='customer')
for row in meta:
print(row.column_name, row.type_name)
cursor.execute('''
INSERT INTO testdb.dbo.customer (Name, Age, City, Date)
VALUES
('Sakura',20,'Tokyo', '2020-01-01'),
('Neo',30,'London', '2020-04-15'),
('Patrick',40,'', '2019-10-21')
''')
print(cursor.rowcount, 'records inserted')
cnxn.commit()
cursor.execute("INSERT INTO customer (Name, Age, City, Date) values (?, ?, ?, ?)",
'Tom', '50','California', '2020-02-03')
print(cursor.rowcount, 'records inserted')
cnxn.commit()
cursor.execute('''
UPDATE TestDB.dbo.customer
SET Age = 50, City = 'Bangkok'
WHERE Name = 'Patrick'
''')
print(cursor.rowcount, 'records updated')
cnxn.commit()
cursor.execute('''
DELETE FROM TestDB.dbo.customer
WHERE Name = 'Tom'
''')
print(cursor.rowcount, 'records deleted')
cnxn.commit()
# SELECT ALL
cursor.execute("SELECT * FROM customer")
rows = cursor.fetchall()
for row in rows:
print(row, end='\n')
# WHERE text
cursor.execute("SELECT * FROM customer WHERE Name='Patrick'")
rows = cursor.fetchall()
for row in rows:
print(row, end='\n')
# WHERE int
cursor.execute("SELECT * FROM customer WHERE Age > 30")
rows = cursor.fetchall()
for row in rows:
print(row, end='\n')
# WHERE date
cursor.execute("SELECT * FROM customer WHERE Date > '2019-12-31'")
rows = cursor.fetchall()
for row in rows:
print(row, end='\n')
import pandas as pd
query = "SELECT * FROM testdb.dbo.customer"
df = pd.read_sql(query, cnxn)
df
# Insert Dataframe into SQL Server:
totalrow = 0
for index, row in df.iterrows():
cursor.execute("INSERT INTO dbo.customer (Name,Age,City,Date) values(?,?,?,?)",
row.Name, row.Age, row.City, row.Date)
totalrow +=cursor.rowcount
print(totalrow, 'records inserted')
cnxn.commit()
import pyodbc
def dblogin():
server = 'DESKTOP-1GHH8VU\JSQL'
database = 'testdb'
username = 'sa'
password = 'mssqlP@ssw0rd'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
'SERVER='+server+';'
'DATABASE='+database+';'
'UID='+username+';PWD='+ password)
return cnxn.cursor()
def select_execute(con, slq):
cursor.execute(slq)
rows = cursor.fetchall()
return rows
if __name__ == '__main__':
cursor = dblogin()
sql = '''SELECT *
FROM customer'''
rows = select_execute(cursor, sql)
for row in rows:
print(row, end='\n')
cursor.close()
cnxn.close()