You need to commit the data. Each SQL command is in a transaction and the transaction must be committed to write the transaction to the SQL Server so that it can be read by other SQL commands. Under MS SQL Server Management Studio the default is to allow auto-commit which means each SQL command immediately … Read more
Is pyodbc becoming deprecated? No. For at least the last couple of years pandas’ documentation has clearly stated that it wants either a SQLAlchemy Connectable (i.e., an Engine or Connection object), a string containing a SQLAlchemy connection URL, or a SQLite DBAPI connection. (The switch-over to SQLAlchemy was almost universal, but they continued supporting SQLite … Read more
You are so close! import pyodbc cnxn = pyodbc.connect(‘DRIVER={SQL Server};SERVER=SQLSRV01;DATABASE=DATABASE;UID=USER;PWD=PASSWORD’) cursor = cnxn.cursor() cursor.execute(“SELECT WORK_ORDER.TYPE,WORK_ORDER.STATUS, WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID FROM WORK_ORDER”) for row in cursor.fetchall(): print row (the “columns()” function collects meta-data about the columns in the named table, as opposed to the actual data).
Do not put a space after the Driver keyword in the connection string. This fails on Windows … conn_str = ( r’DRIVER = {SQL Server};’ r’SERVER=(local)\SQLEXPRESS;’ r’DATABASE=myDb;’ r’Trusted_Connection=yes;’ ) cnxn = pyodbc.connect(conn_str) … but this works: conn_str = ( r’DRIVER={SQL Server};’ r’SERVER=(local)\SQLEXPRESS;’ r’DATABASE=myDb;’ r’Trusted_Connection=yes;’ ) cnxn = pyodbc.connect(conn_str)
You can specify the connection string as one long string that uses semi-colons (;) as the argument separator. Working example: import pyodbc cnxn = pyodbc.connect(r’Driver=SQL Server;Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;’) cursor = cnxn.cursor() cursor.execute(“SELECT LastName FROM myContacts”) while 1: row = cursor.fetchone() if not row: break print(row.LastName) cnxn.close() For connection strings with lots of parameters, the following will accomplish … Read more
I recently had the same problem and feel like to add an answer to this for others. to_sql seems to send an INSERT query for every row which makes it really slow. But since 0.24.0 there is a method parameter in pandas.to_sql() where you can define your own insertion function or just use method=’multi’ to … Read more
It differs by driver. Here are two examples: import MySQLdb mc = MySQLdb.connect() r = mc.cursor() r.execute(‘select %s, %s’, (“foo”, 2)) r._executed “select ‘foo’, 2” import psycopg2 pc = psycopg2.connect() r = pc.cursor() r.execute(‘select %s, %s’, (‘foo’, 2)) r.query “select E’foo’, 2”
EDIT (2019-03-08): Gord Thompson commented below with good news from the update logs of sqlalchemy: Since SQLAlchemy 1.3.0, released 2019-03-04, sqlalchemy now supports engine = create_engine(sqlalchemy_url, fast_executemany=True) for the mssql+pyodbc dialect. I.e., it is no longer necessary to define a function and use @event.listens_for(engine, ‘before_cursor_execute’) Meaning the below function can be removed and only the … Read more
You can get the columns from the cursor description: columns = [column[0] for column in cursor.description]
A shorter and more concise answer import pyodbc import pandas as pd cnxn = pyodbc.connect(r’DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};’ r’DBQ=C:\users\bartogre\desktop\data.mdb;’) sql = “Select sum(CYTM), sum(PYTM), BRAND From data Group By BRAND” data = pd.read_sql(sql,cnxn) # without parameters [non-prepared statement] # with a prepared statement, use list/tuple/dictionary of parameters depending on DB #data = pd.read_sql(sql=sql, con=cnxn, … Read more