DATABASE_CONNECTIONS/PythonConnections.py at master · ParfaitG/DATABASE_CONNECTIONS · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
#!/usr/bin/python
import sqlite3
import pypyodbc
import pymysql


# LIST OF INSTALLED DATA SOURCES (DSNs)
print(pypyodbc.dataSources())

# LIST OF INSTALLED DRIVERS
print(pypyodbc.drivers())

#------------------------------FILE LEVEL DATABASES-------------------------------#

##############################
## SQLITE DB CONNECTION
##############################

db = sqlite3.connect('C:\\Path\\To\\Database\\File.db')
cur = db.cursor()
cur.execute("SELECT * FROM tablename")

cur.close()
db.close()


############################
## ACCESS DB CONNECTION
############################

# (USING DSN)
databasename = 'C:\\Path\\To\\Database\\File.accdb'
constr = "DSN=MS Access Database;DBQ={0};".format(databasename)

db = pypyodbc.connect(constr)
cur = db.cursor()
cur.execute("SELECT * FROM tablename")

cur.close()
db.close()


# (USING DRIVER)
databasename = 'C:\\Path\\To\\Database\\File.accdb'
constr = "DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={0};".format(databasename)

db = pypyodbc.connect(constr)
cur = db.cursor()
cur.execute("SELECT * FROM tablename")

cur.close()
db.close()


#------------------------------SERVER LEVEL DATABASES-------------------------------#
############################
## MYSQL DB CONNECTION
############################

# (USING DSN)
db = pypyodbc.connect(dsn="dsn name")

cur = db.cursor()
cur.execute("SELECT * FROM tablename;")
db.commit()

cur.close()
db.close()

# (USING DRIVER)
db = pypyodbc.connect(driver="ODBC Driver Name", host="hostname",
                      uid="username", pwd="password", database="database")

cur = db.cursor()
cur.execute("SELECT * FROM tablename;")
db.commit()

cur.close()
db.close()

# (PYMYSQL MODULE)
db = pymysql.connect(host="hostname", port=portNumber,
                     user="username", passwd="123ABC", db="database")

cur = db.cursor()
cur.execute("SELECT * FROM tablename;")

cur.close()
db.close()


##############################
## SQL SERVER DB CONNECTION
##############################

#(USING DSN)
db = pypyodbc.connect('DSN=dsn name;')

cur = db.cursor()
cur.execute("SELECT * FROM dbo.tablename")

cur.close()
db.close()


# (USING DRIVER)
constr = 'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=servername;' \
         'DATABASE=database name;UID=username;PWD=password'
db = pypyodbc.connect(constr)

cur = db.cursor()
cur.execute("SELECT * FROM dbo.tablename")

cur.close()
db.close()