Export Python Dataframe to SQL Table

Question

I'm trying to export a python dataframe to a SQL Server table. Is there a better way to do this? I'm getting errors. Dataframe - results_out Output SQL table - FraudCheckOutput

cnn_out = pyodbc.connect('driver={SQL Server};server=XYZ;database=BulkLog;uid=sa;pwd=test')

results_out.to_sql(con=cnn_out, name='FraudCheckOutput', if_exists='replace', flavor='sqlite_master')

Thanks.

k.rozycki · Accepted Answer · 2014-07-17 20:22:24Z

1

Ok, this supposed to make the work done:

import pypyodbc

def database_insert(query, params=())
    conn_params = 'driver={SQL Server};server=XYZ;database=BulkLog;uid=sa;pwd=test'
    try:
        conn = pypyodbc.connect(conn_params)
    except pypyodbc.Error, e:
        print str(e)
    else:
        if conn.connected:
            db = conn.cursor()
            db.execute(query, params).commit()
    finally:
        if conn:
            conn.close()

SQL_INSERT_QUERY = """
    INSERT INTO table_name (
        [field_name1],
        [field_name2]
    )
    VALUES (
        1,
        'vale string'
    )
    WHERE
        field_name3 = ?
    """

database_insert(SQL_INSERT_QUERY, ('field_name3_value',))

in pyodbc usage is very similar

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()
cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()

more on http://code.google.com/p/pyodbc/wiki/GettingStarted

edited Jul 17, 2014 at 20:22

answered Jul 17, 2014 at 19:40

k.rozycki

6351 gold badge5 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BlackHat Over a year ago

So you recommend i use pypyodbc instead of pyodbc then.

k.rozycki Over a year ago

"Almost totally same usage as pyodbc ( can be seen as a re-implementation of pyodbc in pure Python )." - YES

BlackHat Over a year ago

Ok thanks. I'm asking because i'm using Anaconda and pypyodbc will not install on it.

BlackHat Over a year ago

Using the pyodbc i get pyodbc.IntegrityError: ('23000', "[23000]. Any idea how to resolve it?

BlackHat Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Export Python Dataframe to SQL Table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related