insert into access db with python/pyodbc

So try it with single quotes.

SQL = ""
"INSERT INTO AssetDetails
(
   [Reporting Account Number],
   [As Of Date],
   [Asset Type],
   [Security Description 1],
   [Shares / Par],
   [Base Price],
   [Base Cost],
   CUSIP,
   Ticker,
   ISIN,
   SEDOL
)
VALUES
   (
      'ABCD99020002',
      '1/31/2010',
      'CASH & CASH EQUIVALENTS',
      'INTEREST RECEIVABLE',
      '0.000',
      '1.00',
      '1,171,069.04',
      '',
      '',
      '',
      ''
   );
""
"

Suggestion : 2

07/27/2022

To get started, run the following sample script. Create a file called test.py, and add each code snippet as you go.

> python test.py

Connect

import pyodbc
# Some other example server values are
# server = 'localhost\sqlexpress'
#
for a named instance
# server = 'myserver,port'
# to specify an alternate port
server = 'tcp:myserver.database.windows.net'
database = 'mydb'
username = 'myusername'
password = 'mypassword'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
cursor = cnxn.cursor()

The cursor.executefunction can be used to retrieve a result set from a query against SQL Database. This function accepts a query and returns a result set, which can be iterated over with the use of cursor.fetchone().

#Sample select query
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
   print(row[0])
row = cursor.fetchone()

Suggestion : 3

pyodbc is going to be the bridge between SQL and Python. This makes access easy to ODBC (Open Database Connectivity) databases. ODBC was developed by SQL Access Group in the early ’90s as an API (Application Programming Interface) to access databases. These DBMS (Database management Systems) are compliant with ODBC.,We are going to use the library named pyodbc to connect python to SQL. This will give us the ability to use the dynamic nature of Python to build and run queries like SQL. These two languages together are a formidable force in our hands. These together can take your code to the pinnacle of automation and efficiency.,Now you can start using Python to work upon your data which rests in SQL Databases. Once you brought it as DataFrame, then all the operations are usual Pandas operations. Many of these operations were not possible in SQL.,Notice the output above, it’s the same as you would expect from any local data file (say .csv), imported in Python as Pandas DataFrame.

Install pyodbc using pip or visit their webpage.

pip install pyodbc

and then import the library in your Jupyter notebook

import pyodbc

For Trusted Connection:

connection_string = ("Driver={SQL Server Native Client 11.0};"
   "Server=Your_Server_Name;"
   "Database=My_Database_Name;"
   "Trusted_Connection=yes;")

For Non-Trusted Connection:

connection_string = ("Driver={SQL Server Native Client 11.0};"
   "Server=Your_Server_Name;"
   "Database=My_Database_Name;"
   "UID=Your_User_ID;"
   "PWD=Your_Password;")

The other way is to run the following query in SQL.

SELECT @ @SERVERNAME

# Lets summarise the codes till now
import pyodbc
connection_string = ("Driver={SQL Server Native Client 11.0};"
   "Server=Your_Server_Name;"
   "Database=My_Database_Name;"
   "UID=Your_User_ID;"
   "PWD=Your_Password;")
connection = pyodbc.connect(connection_string)

# Initialise the Cursor
cursor = connection.cursor()
# Executing a SQL Query
cursor.execute('SELECT TOP(10) * FROM State_Population')

for row in cursor:
   print(row)

Out[3]:

(AL, under18, 2012, 1117489.0)
(AL, total, 2012, 4817528.0)
(AL, under18, 2010, 1130966.0)
(AL, total, 2010, 4785570.0)
(AL, under18, 2011, 1125763.0)
(AL, total, 2011, 4801627.0)
(AL, total, 2009, 4757938.0)
(AL, under18, 2009, 1134192.0)
(AL, under18, 2013, 1111481.0)
(AL, total, 2013, 4833722.0)