Runs SQL statement against a database, specified by SQLAlchemy connect string. If no database connection has been established, first word should be a SQLAlchemy connection string, or the user@db name of an established connection. Examples:: %%sql postgres
(self, line, cell='', local_ns={})
| 49 | @line_magic('sql') |
| 50 | @cell_magic('sql') |
| 51 | def execute(self, line, cell='', local_ns={}): |
| 52 | """Runs SQL statement against a database, specified by SQLAlchemy connect string. |
| 53 | |
| 54 | If no database connection has been established, first word |
| 55 | should be a SQLAlchemy connection string, or the user@db name |
| 56 | of an established connection. |
| 57 | |
| 58 | Examples:: |
| 59 | |
| 60 | %%sql postgresql://me:mypw@localhost/mydb |
| 61 | SELECT * FROM mytable |
| 62 | |
| 63 | %%sql me@mydb |
| 64 | DELETE FROM mytable |
| 65 | |
| 66 | %%sql |
| 67 | DROP TABLE mytable |
| 68 | |
| 69 | SQLAlchemy connect string syntax examples: |
| 70 | |
| 71 | postgresql://me:mypw@localhost/mydb |
| 72 | sqlite:// |
| 73 | mysql+pymysql://me:mypw@localhost/mydb |
| 74 | |
| 75 | """ |
| 76 | # save globals and locals so they can be referenced in bind vars |
| 77 | user_ns = self.shell.user_ns.copy() |
| 78 | user_ns.update(local_ns) |
| 79 | |
| 80 | parsed = sql.parse.parse('%s\n%s' % (line, cell), self) |
| 81 | conn = sql.connection.Connection.get(parsed['connection']) |
| 82 | first_word = parsed['sql'].split(None, 1)[:1] |
| 83 | if first_word and first_word[0].lower() == 'persist': |
| 84 | return self._persist_dataframe(parsed['sql'], conn, user_ns) |
| 85 | |
| 86 | try: |
| 87 | result = sql.run.run(conn, parsed['sql'], self, user_ns) |
| 88 | |
| 89 | if result and ~isinstance(result, str) and self.column_local_vars: |
| 90 | #Instead of returning values, set variables directly in the |
| 91 | #users namespace. Variable names given by column names |
| 92 | |
| 93 | if self.autopandas: |
| 94 | keys = result.keys() |
| 95 | else: |
| 96 | keys = result.keys |
| 97 | result = result.dict() |
| 98 | |
| 99 | if self.feedback: |
| 100 | print('Returning data to local variables [{}]'.format( |
| 101 | ', '.join(keys))) |
| 102 | |
| 103 | self.shell.user_ns.update(result) |
| 104 | |
| 105 | return None |
| 106 | else: |
| 107 | #Return results into the default ipython _ variable |
| 108 | return result |
no test coverage detected